date format issues

[mazza]

When using the below function is an english/US date settings envrionment the funcionad is running fine, hwr when I move this database to a german environment where the date format is 01.01.2008 instead of 01/01/2008 it will not return a value.

the TBLPeriod.StartDate for example = 01/04/2008


So comparing . with / isn't working

what would be the easiest solution, as it might happen to other date formats around the world



Function getcurrentPeriod() As String
Dim SQLData As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Use in query criteria
'**************************************
SQLData = "SELECT TBLPeriod.PeriodNo, TBLPeriod.StartDate, TBLPeriod.EndDate FROM TBLPeriod WHERE (((TBLPeriod.StartDate)<Date()) AND ((TBLPeriod.EndDate)>Date()));"
Set db = CurrentDb()

Set rs = db.OpenRecordset(SQLData, dbOpenDynaset, dbSeeChanges)

'A = DLookup("Periodno", "TblPeriod", "[startdate] < date" And "[enddate] > date")
getcurrentPeriod = rs!periodNo
'**************************************
End Function

 

[ecawilkinson]

I don't know enough about the format in which you hold your dates in the table but there is a little trick that certain works for the confusion between some date formats. try using CLng(Date()) instead of just Date(). This should work if startdate and enddate are defined as date datatype.

HTH,
Chris