Dlookup Data type mismatch

[Directlinq]

I hope you can help?
Why is the code causing this error?

vididmax = Nz(DMax("video_id", "TblVideo"))
dtan = DLookup("[dtan_lib_no]", "[TblVideo]", "[video_id]=""" & vididmax & """")
MsgBox dtan

Data type mismatch

Im trying to look up the highest video_id and get the data from field dtan_lib_no into a variable dtan
Many Thanks

 

[pbaldy]

If the field is numeric, you don't want quotes around the value:

http://www.mvps.org/access/general/gen0018.htm

You may also have a problem if the DLookup returns Null and dtan is declared as anything but a Variant.

 

[Directlinq]

I must have tried almost every combination and i cannot get this to work.
Could anybody give me a hand please.
Is there another way i can achieve the same rtesult if i cant get this working?

dtan_lib_no = text (eg. DTV03000)

video_id = number (eg. 1263)

vididmax = string (eg. 12222)

dtan = variant

My whole code

Dim dtan as variant
Dim vididmax as string
 
vididmax = Nz(DMax("video_id", "TblVideo"))
dtan = DLookup("[dtan_lib_no]", "[TblVideo]", "[video_id]=""" & vididmax & """")
MsgBox dtan

There is no reason dlookup should return null as there is always going to be a highest video number.
I have checked your link and i have tried all of them.
Why is it not working

Many Thanks

 

[pbaldy]

If video ID is numeric, you don't want the extra quotes around it. That's the relevant data type, not the data type of the field being returned. That would mean that variable should be numeric as well.

 

[Chrisopia]

dtan = DLookup("[dtan_lib_no]", "[TblVideo]", "[video_id]=""" & vididmax & """")

this line looks iffy?

"[video_id]=" & vididmax )


too many quote marks? is vididmax a variable? or a control?

 

[Scooterbug]

dtan = DLookup("[dtan_lib_no]", "[TblVideo]", "[video_id]=""" & vididmax & """")

this line looks iffy?

"[video_id]=" & vididmax )


too many quote marks? is vididmax a variable? or a control?

It's declared vididmax as a variable and used dmax to find the ID number so it's a variable.

What pbaldy is saying is that since the criteria of your dlookup is based on the Video_ID on your table, then it's looking to plug in a number. By putting the extra quotes around the variable, you are in effect telling access that the value you are plugging in is a string. For reference:

When it's a number: No quotes, no nothing
When it's a Text: Need quotes
When it's a date: wrap it with #s