Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Date Format (Access 97)

    Hi

    I have a query that selects records based on the date in the query being between two dates picked from a form, and the query is used in a report recordsource. The query is as follows:

    SELECT qryInvoicePaid.InvoiceReason, qryInvoicePaid.Amount, qryInvoicePaid.InvoiceNo, qryInvoicePaid.Narrative, qryInvoicePaid.Create_Date, qryInvoicePaid.Print_Date, qryInvoicePaid.Paid_Date, qryInvoicePaid.Export_date, qryInvoicePaid.tnp_ref, qryInvoicePaid.product_name, qryInvoicePaid.CompanyArc, qryInvoicePaid.PostalAddArc, qryInvoicePaid.ContactNameArc, qryInvoicePaid.create_user
    FROM qryInvoicePaid
    WHERE format(qryInvoicePaid.Paid_Date,"dd/mm/yyyy") Between [Forms]![frmPaidInvoice]![WeekDate1] And [Forms]![frmPaidInvoice]![WeekDate2];

    The qryInvoicePaid.Paid_Date is a date and time field and the date fields in the forms are just date fields. So I am using the format property to just select the date part. I'm not sure whether this is the correct method. Anyway, when using this method, I get some strange results. For example, when using the form dates 6/10/2002 and 10/10/2002, it selects records with the paid date of 9/10/2002 (correct) and 8/07/2002 (incorrect).

    Any help would be appreciated.

    Regards
    WTH

    PS All dates are based on the english calendar system eg 7 June 2002 = 7/06/2002.

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

    Re: Query Date Format (Access 97)

    You should be able to code the where clause as follows (ie. without the format statement):

    WHERE qryInvoicePaid.Paid_Date Between [Forms]![frmPaidInvoice]![WeekDate1] And [Forms]![frmPaidInvoice]![WeekDate2];

    HIAR (Hope I Am Right)
    Pat

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Date Format (Access 97)

    Hi

    Thanks for the reply.

    Yes I have tried that but it does not work as it seems the time part of the date causes some paid dates to either be included or excluded where the input dates are the same as the paid dates.

    Regards
    WTH

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Date Format (Access 97)

    Are you building this as a query using the qbe grid, or are your writing an sql statement that is used by a dao recordset.
    If you are doing the latter, then sql expects dates to be in mm/dd/yyyy format so change the where clause to something like:

    WHERE format(qryInvoicePaid.Paid_Date,"mm/dd/yyyy") Between #" & format( [Forms]![frmPaidInvoice]![WeekDate1] ,"mm/dd/yyyy") & "# And # " format([Forms]![frmPaidInvoice]![WeekDate2],"mm/dd/yyyy") & "#";

    Dates also need to be enclosed in # signs.
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Date Format (Access 97)

    Hi

    No I'm using the former. However, I might give the # signs a go as it can't hurt to try.

    Regards
    WTH

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

    Re: Query Date Format (Access 97)

    You could try:

    WHERE qryInvoicePaid.Paid_Date > [Forms]![frmPaidInvoice]![WeekDate1]-1 And qryInvoicePaid.Paid_Date < [Forms]![frmPaidInvoice]![WeekDate2]+1;

    If the -1 and +1 don't work here, setup these values in the form as invisible controls.
    You want to avoid using formatting of the records date for performance reasons.

    Edited by Pat 10:50.
    The above is no good as you need to check the From Date - 1 day at Midnight.


    HTH
    Pat

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Date Format (Access 97)

    The way it returns dates from well outside the range makes we wonder if it is still mixing up days and months.
    Try this
    WHERE qryInvoicePaid.Paid_Date Between format([Forms]![frmPaidInvoice]![WeekDate1],"dd/mm/yyyy") And
    format( [Forms]![frmPaidInvoice]![WeekDate2],"dd/mm/yyyy") ;
    Regards
    John



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

    Re: Query Date Format (Access 97)

    Referring to my last post, delete the Edited by Pat comment. I have just made up a little database and have tested the following:

    SELECT [tbl Dates].DateEntered, [tbl Dates].RecordText
    FROM [tbl Dates]
    WHERE ((([tbl Dates].DateEntered)>[forms]![frm Dates]![TextFromDate]-1 And ([tbl Dates].DateEntered)<[forms]![frm Dates]![TextToDate]+1));

    This returns the correct records for a range of dates of 1/6/02 thru the 30/6/02 (my test) with the following data:

    DateEntered RecordText
    30/05/02 12:04:00 1
    30/05/02 23:59:00 2
    1/06/02 12:20:00 3
    30/06/02 12:30:00 4
    30/06/02 23:59:00 5
    1/07/02 6

    The records returned from the query above were:
    DateEntered RecordText
    1/06/02 12:20:00 3
    30/06/02 12:30:00 4
    30/06/02 23:59:00 5

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Date Format (Access 97)

    Hi

    Thanks for everyone

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Date Format (Access 97)

    Have you got the right settings in Regional Settings?
    (Start - Settings --Control Panel---Regional Settings
    If you haven't told the computer that you use English date settings it can cause these sorts of problems.
    Regards
    John



Posting Permissions

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