Date formatting in an Access 2010 query

[cliff.clayman]

I have a Date field that I need to format and do a null check on. Here is what I tried so far and it doe not seem to be working. I am trying to compare two dates fields to see if they match.



Here is the code for the first field:

ADCC1: CDate(Format(IIf(IsNull([ADCC]),#1/1/1900#,[ADCC]),"mm/dd/yyyy"))

Here is the code for the second field. I do not need to format this field as it is already formatted properly:

LCD1: IIf(IsNull([LCD]),#1/1/1900#,[LCD])

Here is the Criteria I am trying to do on [LCD] to compare it to [ADCC]:

CDate(Format(IIf(IsNull([ADCC]),#1/1/1900#,[ADCC]),"mm/dd/yyyy"))

Please help as it is not returning the proper results!

[CJ_London]

Cdate converts a string to a date so you need to be clear are you comparing two string datatypes or two date datatypes?

you can also simply your code by using the nz function

if your fields are both date types then

nz(ADCC1,#01/01/1900#)=nz(LCD1,#01/01/1900#)

[cliff.clayman]

Both fields are date/time, but ADCC has a timestamp whereas LCD does not.

[CJ_London]

in that case

datevalue(nz(ADCC1,#01/01/1900#))=nz(LCD1,#01/01/1900#)

[cliff.clayman]

Here is what I did and it worked:

ADCC1: Format(Nz([ADCC],#1/1/1900#),"mm/dd/yyyy")

Criteria for LCD field:

<>Format(Nz([ADCC],#1/1/1900#),"mm/dd/yyyy")

[CJ_London]

converting to a string is probably slower but wont be apparent is small datasets