R
roll-monroe-co
Hello,
The only reason I haven't pulled out my hair yet is that it's too short t
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 las
year (as far as I remember). This year (with a new dataset), a particular quer
yields an error. I have checked that the datatypes in the new data table are al
the same as last year (date/time types for both of the source variables, DATE O
BIRTH and ARREST DATE).
The sticking point seems to be where I try to turn a concatenated text fiel
into a genuine date. The variable is made up of datepart expressions that ar
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 th
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 concatenate
string. Doesn't help.
I've tried something like Cdate(Format([AYB],"short date")). Same error (thoug
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'
like. This worked last year. I remember being kind of proud of my solution fo
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 trie
re-importing the data table, and I've tried stripping the queries and data ou
and putting them in a new table.
The result for my every effort: Data type mismatch in expression.
Any ideas? Thanks for any help you can provide.
Eric
The only reason I haven't pulled out my hair yet is that it's too short t
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 las
year (as far as I remember). This year (with a new dataset), a particular quer
yields an error. I have checked that the datatypes in the new data table are al
the same as last year (date/time types for both of the source variables, DATE O
BIRTH and ARREST DATE).
The sticking point seems to be where I try to turn a concatenated text fiel
into a genuine date. The variable is made up of datepart expressions that ar
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 th
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 concatenate
string. Doesn't help.
I've tried something like Cdate(Format([AYB],"short date")). Same error (thoug
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'
like. This worked last year. I remember being kind of proud of my solution fo
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 trie
re-importing the data table, and I've tried stripping the queries and data ou
and putting them in a new table.
The result for my every effort: Data type mismatch in expression.
Any ideas? Thanks for any help you can provide.
Eric