DLookup not recognizing valid record/type mismatch

[walter189]

So let me lay out the scenario:

I have two tables, one called [Entry Data] with a field [Symbol Number] and another table called [Symbol Number] which has primary key field also called Symbol Number. I populate a combobox in my form [ItemNumberSearch] with the primary keys of [Symbol Number] and once a search button is pressed I am trying to populate a report based off of all records with that symbol number. In order to check and make sure that symbol number exists in the records I have been using the code:

1) If DLookup("[Symbol Number]", "[Entry Data]", "[Symbol Number] = '" & _ Forms![ItemNumberSearch]!Symbol_Num2 & "'") Then . . . .

(Symbol_Num2 is the name of the present value of the combobox)

This gave data type mismatch so then i tried
2) If DLookup("[Symbol Number]", "[Entry Data]", "[Symbol Number] = '" & _ Forms![ItemNumberSearch]!Symbol_Num2 & "'") Then . . . .

which got rid of the data type mismatch but now says the symbol number doesn't exist (when i know it does)

I looked at the field types of both Symbol Numbers and they are both text fields. I'm not sure why DLookup wouldn't be able to find this data when i have used it to look up other data earlier in my code and it worked fine?

[ketbdnetbp]

In VBA, try -

If IsNull(DLookup("[Symbol Number]", "[Entry Data]", "[Symbol Number] = " & Forms![ItemNumberSearch]!Symbol_Num2))= false Then . . . .
MsgBox "Record Found!"

See if that gets you what you want.

All the best,

Jim

[rpeare]

You said this was your if statement:

If DLookup("[Symbol Number]", "[Entry Data]", "[Symbol Number] = '" & _ Forms![ItemNumberSearch]!Symbol_Num2 & "'") Then

The previous poster noted one problem, you are using an IF statement but you are not evaluating anything you have to have something like:

if <dlookupstatement> = <something> then
or
if <dlookupstatement> >= <something> then
etc.

Secondly. write a line that shows whether or not your code is correctly finding something with your dlookup function.

debug.print <result of dlookup>

domain functions (sum, lookup, count, max, min, etc) can be very cantankerous if you are trying to supply a criteria that is a variable rather than a static value.

[walter189]

Thank you guys. My boss has asked me to create a database in access and I'm near completion but had to teach myself access over the past few weeks so it's been interesting to say the least. The problem with type mismatch was indeed not comparing the dlookup to anything. Thanks again!