Checking if a year exists via DLookup

[walter189]

Scenario:


In the table [Entry Data] I have fields [Symbol Number] and [Date Tested] that i'm using as the basis for my DLookup. I need to see if the symbol number a user entered in a form along with a date (more specifically it's year) exist among the records. I am basically trying to debug it right now by entering data into the form that I know a record exist for but it still is telling me that i'm getting a null value.
Here's my code (any thoughts into what i'm doing wrong would be greatly appreciated:

If IsNull(DLookup("[Symbol Number]", "[Entry Data]", "[Symbol Number] = '" & Symbol_Num2 & "' AND Year([Date Tested]) = Year(" & Begin_Date & ")")) = True Then . . .

Records exist for the symbol number and year from the date field associated with the symbol number which i enter in my form

[pbaldy]

I don't see anything right off. What's the data type of [Symbol Number]? Add this line:

Debug.Print "[Symbol Number] = '" & Symbol_Num2 & "' AND Year([Date Tested]) = Year(" & Begin_Date & ")"

And check the result in the VBA Immediate window. If you don't spot the problem, post the result here.

[rpeare]

If IsNull(DLookup("[Symbol Number]", "[Entry Data]", "[Symbol Number] = '" & Symbol_Num2 & "' AND Year([Date Tested]) = Year(" & Begin_Date & ")")) = True Then . . .

Why are you looking up a value for a value you already have?

You're looking up [symbol number] when you already have the symbol number you're looking it up with.

[pbaldy]

It's a test for existence.

[rpeare]

if it were purely a test for existence it would be a dcount function, he's trying to recover a value from a dataset not find out if there are any records.

[pbaldy]

Perhaps I misunderstood this:

I need to see if the symbol number a user entered in a form along with a date (more specifically it's year) exist among the records

I personally would use DCount, but this DLookup test will still work.

[rpeare]

I think we need more input from the original poster