form selector

  • Thread starter Thread starter alan at work
  • Start date Start date
A

alan at work

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
 
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
Click to expand...
build the
 
Back
Top