Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help on form to drive query criteria! (Access XP)

    Hi there,
    I've been struggling to get my user form to extract the criteria in my queries. I would like to supply the course code attended between two dates and get the query to show the results.
    The query value supplied to the form can be : 2278, or on another occation it can be 2278 or 2559 (multiple criteria, 2 courses).
    On some accations there may even be a But Not: course which is an optional entry.
    So for example:
    The person can type 2278 (attend box) to only get this course between a start and end date, or
    The person can type 2278 or 2559 (multiple courses) in the attend box to only get the result between a start and end date, or
    The person can type 2278 in the Attend box, and 2559 in the But Not box to get people who attended 2278 but did not attend 2559 between a start and end date.

    Could i possibly get some assistance,as I don't know if my design and criteria structure is correct!

    Many Thanx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help on form to drive query criteria! (Access XP)

    See attached version. I had to use CDate for the date comparisons, since the Start Date field in the table is a text field, not a date field. Here is the SQL for Q1:

    SELECT Test.staffid, Test.[Course Code], Test.[Start Date], Test.Participant, Test.[Phone Number], Test.[Email Address]
    FROM Test
    WHERE (((InStr([Forms]![QueryForm]![Attended],[Course Code]))>0 Or (InStr([Forms]![QueryForm]![Attended],[Course Code])) Is Null) AND ((CDate([Start Date])) Between [Forms]![QueryForm]![StartDate] And [Forms]![QueryForm]![EndDate]));

    And for Q2:

    SELECT Test.staffid, Test.[Course Code], Test.[Start Date], Test.Participant, Test.[Phone Number], Test.[Email Address]
    FROM Test
    WHERE (((InStr([Forms]![QueryForm]![ButNot],[Course Code]))>0) AND ((CDate([Start Date])) Between [Forms]![QueryForm]![StartDate] And [Forms]![QueryForm]![EndDate]));

    That for Q3Result has remained unchanged.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help on form to drive query criteria! (Access XP)

    The attachment works like a charm! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Thankyou Hans. I will apply this to the actual DB and test it out. I doubt that it will cause any hassles either.

    Just one thing. The actual DB's start date is type Date/Time. I assume I need to change CDate back to Date?

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>...and <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help on form to drive query criteria! (Access XP)

    > I assume I need to change CDate back to Date?

    You can simply use [Start Date] instead of CDate([Start Date]). Or leave it - CDate applied to a date doesn't do harm.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •