Data type mismatch in criteria expression in query

[Lysander]

I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the date is more than 10 months out.

The query runs fine, but I when I put the criteria of >10 I get 'Data Type mismatch' error. The code below is the original query. I have since put all the datediff bit in code, with all variables declared as date, string, integer etc and called the function in the query, again, "Data type mismatch"

I finally did the comparison in code and had a boolean function returning true or false. The query ran, but when I put in the criteria to return TRUE, I again got the 'Data Type Mismatch'

This is a critical bit of data validation and I am going mad here trying to work out what I am doing wrong

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]) AS LMPMonths
FROM tblWomen
WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null) AND ((DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]))>10));
 

[MikeTheBike]

Hi

I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the date is more than 10 months out.

The query runs fine, but I when I put the criteria of >10 I get 'Data Type mismatch' error. The code below is the original query. I have since put all the datediff bit in code, with all variables declared as date, string, integer etc and called the function in the query, again, "Data type mismatch"

I finally did the comparison in code and had a boolean function returning true or false. The query ran, but when I put in the criteria to return TRUE, I again got the 'Data Type Mismatch'

This is a critical bit of data validation and I am going mad here trying to work out what I am doing wrong

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]) AS LMPMonths
FROM tblWomen
WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null) AND ((DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]))>10));
 

This may be a long shot but try this

WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null) AND ((DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered])>10)));

I have move >10 one bracket to the left ????


MTB

[Lysander]

Hi

This may be a long shot but try this

WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null) AND ((DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered])>10)));

I have move >10 one bracket to the left ????


MTB

Thanks Mike but the SQL was created by the Access Query Editor, not typed in. In the query editor there are no brackets to move against. Interestingly, I went to the SQL view and did move the bracket, and in design mode, nothing had changed, i would have expected the design view to fail. Still got the Data Type Mismatch though.

[Lysander]

Thanks Mike but the SQL was created by the Access Query Editor, not typed in. In the query editor there are no brackets to move against. Interestingly, I went to the SQL view and did move the bracket, and in design mode, nothing had changed, i would have expected the design view to fail. Still got the Data Type Mismatch though.

Still not able to solve this problem, and its becoming critical. I have simplified the expression and have come down to just a datevalue function as below

Expr1: DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear])

All the fields are valid and correct. Expr1 returns the expected date, but as soon as I put any criteria against it, like, only return #02/03/1958# I get the Data Mismatch error. It does not seem to be returning a date (I have also tried numbers and text, nothing will let me select a criteria against Expr1.

Any ideas anyone.

[MikeTheBike]

Hi

Still not able to solve this problem, and its becoming critical. I have simplified the expression and have come down to just a datevalue function as below

Expr1: DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear])

All the fields are valid and correct. Expr1 returns the expected date, but as soon as I put any criteria against it, like, only return #02/03/1958# I get the Data Mismatch error. It does not seem to be returning a date (I have also tried numbers and text, nothing will let me select a criteria against Expr1.

Any ideas anyone.

I do not know why DateValue doen't work, its not a function I tend to use, but it does seem to work OK. The function I usualy use for this is DateSerial() so I suggest (clutching at staws) you try that just replace the DateValue() with

DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday])

and see what happens?

I assume all these arguments are integer.

MTB

[Lysander]

Hi

I do not know why DateValue doen't work, its not a function I tend to use, but it does seem to work OK. The function I usualy use for this is DateSerial() so I suggest (clutching at staws) you try that just replace the DateValue() with

DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday])

and see what happens?

I assume all these arguments are integer.

MTB

Thanks Mike, I replaced the expression with this

LMPMonths: DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]), [whenentered])
 

Running the query returns one record, with a value for LMPMonths of 592.

Put in the criteria >10 and I get Data Mismatch again. Yes, all three fields are integers (Long Number)

I think I will try to convert the table to Access 2000 (using 2003 atm) and see if I get the same result. This is doing my head in. Either its an Access bug (easy response to any problem) or I am missing something so obvious I should be shot:)

[MikeTheBike]

Thanks Mike, I replaced the expression with this

LMPMonths: DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]),[whenentered])
 

Running the query returns one record, with a value for LMPMonths of 592.

Put in the criteria >10 and I get Data Mismatch again. Yes, all three fields are integers (Long Number)

I think I will try to convert the table to Access 2000 (using 2003 atm) and see if I get the same result. This is doing my head in. Either its an Access bug (easy response to any problem) or I am missing something so obvious I should be shot:)

OK, when >10 is omitted and you return one record with 592 for LMPMonth what are the values of ALL the field in the query for that record. That just might give us a clue!

I assume [whenentered] is a date field!

MTB

[Lysander]

OK, when >10 is omitted and you return one record with 592 for LMPMonth what are the values of ALL the field in the query for that record. That just might give us a clue!

I assume [whenentered] is a date field!

MTB

Yup, WhenEntered is date/time, defaulting to Now()

When I run the query with no criteria on the relevant expression I get

WomenKey 00580001
LMPMonths 592 (This I what I want to select on)
CurrentlyPregnant 1
IfyesLMPDay 2
IfyesLMPMonth 3
IfyesLMPYear 1958

query SQL is

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]),[whenentered]) AS LMPMonths, tblWomen.CurrentlyPregnant, tblWomen.IfyesLMPDay, tblWomen.IfyesLMPMonth, tblWomen.IfyesLMPYear
FROM tblWomen
WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null));
 

As soon as I put any criteria against LMPMonths, I get data mismatch.

Just out of interest, this query is running a validation against a survey of 80,000 women. It's looking for women who have been entered as pregnant for more than 10 months.

[mlcampeau]

Not sure if this would do anything, but in your last post you have

DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday])

(year, month, day)
and in your first post you have

DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear])

(day, month, year)
I don't know enough about DateDiff() to know if the order matters - I would assume it needs to be input in the same order that [whenentered] is. Access Help shows dates as month, day, year.

[FishVal]

Yup, WhenEntered is date/time, defaulting to Now()

When I run the query with no criteria on the relevant expression I get

WomenKey 00580001
LMPMonths 592 (This I what I want to select on)
CurrentlyPregnant 1
IfyesLMPDay 2
IfyesLMPMonth 3
IfyesLMPYear 1958

query SQL is

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]),[whenentered]) AS LMPMonths, tblWomen.CurrentlyPregnant, tblWomen.IfyesLMPDay, tblWomen.IfyesLMPMonth, tblWomen.IfyesLMPYear
FROM tblWomen
WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null));
 

As soon as I put any criteria against LMPMonths, I get data mismatch.

Just out of interest, this query is running a validation against a survey of 80,000 women. It's looking for women who have been entered as pregnant for more than 10 months.

Hi, Lysander.

I'm not sure whether it will help. Just my 0.2 cents.

Try to avoid using alias name in WHERE clause.
1) try to use full expression instead (I know this is ugly)
2) build another query with criteria for LMPMonths based on this one

[Lysander]

Not sure if this would do anything, but in your last post you have

DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday])

(year, month, day)
and in your first post you have

DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear])

(day, month, year)
I don't know enough about DateDiff() to know if the order matters - I would assume it needs to be input in the same order that [whenentered] is. Access Help shows dates as month, day, year.

Thanks Mary, I was following up a suggestion from MikeTheBike. The 1st query used DateValue, the second DateSerial. The order is different in both,

[Lysander]

Hi, Lysander.

I'm not sure whether it will help. Just my 0.2 cents.

Try to avoid using alias name in WHERE clause.
1) try to use full expression instead (I know this is ugly)
2) build another query with criteria for LMPMonths based on this one

Not sure what you mean by alias name. I am building the query using the query builder, not typing in the SQL directly.

Have tried step 2, same problem. I have even written a function that I pass the 4 fields to. The function works out the date difference and then returns true or false. True if more than 10 months old. Function works, query runs, but try selecting True only, and you get Data Type Mismatch again!

[Lysander]

I have finally worked out whats going on, but still don't know how to fix it. Without the criteria on the date field, the query displays all records, where day,month,year are not null. When I put a criteria on the date field, Access must be validating the date field, before the other criteria. I.E it is trying to do a datediff when the day,month and year are null. I proved this by making a tempoary table with all the day,month,year info in every record, and the query works fine.

Short of running a maketable query to house only wanted records, any idea how I can force access to apply the date selection after everything else.

[MikeTheBike]

I have finally worked out whats going on, but still don't know how to fix it. Without the criteria on the date field, the query displays all records, where day,month,year are not null. When I put a criteria on the date field, Access must be validating the date field, before the other criteria. I.E it is trying to do a datediff when the day,month and year are null. I proved this by making a tempoary table with all the day,month,year info in every record, and the query works fine.

Short of running a maketable query to house only wanted records, any idea how I can force access to apply the date selection after everything else.

First well found.

The only way, so far(!), I've found round this is

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateSerial(NZ([ifyeslmpyear],Year(Date())), Nz([ifyeslmpmonth],Month(Date())),Nz([ifyeslmpday],Day(Date()))),[whenentered]) AS LMPMonths, tblWomen.CurrentlyPregnant, tblWomen.ifyeslmpday, tblWomen.ifyeslmpmonth, tblWomen.ifyeslmpyear
FROM tblWomen
WHERE (((DateDiff("m",DateSerial(NZ([ifyeslmpyear],Year(Date())), Nz([ifyeslmpmonth],Month(Date())),Nz([ifyeslmpday],Day(Date()))),[whenentered]))>10) AND ((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.ifyeslmpday) Is Not Null) AND ((tblWomen.ifyeslmpmonth) Is Not Null) AND ((tblWomen.ifyeslmpyear) Is Not Null));

Mark 2

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, IIf(IsNull([ifyeslmpyear]) Or IsNull([ifyeslmpmonth]) Or IsNull([ifyeslmpday]),0,DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth], [ifyeslmpday]),[whenentered])) AS LMPMonths, tblWomen.CurrentlyPregnant, tblWomen.ifyeslmpday, tblWomen.ifyeslmpmonth, tblWomen.ifyeslmpyear
FROM tblWomen
WHERE (((IIf(IsNull([ifyeslmpyear]) Or IsNull([ifyeslmpmonth]) Or IsNull([ifyeslmpday]),0,DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth], [ifyeslmpday]),[whenentered])))>10) AND ((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.ifyeslmpday) Is Not Null) AND ((tblWomen.ifyeslmpmonth) Is Not Null) AND ((tblWomen.ifyeslmpyear) Is Not Null));

Both these seen to work?

MTB

[Lysander]

First well found.

The only way, so far(!), I've found round this is

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateSerial(NZ([ifyeslmpyear],Year(Date())), Nz([ifyeslmpmonth],Month(Date())),Nz([ifyeslmpday],Day(Date()))),[whenentered]) AS LMPMonths, tblWomen.CurrentlyPregnant, tblWomen.ifyeslmpday, tblWomen.ifyeslmpmonth, tblWomen.ifyeslmpyear
FROM tblWomen
WHERE (((DateDiff("m",DateSerial(NZ([ifyeslmpyear],Year(Date())), Nz([ifyeslmpmonth],Month(Date())),Nz([ifyeslmpday],Day(Date()))),[whenentered]))>10) AND ((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.ifyeslmpday) Is Not Null) AND ((tblWomen.ifyeslmpmonth) Is Not Null) AND ((tblWomen.ifyeslmpyear) Is Not Null));

Mark 2

SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, IIf(IsNull([ifyeslmpyear]) Or IsNull([ifyeslmpmonth]) Or IsNull([ifyeslmpday]),0,DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth], [ifyeslmpday]),[whenentered])) AS LMPMonths, tblWomen.CurrentlyPregnant, tblWomen.ifyeslmpday, tblWomen.ifyeslmpmonth, tblWomen.ifyeslmpyear
FROM tblWomen
WHERE (((IIf(IsNull([ifyeslmpyear]) Or IsNull([ifyeslmpmonth]) Or IsNull([ifyeslmpday]),0,DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth], [ifyeslmpday]),[whenentered])))>10) AND ((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.ifyeslmpday) Is Not Null) AND ((tblWomen.ifyeslmpmonth) Is Not Null) AND ((tblWomen.ifyeslmpyear) Is Not Null));

Both these seen to work?

MTB

MikeTheBike you de man. I was so bogged down the data type mismatch error I didn't think of this. Cut and pasted Mark 2 into my system and it works. 80,000 women and their children thank you:)

[Marknut]

This is the fix!
I had the same issue, and thanks to this thread I was able to fix it. In case this helps break it down...
Overview: Dates can be null, and limiting your query to Not Null dates will not matter because the query looks at all data before the Where clause. It only gives you an error if you put anything in the Critria expression.
Error Formula: DateDiff('s',[Task_Due_Date],Now()) > 0
Correct Formula: DateDiff('s',nz([Task_Due_Date],0),Now()) > 0
Adding nz([Task_Due_Date],0) removes all null dates when the expression is run.

[Lysander]

Glad to see an issue I raised 3 years ago still helping today. I really need to get back to spending more time in these forums:)

[heardamir]

Hi there,

I have a similar Data Mismatch problem when entering criteria into field that calls a function. The function is used to calculate and return in days (integer) between something was received and today's date.

The expression in SLA_Days, looks at two separate start date fields (and takes the Is Not Null) and calculates the number of days, since a request was received, in business days (as an integer).

The query works perfectly, until i try to add a ctireria expression. For example. I want to display all records where "[Days_to_SLA]" is Less than 2. DATA MISMATCH. I've tried using the nz() expression, but still can't get any love.

Access 2003 (Access 2000 format), Win XP

Hope you can help me :S

SELECT qry_pp_myqueue.wr_ID, qry_pp_myqueue.wr_Client_Name, [usr_First_Name] & " " & [usr_last_name] AS Adviser, tbl_doc_type.doc_type_desc AS Document, tbl_job_state.jb_ste_desc AS [Job State], qry_pp_myqueue.wr_date_allocated, qry_pp_myqueue.wr_pp_dte_full_inf_rec, qry_pp_myqueue.wr_SLA AS Default_SLA, CInt(IIf(IsNull([wr_pp_dte_full_inf_rec]),SLACALC([wr_SLA],[wr_date_allocated]),SLACALC([wr_SLA],nz([wr_pp_dte_full_inf_rec])))) AS SLA_Days, CInt([wr_SLA]-[SLA_Days]) AS Days_to_SLA
FROM ((qry_pp_myqueue INNER JOIN tbl_users ON qry_pp_myqueue.wr_Adviser = tbl_users.usr_id_no) INNER JOIN tbl_doc_type ON qry_pp_myqueue.wr_doc_type = tbl_doc_type.doc_type_ID) INNER JOIN tbl_job_state ON qry_pp_myqueue.wr_Job_state = tbl_job_state.jb_ste_ID
ORDER BY qry_pp_myqueue.wr_Client_Name, qry_pp_myqueue.wr_ID;

damir -

[Marknut]

I'm not sure what the issue is at the moment, but here are a couple options:

1. The NZ function needs a value if null. i.e. NZ([Date_Field], 0)
2. The IIF(IsNull(... portion might be the issue. Maybe try something like IIF(NZ([Date_Field],0)=0,...

[JCollado]

I had a similar problem when converting a project from sql server to access 2002.

I solve the problem by replacing the = with LIKE in the critera.

it worked