Data type mismatch error -- text to date

roll-monroe-co wrote:
The only reason I haven't pulled out my hair yet is that it's too short to
grab. However, I'm about to go for the tweezers! I hope someone can help.

I have a database that I use to run reports every year. It worked great last
year (as far as I remember). This year (with a new dataset), a particular query
yields an error. I have checked that the datatypes in the new data table are all
the same as last year (date/time types for both of the source variables, DATE OF
BIRTH and ARREST DATE).

The sticking point seems to be where I try to turn a concatenated text field
into a genuine date. The variable is made up of datepart expressions that are
concatentated with "/" between them to make up a date like "m/d/yyyy".

The expression that seemed to work last year was:

Arrest_Year_Birthday: FormatDateTime(DatePart("m",[DATE OF BIRTH]) & "/" &
DatePart("d",[DATE OF BIRTH]) & "/" & DatePart("yyyy",[ARREST DATE]))

It is the creation of this variable (my use of FormatDateTime) that returns the
data type mismatch error.

To isolate the problem, I created a new var (AYB) with the concatenated text.
An IsDate expression with this returns true.

Cdate([AYB]) generates the data type mismatch error.

I've tried tacking # signs onto the beginning and end of the concatenated
string. Doesn't help.

I've tried something like Cdate(Format([AYB],"short date")). Same error (though
x:Format([AYB],"short date")) returns a lovely date).

It seems to be my effort to turn this string into a date that Access doesn't
like. This worked last year. I remember being kind of proud of my solution for
figuring out someone's age when they were arrested. I am baffled.

I have tried opening the db in both recent version of Access, I've tried
re-importing the data table, and I've tried stripping the queries and data out
and putting them in a new table.

The result for my every effort: Data type mismatch in expression.


You're trying to set a date field to a string. Use this
instead:

DateSerial(Year([ARREST DATE], Month([DATE OF BIRTH]),
Day([DATE OF BIRTH]))

--
Marsh
MVP [MS Access]