Null Value results in Data Mismatch

[hlock]

I have created two fields. The first one converts a text formatted YYYYMMDD
to a date:

Date Added: CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@"))

The second one calculates the difference of the Date Added (shown above) and
the current date:

Days:
DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),CDate(Date()))

This works fine as long as I don't have any criteria for Days. However when
I put in a criteria of >45 for days, I get a Datatype Mismatch error. I
looked at my data without the criteria and saw that I have a few zeros in the
Days field because the date added is the same as today's date.

I tried putting <>Date() as criteria in the Date Added field, but I'm still
getting an error. Can you suggest a resolution. Thanks!

 

[KARL DEWEY]

Try this without criteria then with --
Days:
Val(DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Date()))

 

[hlock]

Adding the Val() worked without the criteria, however, when I added the
criteria, I got the same mismatch error message again. Any other thoughts?

Try this without criteria then with --
Days:
Val(DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Date()))

--
KARL DEWEY
Build a little - Test a little

I have created two fields. The first one converts a text formatted YYYYMMDD
to a date:

Date Added: CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@"))

The second one calculates the difference of the Date Added (shown above) and
the current date:

Days:
DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),CDate(Date()))

This works fine as long as I don't have any criteria for Days. However when
I put in a criteria of >45 for days, I get a Datatype Mismatch error. I
looked at my data without the criteria and saw that I have a few zeros in the
Days field because the date added is the same as today's date.

I tried putting <>Date() as criteria in the Date Added field, but I'm still
getting an error. Can you suggest a resolution. Thanks!

 

[John Spencer]

You are going to generate an error anytime DteAdd cannot be interpreted as a date.

So you need to use something like the following to calculate the date.
IIF(IsDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),
CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Null)


That will make your expression read

DateDiff("d",IIF(IsDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),
CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Null),(Date())

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Adding the Val() worked without the criteria, however, when I added the
criteria, I got the same mismatch error message again. Any other thoughts?

Try this without criteria then with --
Days:
Val(DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Date()))

--
KARL DEWEY
Build a little - Test a little

I have created two fields. The first one converts a text formatted YYYYMMDD
to a date:

Date Added: CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@"))

The second one calculates the difference of the Date Added (shown above) and
the current date:

Days:
DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),CDate(Date()))

This works fine as long as I don't have any criteria for Days. However when
I put in a criteria of >45 for days, I get a Datatype Mismatch error. I
looked at my data without the criteria and saw that I have a few zeros in the
Days field because the date added is the same as today's date.

I tried putting <>Date() as criteria in the Date Added field, but I'm still
getting an error. Can you suggest a resolution. Thanks!

 

[hlock]

Perfect, it worked!!!! Thank you so much!

You are going to generate an error anytime DteAdd cannot be interpreted as a date.

So you need to use something like the following to calculate the date.
IIF(IsDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),
CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Null)


That will make your expression read

DateDiff("d",IIF(IsDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),
CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Null),(Date())

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Adding the Val() worked without the criteria, however, when I added the
criteria, I got the same mismatch error message again. Any other thoughts?

Try this without criteria then with --
Days:
Val(DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),Date()))

--
KARL DEWEY
Build a little - Test a little


:

I have created two fields. The first one converts a text formatted YYYYMMDD
to a date:

Date Added: CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@"))

The second one calculates the difference of the Date Added (shown above) and
the current date:

Days:
DateDiff("d",CDate(Format([QRYDWXC020]![DTEADD],"@@@@-@@-@@")),CDate(Date()))

This works fine as long as I don't have any criteria for Days. However when
I put in a criteria of >45 for days, I get a Datatype Mismatch error. I
looked at my data without the criteria and saw that I have a few zeros in the
Days field because the date added is the same as today's date.

I tried putting <>Date() as criteria in the Date Added field, but I'm still
getting an error. Can you suggest a resolution. Thanks!