Results 1 to 13 of 13
  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 (Access 2002)

    Hi

    Same old problem, I can't get seem to format the date correctly in the query to select the records for the date range parameter (selection criteria is for dates in a selected week period). Instead it is selecting is the right month and day range, but not the right year range, ie it is also selecting all years meeting the month and day range. The query is:

    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, qryInvoicePaid.PDate
    FROM qryInvoicePaid
    WHERE qryInvoicePaid.PDate Between format([Forms]![frmPaidInvoice]![WeekDate1], "mm/dd'/yyyy") And format([Forms]![frmPaidInvoice]![WeekDate2],"mm/dd/yyyy");

    Weekdate1 and weekdate2 are unbound fields on the form based on dateserial values.

    I know you have to explicitly tell Access to use month/day/year format using #, but having tried various combinations I either get an error message or no records. So I would be grateful for some advice on what is the correct syntax to use.

    Thanks & Regards

    WTH

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

    Re: Query Date (Access 2002)

    You must use US date formats explicitly if you assemble an SQL statement in code. In a stored query, Access will do the work for you, so you should be able to use

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

    To make sure that the form parameters are interpreted as dates, you can declare them in Query | Parameters.... Enter [Forms]![frmPaidInvoice]![WeekDate1] under Parameter, and select Date/Time as data type. In the second row, enter [Forms]![frmPaidInvoice]![WeekDate2] under Parameter, and select Date/Time as data type. Then click OK.

    You should set the Format property of the WeekDate1 and WeekDate2 text boxes to one of the date formats.

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

    Re: Query Date (Access 2002)

    Hans,
    Does VBA also use m/d/yy (or US) formats explicitly.?
    Regards,
    Rudi

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

    Re: Query Date (Access 2002)

    If you want to use a literal number in VBA or SQL, it must be in US number format (i.e. use the period as decimal separator), for example

    Dim dblTest As Double
    dblTest = 123.45

    If you want to use a literal date in VBA or in SQL, it must be in US date format (i.e. mm/dd/yy or mm/dd/yyyy), and you must put # before and after it, for example

    Dim datMyDate As Date
    ' The next two statements are equivalent
    datMyDate = #10/19/04#
    datMyDate = #10/19/2004#

    Note: literal times must also have # before and after them, but VBA and SQL accept times based on the 12 hour clock (with AM/PM) and on the 24 hour clock, for example

    Dim datMyTime As Date
    ' The next two statements are equivalent
    datMyTime = #2:32 PM#
    datMyTime = #14:32#

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

    Re: Query Date (Access 2002)

    We deal with this in our applications by using a couple of customer functions, USDate() and USNum() to cast dates and numbers into the US format for our clients scattered across the globe. This also applies to things like DLookup, which also requires the US format because it is generating a SQL query underneath. The requirement for the US date format is built into SQL, which is NOT a Microsoft technology ( <img src=/S/smile.gif border=0 alt=smile width=15 height=15>). We also make sure our exports are translated to US format dates and numbers so that the import process can handle them, regardless of the regional settings on the importing machine.
    Charlotte

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

    Re: Query Date (Access 2002)

    Thanx!
    Charlotte:
    Do these USDate() and USNum() functions exist in VBA as default functions or are they custom built or activated somehow.
    Also how would you impliment them in Excel or Access VBA. Could you show me a quick example! (Obviously only if they are available to me) <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards,
    Rudi

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

    Re: Query Date (Access 2002)

    We created them ourselves and implement them in Access.

    The basic test in USNum is to see if numbers use the period for the decimal delimiter, and that can easily be done using <code><font color=blue> strDelim = Format(0, ".")</font color=blue></code> to assign a formatted decimal to a string variable. If strDelim is NOT = "." then some other delimiter is being used and you have to convert the passed number to a string and replace strDelim in that string with a "." to convert the number to US format.

    With dates, you start by converting the dates to strings using the CDate function, like this:

    strDate = Format(CDate(varDate), "mm/dd/yyyy")
    strTime = Format(CDate(varDate), "h:nn:ss AM/PM")

    Then you have to check for non-US date and time delimiters with a statement like <font color=blue><code> strDateDelim = Format(Now(), "/")</code></font color=blue> and <font color=blue><code>strTimeDelim = Format(Now(), ":") </code></font color=blue> and then use the Replace function, if needed, to substitute the "/" for strDateDelim in strDate and the ":" for strTimeDelim in strTime. Then just concatenate <font color=blue><code>"#" & strDate & " " & strTime & "#"</code></font color=blue> and you have a US date.
    Charlotte

  8. #8
    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 (Access 2002)

    Hi Hans

    Thanks for the advice, although I'm still not doing something right. I have ensured the unbound text fields in the form now have a short date format. However, amending the query in line with your suggestion is proving problematic. After entering the dates for the paramter prompts I get an error message saying either the expression is incorrect or it is too complex. Here is the revised query:

    PARAMETERS [Forms]!frmPaidInvoice![WeekDate1] DateTime, [Forms]!frmPaidInvoice![WeekDate2] DateTime;
    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, qryInvoicePaid.PDate
    FROM qryInvoicePaid
    WHERE (((qryInvoicePaid.PDate) Between [Forms]![frmPaidInvoice]![WeekDate1] And [Forms]![frmPaidInvoice]![WeekDate2]));

    Obviously, I have the wrong syntax somewhere, so a pointer in the right direction would be welcome.

    Thanks & Regards
    WTH

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

    Re: Query Date (Access 2002)

    In A2002, you have to be very careful when putting in form parameters or the query engine gets confused. Change the parameters line of your SQL to include ALL the square brackets, like this:

    <code>PARAMETERS [Forms]![frmPaidInvoice]![WeekDate1] DateTime, [Forms]![frmPaidInvoice]![WeekDate2] DateTime;</code>
    Charlotte

  10. #10
    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 (Access 2002)

    Hi Charlotte

    Thanks. I changed it as suggested, but it still comes up with the same error message. Attached is a copy of it.

    Any further suggestions??

    Regards
    WTH

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

    Re: Query Date (Access 2002)

    Any chance of a cutdown version of your database.

  12. #12
    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 (Access 2002)

    Hi

    Same old story, programmer not doing enough checking before rushing for help. Translation - problem solved. As you will have noted the query I showed was based on another query. The problem was in this other query. Once corrected, the error message was removed and it now selects the correct records based on Hans and Charlotte earlier advice.

    Thanks for the assistance.

    Regards
    WTH

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

    Re: Query Date (Access 2002)

    Ah - thanx for that explaination Charlotte! I will attempt this strategy/advice when I can, and see if i can get it working. I know where I can call if I get in a jam!
    Thanx again!
    Regards,
    Rudi

Posting Permissions

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