Date Format

[mithani]

Hello,

I have report based on query with date parameters. When I enter date from 01/05/2007(01st May 2007) to 30/05/2007(30th May 2007), I get report from 05/01/2007(5th of Jan 2007) upto 30/05/2007(30th May 2007).

How can I fix this problem.

Thanks

mithani

 

[Rich]

Change your Regional settings in Windows

 

[mithani]

Thanks Rich,

My regional setting are already dd/mm/yyyy format.

Any idea mate?

mithani

 

[Rich]

How are you passing the Parameters to the query, and are they actual date fields and not text?

 

[Rabbie]

If you are generating your SQL on the fly in your code you need to format your dates in #mm/dd/yyyy#. If you are using the query builder then use your regional settings.

 

[mithani]

Thanks Rich & Rabbie,

Rich: I am passing this parameter to query

DoCmd.OpenReport "Purchase Report", acViewPreview, , "([InvoiceDate] Between #" & Format(Me.OSTARTDATE, "DD/MM/YYYY") & "# And #" & Format(Me.OENDDATE, "DD/MM/YYYY") & "#)"

Rabbie:

My computer reginal setting is OK.

Any idea?

Thanks

mithani

 

[boblarson]

As Rabbie mentioned, change it to this:
DoCmd.OpenReport "Purchase Report", acViewPreview, , "([InvoiceDate] Between #" & Format(Me.OSTARTDATE, "mm/dd/yyyy") & "# And #" & Format(Me.OENDDATE, "mm/dd/yyyy") & "#)"

Use U.S. date formatting for this as shown due to the way Access uses them (remember it was created in Redmond, Washington, USA so Bill's team made it that way).

 

[mithani]

Thanks Bob,

I can't enter in birtish format dd/mm/yyyy?

thanks

mithani

 

[boblarson]

You can ENTER it as such, but you just need to use the FORMAT function to format it to US dates when using it as you are, or in queries (I believe).

 

[mithani]

Thanks Bob,

I changed the date format to mm/dd/yyyy. When I enter date in US format, 08/01/2007 (August/01/2007), record printing from 08th Jan 2007.

I am totaly confused

mithani

 

[boblarson]

Okay, confusion happens. You can ENTER the date in your own format, but the code will format it into the US date format. So, in the text box, you enter 01/08/2007 for August 1st and the code - Format(Me.YourDateTextBox,"mm/dd/yyyy") will take care of the formatting for the criteria.

 

[mithani]

Thanks Bob.

Working great.

mithani