Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date fields in queries (2000)

    I have a date field set up called 'night of stay'. I have about 30 queries set up which specify dates between certain values. Ex: night of stay between 5/1/02 and 5/31/02.
    Several different queries are run separately for each month (May-November). I'd like to set up a
    parameter query. The month and day are hard coded in the query, but the user enters the year
    they want. I've tried several things such as:
    between #4/1/]enter year]#. I've also tried setting up the month/day as a date field, and the year as a
    separate field, but each time I run a query, the field month/day automatically defaults to the current year.
    How do I get around this? I've also tried setting up a date field with a format which just specifies month/day and no year, but again, in queries, Access still defaults to the current year. Help!!
    Thanks!
    Donna

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date fields in queries (2000)

    Enter these two function in a module. Save the module with any name but not EnterFirstYear or EnterSecondYear
    <pre>Function EnterFirstYear() As Date
    Dim firstyear As Integer
    firstyear = InputBox("enter first year")
    EnterFirstYear = DateSerial(firstyear, 1, 4)
    End Function
    Function EnterSecondYear() As Date
    Dim secondyear As Integer
    secondyear = InputBox("enter second year")
    EnterSecondYear = DateSerial(secondyear, 2, 4)
    End Function</pre>

    In the lines with dateserial adapt the month and day like you want.(first number is the month, second is the day)
    In the criteria of the query enter :
    <pre>Between enterfirstyear() And entersecondyear()</pre>

    Francois

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

    Re: Date fields in queries (2000)

    One option is to impersonate date with a text string in the query criteria line, ex, this expression

    Between "01/01/" & [Enter year:] And "03/31/" & [Enter year:]

    will return all records between Jan 1 & Mar 31 for year entered at prompt. Assumes date range always within same year, otherwise modify 2nd parameter. This works (for valid date field) whether you enter 2-digit or 4-digit year. Not sure if it's possible to have a "real" date parameter (with # delimiters) for partial date.

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date fields in queries (2000)

    Thank you Francois and MarkD!!!!!!!!!
    Both of these options worked great!!
    --Donna

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date fields in queries (2000)

    I have one more question.. I want the user to enter in the year from an input box.
    I then want to use that same year in several queries. Each query selects a month and the year that the user put in the input box. The first query would select night_of_stay between 5/1/year from input box
    and 5/31/year from input box. Second query would select night_of_stay between 6/1/year from input box and 6/30/year from input box. I want the user to enter the value in once, and then a bunch of queries will be executed, one after the other, each based on a month and the same year. I want the user to only enter the year in once. What's the most efficient way of doing this? I've tried several things and nothing seems to work. I'd really appreciate the help!!!!!
    Thanks,
    Donna

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

    Re: Date fields in queries (2000)

    You could enter the year into a field on a form then reference the form for each of your queries.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date fields in queries (2000)

    I'm sorry, but I don't understand what you're doing at all. What do you mean a bunch of queries will be executed? You say that the first query would *select* something and the second query would *select* something else. That isn't execution, which implies an action query (append, update, delete).

    What exactly are you doing. You may be getting answers, but you'll get better answers if you explain the question.
    Charlotte

  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 fields in queries (2000)

    If the dates you want to select all fall in same month and year, you can create one query to select records rather than creating multiple queries that are apparently are doing same thing. Create 2 calculated fields using the following expressions:

    MONTH_OF_STAY: DatePart("m",[DATE_OF_STAY])
    YEAR_OF_STAY: DatePart("yyyy",[DATE_OF_STAY])

    The 1st calculated field uses DatePart function to return month portion of DATE_OF_STAY date field. 2nd field uses DatePart function to return year portion of DATE_OF_STAY (4 digits). In the Criteria line enter expression similar to:

    (for MONTH_OF_STAY): [Forms]![TEST_FRM]![COMBO_MON]
    (for YEAR_OF_STAY): [Forms]![TEST_FRM]![COMBO_YEAR]

    This example uses combo boxes (as opposed to text boxes) on a form named TEST_FRM to set query criteria. There's no way to directly reference an InputBox in a query. For COMBO_MON RowSource can use Value List in this format: 1;JAN;2;FEB;3;MAR;4;APR (etc). Hide first column by setting width to 0, 1st column will be bound column because DatePart function returns month as a number between 1 and 12. For COMBO_YEAR RowSource recommend using SQL statement similar to this, using DatePart function to return list of valid years based on DATE_OF_STAY field:

    SELECT DISTINCT DatePart("yyyy",[DATE_OF_STAY]) AS YEARS FROM [TABLE_NAME]
    WHERE (((DatePart("yyyy",[DATE_OF_STAY])) Is Not Null));

    The user can select month and valid year using combo boxes, then click a command button to run query and view results. NOTE: To have the combo boxes display 1st value in list when you open form (instead of being blank) enter following for form's On Load event:
    <pre>With Me
    .Combo_MON.Value = .Combo_MON.ItemData(0)
    .Combo_YEAR.Value = .Combo_YEAR.ItemData(0)
    End With</pre>

    This assumes no column headings. Another suggestion, you can use "Like" criteria for date fields as in following examples:

    Like "12/*/2000" Returns all records for DEC 2000
    Like "*/*/2000" Returns all records for Year 2000
    Like "12/*/*" Returns all records for month of DEC, all years

    For month be sure to use 2 digits. Like "1/*/1999" won't work. Like "01/*/1999 does. Likewise, use all 4 digits if specifying year. However, this may depend on your computer's regional date settings; the date field's format did not effect results. Another suggestion, you can create report based on query without the month/year criteria, and group report on date field using Year and Month as grouping intervals.

    Hope this helps.

  9. #9
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date fields in queries (2000)

    Thanks MarkD (and everyone else who replied!)
    The information you gave me on using date fields is really helpful. I also didn't realize I could not reference an InputBox in a query. I followed the suggestion to set up a form with a combo box to select the year, and set up a command button to run the queries. It's working great!
    Thanks again!!!!!!!
    Donna

Posting Permissions

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