form selector

I want Access to open a form from an open one and the key
in both cases is the date. For some reason Access ALWAYS
uses the American date format when searching the 'new'
table/form and so can't find the data. Is there a way
around this. ALL PC's are using UK date format.

Below is the Basic subroutine. Do I need to alter/ modify
the [date] bit. Ive used this routine in the past but the
key was always a reference number.

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Check sheet"

stLinkCriteria = "[date]=" & "#" & Me![date] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command47_Click:
Exit Sub

Err_Command47_Click:
MsgBox Err.Description
Resume Exit_Command47_Click

End Sub

This is how dates are interpreted in Access:

a) In the interface (text box on form, datasheet, or the Criteria in query
design view), the date is interpreted according to your regional settings
(in the Windows Control Panel).

b) In VBA code and SQL strings, the date is interpreted as mm/dd/yyyy,
regardless of your regional settings. This is the only way a piece of code
can be guaranteed to work the same way, no matter where it is executed.

Therefore, you must use the American format when you build the
WhereCondition for your OpenReport, i.e.:
stLinkCriteria = "[date]=" & "#" & Format(Me![date], "mm/dd/yyyy" & "#"

For more information, see:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"alan at work" wrote in message
...
I want Access to open a form from an open one and the key
in both cases is the date. For some reason Access ALWAYS
uses the American date format when searching the 'new'
table/form and so can't find the data. Is there a way
around this. ALL PC's are using UK date format.

Below is the Basic subroutine. Do I need to alter/ modify
the [date] bit. Ive used this routine in the past but the
key was always a reference number.

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Check sheet"

stLinkCriteria = "[date]=" & "#" & Me![date] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command47_Click:
Exit Sub

Err_Command47_Click:
MsgBox Err.Description
Resume Exit_Command47_Click

End Sub

Sorted Many thanks saved a lot of head scratching!!!

-----Original Message-----
This is how dates are interpreted in Access:

a) In the interface (text box on form, datasheet, or the
Criteria in query
design view), the date is interpreted according to your
regional settings
(in the Windows Control Panel).

b) In VBA code and SQL strings, the date is interpreted
as mm/dd/yyyy,
regardless of your regional settings. This is the only
way a piece of code
can be guaranteed to work the same way, no matter where
it is executed.

Therefore, you must use the American format when you
build the