DLookup Issue

[ElvisR]

I have a table I am running DLOOKUP on to get data. It works as it should, except for when the Criteria is "QUEENS" - it does not pull any data from the table. In VB's immediate window, it does not print any data.

Please see if anything is off in the code. Suggestions welcomed. Thanks.

Public Function GetDutyStation()
Dim strCity As String
Dim strCounty As String
Dim strState As String
Dim strDSC As String

strCity = Nz(Me.CITY.Value)
strCounty = Nz(Me.cboCOUNTY.Value)
strState = Nz(Me.cboSTATE.Value)

If strCounty <> "" Then
If strCounty = "KINGS" Then
strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"))
End If
If strCounty = "QUEENS" Then
strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"))
MsgBox "This is Queens"
End If
If strCounty = "NEW YORK" Then
strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"))
End If
If strCounty = "RICHMOND" Then
strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"))
End If
If strCounty = "BRONX" Then
strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"))
End If

Else
MsgBox "A Duty Station Code was not found. Please lookup duty station code"
Me.txtDutyStationCode.SetFocus
End If
If strCity <> "" Then
If strState <> "" Then
strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[CITY]=""" & strCity & """ and [STATE]=""" & strState & """"))
End If
End If
Debug.Print strDSC
Me.txtDutyStationCode.Value = strDSC
End Function

 

[missinglinq]

I think that first you need to go into your code and change all of your statements that look like

If strCounty <> "" Then

The problem is that your code is going to fire unless the given Variable, in the above case, strCounty, is not equal to a Zero-Length String. This means that if cboCOUNTY is simply empty, which is to say Null, the code will fire! A Zero-Length String, i.e. "", is not the same as Null!

Correct this with one of the alternatives to checking for ZLSs and Nulls, and see what happens.

Instead of

If strCounty <> "" Then

use

If Nz(strCounty,"") <> "" Then

or

If Len(strCounty & "") <> 0 Then

Linq ;0)>

 

[Cronk]

I'd suggest that after the line
MsgBox "This is Queens"
you add another line
Debug.print strDSC, "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"

This will show the intermediate values.

 

[TJPoorman]

have you tried running a query with your criteria to see what is returned?

 

[TJPoorman]

I would rewrite your code a little. See if this works for you:

Public Function GetDutyStation()
Dim strCity As String
Dim strCounty As String
Dim strState As String
Dim strDSC As String

strCity = Nz(Me.CITY.Value, "")
strCounty = Nz(Me.cboCOUNTY.Value, "")
strState = Nz(Me.cboSTATE.Value, "")
 
If strState = "" Then
    MsgBox "You must select a state"
    Exit Sub
End If

If strCounty <> "" Then
    strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[COUNTY]=""" & strCounty & """ and [STATE]=""" & strState & """"), "")
End If

If strCity <> "" Then
    strDSC = Nz(DLookup("[DUTY STATION CODE]", "DUTY STATION CODES", "[CITY]=""" & strCity & """ and [STATE]=""" & strState & """"), "")
End If
 
If strDSC = "" Then
    MsgBox "A Duty Station Code was not found. Please lookup duty station code"
    Me.txtDutyStationCode.SetFocus
End If

'Debug.Print strDSC
Me.txtDutyStationCode.Value = strDSC
End Function

 

[ElvisR]

Thanks for all the qualified responses.
I used to TJ's abbreviated code, and included Cronk's suggestion to debug.

Results: No change. Well, I am now getting the message box that the code was not found when Queens is the county. I eliminated the other 3000+ codes and left only 5 just so I can troubleshoot. The debug print does display the right criteria ([COUNTY]="QUEENS" and [STATE]="NY"), but the code returned is blank.

I also ran a query, and it does pull the records from the other table with Queens county, and shows the code from the Duty Station Codes table.

I checked for extra spaces in the table already.
Is Queens a reserved word or something.... geez

 

[TJPoorman]

Would it be possible for you to upload a stripped down version of your db with some sample data?

 

[Cronk]

In the meantime, now that you have ensured the criteria is what you want, there must me something with the data in the table.

What I'd do is to create a query with this criteria and then play with easing the criteria such as [COUNTY] like"QUE*" without the STATE and seeing what [DUTY STATION CODE] and STATE values are returned.

 

[ElvisR]

Issue solved. It was a rookie mistake: I was ref a field [COUNTY] that I did not have in the table. It should have been:

[CITY]=""" & strCounty

Confusing I know, but its New York geography. Thanks all!

 

[Cronk]

Pleased to hear you found the problem. And it's not necessarily a rookie mistake. You just have to develop trouble shooting skills.