date problem 2007

[Lizba]

Hi
Can someone help me with a date problem.
We have recently upgraded to 2007. In 2003, all our dates were yyyy/mm/dd. In 2007, the regional settings for South Africa, is now dd/mm/yyyy . All the forms in the program are using this format. The problem now is: On the booking schedule, if you want to see who is in a particular room, you double click on the block and it will open a popup form with the relevant information.
The code on the booking schedule is:
stLinkCriteria = "HouseDetailID = " & HseID
stLinkCriteria = stLinkCriteria & " And Date = #" & BookedDate & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal

Now what happens is, if the day is < the 13th of the month, it will look for mm/dd/yyyy
If the date is >= 13th then it look for dd/mm/yyyy

Ie: if the block is for 1st March 2010, it will find a booking for 3rd January 2010, If the block is for 13th March 2010, it will correctly find the booking for that date.

The format on the schedule and the popup form are both dd/mm/yyyy.
Please help!!!
Lizba

 

[ghudson]

You can use the format function to display the date however you want it.

format([YourDate],"yyyy/mm/dd")

You can also change the users regional date to the format you want.

 

[gemma-the-husky]

if it helps, heres a module to check the locale/regional settings

Option Compare Database
Option Explicit

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SDECIMAL As Long = &HE
Private Const LOCALE_ILDATE As Long = &H22
Private Const LOCALE_ICOUNTRY As Long = &H5
Private Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country
Private Const LOCALE_SENGLANGUAGE = &H1001 ' English name of language
Private Const LOCALE_SNATIVELANGNAME = &H4 ' native name of language
Private Const LOCALE_SNATIVECTRYNAME = &H8 ' native name of country

Public Function GetInfo(ByVal lInfo As Long) As String
Dim Buffer As String
Dim Ret As String
Buffer = String$(256, 0)
Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
If Ret > 0 Then
GetInfo = Left$(Buffer, Ret - 1)
Else
GetInfo = ""
End If

End Function

Function locale_UK() As Boolean
locale_UK = GetInfo(LOCALE_ICOUNTRY) = 44
End Function

Function locale_string() As String
locale_string = GetInfo(LOCALE_SENGCOUNTRY)
End Function

 

[Lizba]

Thanks for your suggestions.
I eventually solved the problem another way, I created a table called "Dates", where I pasted a list of dates from 2007 to 2035 (by then I should be retired!!) with an autonumber ID. So now I just use the ID to open the correct form irrespective of whatever date format is used.
Thanks again
Lizba