DLookup strange issue...

[BadgerLikeSpeed]

I have some code to look up the data from a previous week's entries to fill a form. It seems to work fine, unless the previous week crosses a month end. As in if the week runs from the 1/1/13 to 7/1/13 then then I go to the data entry form for the week starting 8/1/13 and everything is fine... But if the week runs from the 31/12/12 to the 6/1/13 then the dlookup code doesn't work.
The form asks for the starting date of the week, this is then taken to the table and the week ending date is calculated. Teh Dlookup code takes the week starting date and subtracts one day to get the previous week's ending date, and then looks up the data to fill in fields on the form. I hope that description makes sense...

Dim WkEnding As String
WkEnding = DateAdd("d", -1, [Forms]![TestEngDataNew]![WkSt])
GasOil = DLookup("[GOEnd]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
MELub = DLookup("[MELubEnd]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
AuxLub = DLookup("[AuxLubEnd]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
ZPellerLub = DLookup("[ZPelLubEnd]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
WinchLub = DLookup("[WinchLubEnd]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
PME = DLookup("[MEHPE]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
SME = DLookup("[MEHSE]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
GE1 = DLookup("[GE1E]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
GE2 = DLookup("[GE2E]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")
GE3 = DLookup("[GE3E]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")

Why would there be a difference when the week crosses a month end? It's not as though in the the example that the calculation crosses a month, as in 1/1/13 to 31/12/12, it's only 7/1/13 to 6/1/13. I had thought it was something to do with the year, but it also works accross months. As long as the whole week is in the same month then the code works. Any ideas?

 

[spikepl]

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

 

[BadgerLikeSpeed]

Thanks... I just need a little clarification. Do I need to reformat every date in the DLookup query, as in the date from [WkEnd] as well as the WkEnding date?
I'm learning this whole thing as I'm going along...

 

[gemma-the-husky]

the problem is that this

GE3 = DLookup("[GE3E]", "[WatEngDataWeek]", "[WkEnd] = #" & WkEnding & "#")

will ALWAYS try and use WkEnding in US format dates.

so a UK date like 6/1/2013 (6th Jan) will actually be construed as 1st June.

In order to get the dlookup to work you have to force the dlookup to treat the date expression as a UK date, generally with a "format" expression.

if the date is clearly not a US date - such as 28/12/2012, then there is no ambiguity, and the date is managed "correctly".

 

[pr2-eugin]

As Spikepl, has suggested you need to format the dates in US Format to make sure you will get the right data. Make use of the Wrapper function in Allen Brown's web page. (i.e. SQLDate)..

Also you are using DLookUp for every single variable, in other words you are querying the same table for 10 times to complete one transaction.. You can minimize this by using a recordset.. simple and easy, also avoids the unwanted Invalid use of Null error if in case there is no record to match..

Dim rsObj As DAO.RecordSet

Set rsObj = [COLOR=Red][B]CurrentDB.OpenRecordSet[/B][/COLOR]("SELECT [COLOR=Blue]allRequiredFields[/COLOR] FROM [COLOR=Blue]theTable[/COLOR] WHERE [COLOR=Blue]someCriteria[/COLOR]"[COLOR=Red][B])[/B][/COLOR]

someVariable = rsObj.Fields("someField")

Post back if you are struggling with that..

 

[BadgerLikeSpeed]

Would this be right?

Dim rsObj As DAO.RecordSet
Dim WkEnding As String
Dim WkEndingSQL As String
WkEnding = DateAdd("d", -1, [Forms]![TestEngDataNew]![WkSt])
WkEndingSQL = Format$(WkEnding, "\#mm\/dd\/yyyy\#")
 
Set rsObj = "SELECT [Field1], [Field2] etc. FROM [COLOR=#0000ff][WatEngDataWeek][/COLOR] WHERE [COLOR=#0000ff][WkEnd] = "& WkEndingSQL)[/COLOR]
 
GasOil = rsObj.Fields("[GOEnd]")

To get the value into the GasOil field?
I've got the SQLDate in there now thanks...

 

[pr2-eugin]

Sorry Badger, made a major blunder of missing CurrentDB.OpenRecordset..

Based on that the code would be..

Dim WkEnding As String
Dim rsObj As DAO.Recordset
Dim strSQL As String

WkEnding = DateAdd("d", -1, [Forms]![TestEngDataNew]![WkSt])
WkEndingSQL = Format$(WkEnding, "\#mm\/dd\/yyyy\#")
strSQL = "SELECT [GOEnd],[MELubEnd],[AuxLubEnd],[ZPelLubEnd],[WinchLubEnd],[MEHPE],[MEHSE],[GE1E],[GE2E],[GE3E] FROM [WatEngDataWeek] WHERE [WkEnd] =" & WkEndingSQL & "")
Set rsObj = CurrentDB.OpenRecordSet(strSQL)

If rsObj.RecordCount <> 0 Then
    GasOil = rsObj.Fields("[GOEnd]")
    MELub = rsObj.Fields("[MELubEnd]")
    AuxLub = rsObj.Fields("[AuxLubEnd]")
    ZPellerLub = rsObj.Fields("[ZPelLubEnd]")
    WinchLub = rsObj.Fields("[WinchLubEnd]")
    PME = rsObj.Fields("[MEHPE]")
    SME = rsObj.Fields("[MEHSE]")
    GE1 = rsObj.Fields("[GE1E]")
    GE2 = rsObj.Fields("[GE2E]")
    GE3 = rsObj.Fields("[GE3E]")
End If

Hope this helps..

 

[BadgerLikeSpeed]

Thanks! That looks a lot neater... but has caused me some confusion...
I went through it and updated my code, and got nothing in the text boxes. So in the 'if' statement I added a messagebox with the exact same statement - this gives me the right value, but still nothing in the text box...

GasOil = rsObj.Fields("[GOEnd]")
MsgBox (rsObj.Fields("[GOEnd]"))

Why is the data not appearing in the text box on the form when it's correct in the message box?

 

[pr2-eugin]

Make sure the text boxes are called exactly as they are defined in your form.. Also use the Me.GasOil to specifically denote the current form we are dealing with..

 

[BadgerLikeSpeed]

Thanks, that's worked, I was just having a moment

 

[pr2-eugin]

Glad you have it working.. Good Luck !