Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Parameter Search (2002 (XP))

    I am trying to use the criteria
    Like "[Forms]![frmReportsMonthly]![Combo1]/*/[Forms]![frmReportsMonthly]![Combo3]"
    But it wont put the values I select on the form into the query. Any Ideas?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date Parameter Search (2002 (XP))

    What are the values of Combo1 and Combo3?
    If you are trying to search for a range of values, you probably need:

    Between [Forms]![frmReportsMonthly]![Combo1] and [Forms]![frmReportsMonthly]![Combo3]

    Pat

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Parameter Search (2002 (XP))

    The critera is for a date field. I want to return all the records from a month Combo1 is the month in numeric form and combo3 is the year in numeric form. The day is a wild card. So what I need is
    Combo1/*/Combo3
    I think the "" on my expression are causing some trouble. I'm not sure though.
    Like "[Forms]![frmReportsMonthly]![Combo1]/*/[Forms]![frmReportsMonthly]![Combo3]"

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date Parameter Search (2002 (XP))

    You will probably need 2 fields in your query:
    One the MonthSearch: Month(YourDate) and it's criteria would be [Forms]![frmReportsMonthly]![Combo1] .
    The other would be the YearSearch: Year(YourDate) and it's criteria would be [Forms]![frmReportsMonthly]![Combo3] .
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Parameter Search (2002 (XP))

    OK I put the date field in twice with one containing
    Like "[Forms]![frmMonthlyReports]![Combo11]/*/*"
    and the other has
    Like */*/"[Forms]![frmMonthlyReports]![Combo3]"
    still no good. How would I put a regular parameter in a wildcard date criteria. I must have something wrong in the syntax
    I tried like "[Enter Month]/*/*" and that didn't even work.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date Parameter Search (2002 (XP))

    Can you post your SQL to the forum here, and I'll see if I can help.
    Pat

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Parameter Search (2002 (XP))

    Here it is. Thanks for taking a look see

    SELECT tblClass.ysnCancelled, tblClass.strClassName, tblClass.dtmStartDate, tblClass.curStipend, Sum(Nz([curTuition],0)) AS Tuition, tblClass.dtmEndDate
    FROM tblClass LEFT JOIN tblRegister ON tblClass.lngClassNumber = tblRegister.lngClassNumber
    GROUP BY tblClass.ysnCancelled, tblClass.strClassName, tblClass.dtmStartDate, tblClass.curStipend, tblClass.dtmEndDate
    HAVING (((tblClass.ysnCancelled)=No) AND ((tblClass.dtmEndDate) Like "[Forms]![frmMonthlyReports]![combo11]/*/[Forms]![frmMonthlyReports]![combo3]"));

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Parameter Search (2002 (XP))

    Recommend try syntax like this:

    <pre>SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate
    FROM Orders
    WHERE (((Orders.OrderDate) Like [Forms]![frmReportsMonthly]![Combo1] & "/*/" &
    [Forms]![frmReportsMonthly]![Combo3]));</pre>


    This example uses Orders table from NorthWind database for test purposes. To work properly date and year parameters must be entered as numbers (1 for Jan thru 12 for Dec for month) and four-digit year (ex: 1997), if this is what your combo is using then should work. Note although Like expression constructed using text string if in right format will be interpreted as date and query will return records for specified date & month.

    HTH

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Parameter Search (2002 (XP))

    I would be happier with setting one date variable to the first of the month and another to the first of month+1 and then using between in the query.
    David Grugeon
    Brisbane Australia

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Parameter Search (2002 (XP))

    PS: Here is another option that does not use "Like" criteria. This uses Pat's suggestion to use Month and Year Functions to set criteria:

    <pre>SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate
    FROM Orders
    WHERE (((Month([OrderDate]))=[Forms]![frmReportsMonthly]![Combo1]) AND
    ((Year([OrderDate]))=[Forms]![frmReportsMonthly]![Combo3]))
    ORDER BY Orders.OrderDate;
    </pre>


    Again the combo values would have to equate to numerical values for month and year, e.g., 1 for Jan, etc. and all 4 digits for year. Entering "01" for Jan or "97" instead of 1997 will result in no records returned even though there are orders for that month.

    HTH

  11. #11
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Parameter Search (2002 (XP))

    All these work great. The entries on the form are numeric. You solved my problem. I am learning so much in hear you are all the best

Posting Permissions

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