Date field problems (I'm pretty sure)

[Sullaway]

Good morning all,

Access 97

I'm trying to use a date field (date of birth) as part of a process to check and see if the customer already exist in our db. I'm using a DLookup variant (varDOB) to search the db for a matching DOB then in my code I'm asking if the date of birth typed in this new record matches varDOB to fire a message.

In my testing I picked a record that I know matches but never get the message. I'm using other fields besides the DOB field to see if there's a matching record. I have tried each field individually to see if they work and they all do except the DOB, so I'm thinking it has something to do with it being a date field.
Could someone lend a hand on how to do this? Please!

Heres the part of the code for DOB

Dim varDOB As Variant
varDOB = DLookup("DOB", "Customers", "[DOB] = Forms![Customers]![DOB]&quot

If Me.DOB = varDOB Then
MsgBox "etc,etc"

TIA
Shane

 

[hsitz]

I'm not sure. But you might try putting # signs around the variant reference to see if that makes it be treated as a date:

if me.DOB = #varDOB# Then . . .

 

[Sullaway]

Thanks hsitz, but it didn't work. It fired an error message stating that I had a syntax error and identified where I had put the # signs.

 

[hsitz]

Is the me.DOB reference to a date field in a the underlying record source, or to a text box control? If it's to a text box control, then that may be where you need to put the # signs:

if #me.DOB# = varDOB then . . .

 

[tlbroadbent]

Use the # signs in the DLookup call.

varDOB = DLookup("DOB", "Customers", "[DOB] = #" & Forms![Customers]![DOB] & "#&quot Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[hsitz]

Darn! Should have been obvious. Thanks Terry.