Date changing from input box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an unbound text box for inputting a date using dd/mm/yyyy format. My
criteria statement is:

stLinkCriteria = [DateOfService] = " # & Me.IncidentDate & # "

When I debug, the statement reads:

[DateOfService] = 11/02/2006



Any ideas what I am doing wrong?

Cheers
 
Disregard, I obviously left my brain at home this morning. Fixed with

stLinkCriteria = "[DateOfService] = #" & Me.IncidentDate & "#"
 
Spoke too soon,

If I enter a day less than 13, it appears that access is switching the day
and month. I seem to recall something about forcing a certain format but
cannot find any reference here.

Any help would be appreciated.



Paul B. said:
Disregard, I obviously left my brain at home this morning. Fixed with

stLinkCriteria = "[DateOfService] = #" & Me.IncidentDate & "#"



Paul B. said:
I have an unbound text box for inputting a date using dd/mm/yyyy format. My
criteria statement is:

stLinkCriteria = [DateOfService] = " # & Me.IncidentDate & # "

When I debug, the statement reads:

[DateOfService] = 11/02/2006



Any ideas what I am doing wrong?

Cheers
Click to expand...
Click to expand...
 
Paul said:
Spoke too soon,

If I enter a day less than 13, it appears that access is switching
the day and month. I seem to recall something about forcing a certain
format but cannot find any reference here.

Any help would be appreciated.
Click to expand...

Date literals used in queries and Domain functions cannot be in dd/mm/yyyy
format. It will only work (as you have seen) when the day is greater than
12. If you want to let the user enter the date as dd/mm/yyyy then you will
need to parse that string up and put it back together in the format that
Access wants or pass it into DateSerial() where there is no ambiguity about
which part is the day, month, and year.

The best practice is to use ISO format yyyy-mm-dd which Access is perfectly
happy with.
 
Thanks for the info Rick....

I need to go back and modify the three date field in my table and then
change the forms.

Cheers
 
Paul B. said:
Thanks for the info Rick....

I need to go back and modify the three date field in my table and then
change the forms.
Click to expand...

Why would you need to make any changes to the table? This concerns *formatting*
and how literals are interpreted by the query engine. The table design has no
relevance.
 
Back
Top