DLookup has just stopped working??? HEEEELLPP! 1

[Modica82]

I have an application that has been fine for months, now all of a suddent the DLookup code no longer works, causing my application to fail??? Any ideas how this could have happened.

I am using Access 2000 on a mixture of WIndows 2000 and Windows XP Pro machines.

Please Help.

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[Remou]

Any error messages?

 

[ItIsHardToProgram]

It happens on data change or on null values if you set it in a variable..... most of time any way... also happens when you change object names.....

 

[MajP]

Probably need to show the DLOOKUP code. As asked prior, any error messages? That would help

 

[Modica82]

Fixed it!! Was to do with regional settings on the machine of all things.

Thank you for taking the time to look at my post!

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[MajP]

Rob,
I think I have a similar problem. My dlookup works on my maching (US Settings) but stopped working on the users machine (British settings). The Dlookup does reference some date fields, that I know could have issues. Any suggestions.

 

[RoyVidar]

Whenever you work with Jet or functions that are interacting with Jet, and feed them dates as strings, it must be formatted to be unambiguous for Jet to understand it.

[tt]dlookup("somefield", "sometable", "mydate = #" & date & "#")[/tt]

will work on US settings, give correct results on UK settings for all dates with higher date than 12, but not on those below, and barf on the settings I use.

[tt]dlookup("somefield", "sometable", "mydate = #" & format(date, "yyyy-mm-dd") & "#")[/tt]

Should work on any settings. The above date format is ISO 8601, also check out Allen Brownes page

http://allenbrowne.com/ser-36.html

with a bit more explanations and stuff.

Roy-Vidar

 

[MajP]

Roy,
Thanks a bunch. Make sure I understand. If I format it as format(date, "yyyy-mm-dd"),
before checking it against a date field I will be OK in either region.

Now what about time? Is there an issue. Here is the code.

  glblStartTime = CDate(TimeValue(Me.dtmRefTimePeriodStart))
  glblEndTime = CDate(TimeValue(Me.dtmRefTimePeriodEnd))
  glblSelectedDate = getSelectedDate

  If Me.ActiveControl.Value <> "Available" Then
    
   strWhere = "[DateStart] = #" &  
      DateValue(getSelectedDate) & "#"
    strWhere = strWhere & " AND CDate([TimeStart]) <= #" &  
      glblStartTime & "#"
    strWhere = strWhere & " AND CDate([TimeEnd]) >= #" & 
      glblEndTime & "#"
    strWhere = strWhere & " AND [intRoomID] = " & glblRoomID
    
glblStartTime = Nz(DLookup("[TimeStart]", "qryAllData", strWhere))

glblEndTime = Nz(DLookup("[TimeEnd]", "qryAllData", strWhere))

glblReservationID = Nz(DLookup("[autoReservationID]", "qryAllData", strWhere))

 

[MajP]

Here is the "where"

[DateStart] = #12/04/2006# AND CDate([TimeStart]) <= #10:00:00# AND CDate([TimeEnd]) >= #10:29:00# AND [intRoomID] = 1

TimeStart and TimeEnd are string fields.

 

[RoyVidar]

The full ISO 8601 is "yyyy-mm-ddThh:nn:ss", where the T is a litteral, that can be removed "yyyy-mm-dd hh:nn:ss", and then only the time format like this "hh:nn:ss".

http://www.mvps.org/access/datetime/date0005.htm

also specifies to escape the colons, which is something I haven't done jet.

There's some more general infor on ISO 8601 here

http://en.wikipedia.org/wiki/ISO_8601

But essentially, using formatting like this when building dynamic sql, concatenating date/time into the string, should make your apps safe around the world ;-)

Next step, is what happens with decimal numbers in regions using comma as decimalseparator ;-)

- just before hitting submit - are you storing date/time as text?

The CDate function within the string, will probably convert the string to a valid time, if possible, but wouldn't it do so per each row before the comparision can be performed?

When using date/time fields, one should be able to just pass valid time, and no conversion should be necessary (with the exception of formatting the criteria once when concatenating them into the string).

The date you present, is that to illustrate the point? I'd guess that could very well be the result of

[tt]strWhere = "[DateStart] = #" & date & "# ...[/tt]

with UK settings and todays date, which Jet would probably interpret as 4th December 2006 ;-)

Roy-Vidar

 

[MajP]

Roy,
Your suggestions seems to fixed it. I formatted the date as you suggested and I formatted my time as "hh:mm:ss" and it seemed to work so far. Thank god I read Tek-Tips because I do not think I would have ever figured this out. I think it did exactly what you said, and worked during the latter days of last month, but not for the earlier days of this month. I do not store date time as text, but I have to strip the time off of a reference time period and then compare it to the time from a specific days time.