dlookup syntax - multiple criteria

[wilderfan]

I'm struggling with the syntax for a dlookup with multiple criteria. (Also, you'll see that my Msgbox is wrong (as I can't recall how to handle a string variable).

Any assistance would be appreciated very much.



Dim iDirectorID As Integer
iDirectorID = Nz(DLookup("nameID", "tblFunctions", "episodeID = " & _ Me!cboSelectEpisode And "Function ='Director'"), 0)

If iDirectorID > 0 Then

Dim strDirector As String
strDirector = DLookup("FirstName" & " " & "LastName", "tblNames", "nameID" = iDirectorID)
MsgBox ("strDirector is already listed as the Director of this episode.")
Exit Sub

End If

 

[vbaInet]

    Dim iDirectorID As Integer, strDirector As String
    
    iDirectorID = Nz(DLookup("nameID", "tblFunctions", "episodeID = " & _
                  Me!cboSelectEpisode & " AND [Function] ='Director'"), 0)
    
    If iDirectorID <> 0 Then
        strDirector = Nz(DLookup("FirstName & ' ' & LastName", "tblNames", "nameID" = iDirectorID), vbNullString)
        
        MsgBox strDirector & " is already listed as the Director of this episode."
    End If

I would imagine FUNCTION is a reserved keyword and must be enclosed in square brackets. You might want to rename it.

 

[wilderfan]

Thanks, vbaInet.

That was driving me crazy !

As for the use of the field name "Function", I should have realized the potential problems with it when I was setting up the tables. Completely slipped my mind.

Thanks again.

 

[vbaInet]

Happy to help!

 

[wilderfan]

Access is still indicating there is a problem - although it was able to get further into the coding.

It appears to be hung up on the coding following

strDirector =


Not sure what Access doesn't like about it.

 

[Galaxiom]

I would also recommend normalizing the values such as Director to IDs by using a lookup table. It will save time on the DLookUp because it is much faster to find a number than a string.

 

[Galaxiom]

strDirector = Nz(DLookup("FirstName & ' ' & LastName", "tblNames","nameID =" & iDirectorID ), vbNullString)

 

[wilderfan]

The message from ACCESS says

type mismatch

 

[vbaInet]

Looks like NameID is Text:

strDirector = Nz(DLookup("FirstName & ' ' & LastName", "tblNames", "nameID = '"  & iDirectorID & "'"), vbNullString)

 

[wilderfan]

It works now.

Thanks to both of you.

FYI - The Function field is limited to a list of only 5 items:

Executive Producer
Producer
Director
Writer
Actor

I wanted to introduce the If coding to the Director function because 99% of the time only 1 person directs a tv episode. With all the other functions, multiple persons can receive credit.


Cheers !

 

[Galaxiom]

Looks like NameID is Text:

strDirector = Nz(DLookup("FirstName & ' ' & LastName", "tblNames", "nameID = '"  & iDirectorID & "'"), vbNullString)

iDirectorID is an Integer so those single qoute around it should not be required.

Either that or nameID is a text field in tblNames which is an odd design choice.

I think the real problem was the variable was not concatentated in the original version.

 

[vbaInet]

I'm aware the value it holds is of type Number, but I was under the impression the OP was still referring to the strDirector line throwing a data type mismatch error even after you had given him the correct syntax. I guess he was talking about something else.

He seems to have solved the problem now though.