Dlookup not returning any results

[leok31]

Here is my VBA Dlookup Code:

Public Sub test()


Dim frm As Form




Set frm = Forms!StationLevelSummary


i = Nz(DLookup(" [tblPaxAtStationLevel]![number_of_days]", "tblPaxAtStationLevel", _
"[tblPaxAtStationLevel]![Time_Band]  = '" & frm.txtbox_am & "' " & _
"  And [tblPaxAtStationLevel]![station]  = '" & frm.[filter_station] & "' " & _
"AND [tblPaxAtStationLevel]![full_date] = " & frm.filter_spm_year & " "), 0)

Debug.Print frm.txtbox_am
Debug.Print frm.[filter_station]
Debug.Print frm.filter_spm_year
Debug.Print i


End Sub

Print out:
Aircraft
31/12/2013
AM Peak
0

At the bottom im printing the content of the controls which are on my form.
These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.

Any help would be appreciated..

 

[CJ_London]

normally date targets are surrounded with # so try

AND [tblPaxAtStationLevel]![full_date] = #" & frm.filter_spm_year & "#")

Although it won't affect the result, there is no need to repeat the tablename in your target and criteria and not sure if it is due te the formum formatting you have a number of unnecessary spaces

Nz(DLookup("[number_of_days]", "tblPaxAtStationLevel", _
"[Time_Band] = '" & frm.txtbox_am & "'" & _
" And [station] = '" & frm.[filter_station] & "' " & _
"AND [full_date] = #" & frm.filter_spm_year & "#"), 0)

 

[leok31]

thank you! worked

 

[CJ_London]

one other thing. The date you used is clearly 31st December but when using # Access takes the US format of mm/dd/yyyy unless the date would be invalid - so 5th June would be interpreted as 6th May for example.

So you should really use format to convert to restate the date in us format - ie. format(myDate,"mm/dd/yyyy")

 

[leok31]

good point. thanks again