If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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] |
Thread Tools | |
Display Modes | |
|
|