UK vs EU date mixup, textbox->query->subform recordsource

[svdoerga]

On my form I have two textboxes where the user can enter a 'From' and a 'To' date. A query is then created that returns records between these dates. The result is displayed in a subform, by setting the subforms recordsource to the query like this:
"Me.RepQ_items_per_manufacturer_subform.Form.RecordSource = strQuery".

The problem is that Access (2007) seems to mix up the UK (MM-DD-YYYY) and EU (DD-MM-YYYY) date formats sometimes. When I input in the textboxes from: 01-01-2011 to: 01-03-2011 (jan 1st to march 1st) the query returns results between jan 1st and jan 3rd. But when I input from: 01-01-2011 to: 31-03-2011 (jan 1st to march 31st) the query correctly returns the results between jan 1st and march 31st.

I pass on the dates to the query without any modifications. While debugging I see the dates are correct in the query that is run:
"...WHERE (((Item_Query.ReceivedDate) Between #1-1-2011# And #1-3-2011#))" and
"...WHERE (((Item_Query.ReceivedDate) Between #1-1-2011# And #31-3-2011#))". Yet 01-03-2011 is interpreted as jan 3rd, and 31-03-2011 is interpreted as march 31st.

I would expect the EU date format, since thats how it formatted in all the tables, forms, and queries. But when I look at queries in SQL view, I see the dates are in UK format. So I am a bit confused.

Does anyone know how to fix this?

 

[PHV]

Use a non ambiguous format:
Between #2011-01-01# And #2011-03-01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Check out Allen Browne's International Dates in Access. You could set the format property of the controls to dd-mmm-yyyy to avoid all confusion.

Duane
Hook'D on Access
MS Access MVP

 

[Panchovia]

Private Sub Command86_Click()

If Me.NewRecord Then
If Nz(Me.Aliasdate) > 0 And Me.aliasamount = Null Or Me.aliasamount = 0 Or Me.Aliasdate = Null Or Mycheckbox = False And DTPicker6.Value = Date Then
MsgBox ("No record will be added"), vbOKCancel
Beep
End If
Else
MsgBox ("Saving record"), vbOKCancel
Beep
DoCmd.RunCommand acSaveRecord
End If
End sub
This button should evalute the date picker values to save the record or not
if the user does enter the form by mistake and click on this button it should give him the message no record will be added and then exit ( the dtpicker checkbox is then unmark but you seee the current date in the dtpicker right now this does work when you click on the comman button)
if the user enter the form and activate only the dtpicker and does not fill in the other fields and opts for exit it should give the message no record will be added (this part works fine with the code)
when the user enters the form and click on the dtpicker and he fills all the field it should give him the message saving record and the exit the form to the main form,(this part doesn't work)
What do I miss in this code.

Panchovia

 

[dhookom]

Panchovia,
I'm not sure why you posted in this existing older thread. You should have started a new thread with your question.

You can't compare a field or value to Null using = since Null is unknown

   ... Me.aliasamount = Null ...

You can use:

   ... IsNull(Me.aliasamount) ...

Duane
Hook'D on Access
MS Access MVP