By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,010 Members | 2,774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,010 IT Pros & Developers. It's quick & easy.

Data type mismatch in criteria expression in query

Expert 100+
P: 344
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


Expand|Select|Wrap|Line Numbers
  1. SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]) AS LMPMonths
  2. FROM tblWomen
  3. 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));
  4.  
Jul 19 '07 #1
Share this Question
Share on Google+
19 Replies


Expert 100+
P: 634
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


Expand|Select|Wrap|Line Numbers
  1. SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]) AS LMPMonths
  2. FROM tblWomen
  3. 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));
  4.  
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
Jul 19 '07 #2

Expert 100+
P: 344
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.
Jul 19 '07 #3

Expert 100+
P: 344
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.
Jul 24 '07 #4

Expert 100+
P: 634
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
Jul 24 '07 #5

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. LMPMonths: DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]), [whenentered])
  2.  
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:)
Jul 24 '07 #6

Expert 100+
P: 634
Thanks Mike, I replaced the expression with this
Expand|Select|Wrap|Line Numbers
  1. LMPMonths: DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]),[whenentered])
  2.  
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
Jul 25 '07 #7

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblWomen
  3. WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null));
  4.  
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.
Jul 25 '07 #8

Expert 100+
P: 296
Not sure if this would do anything, but in your last post you have
Expand|Select|Wrap|Line Numbers
  1. DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday])
(year, month, day)
and in your first post you have
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 25 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblWomen
  3. WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null));
  4.  
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
Jul 25 '07 #10

Expert 100+
P: 344
Not sure if this would do anything, but in your last post you have
Expand|Select|Wrap|Line Numbers
  1. DateDiff("m",DateSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday])
(year, month, day)
and in your first post you have
Expand|Select|Wrap|Line Numbers
  1. 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,
Jul 26 '07 #11

Expert 100+
P: 344
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!
Jul 26 '07 #12

Expert 100+
P: 344
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.
Jul 26 '07 #13

Expert 100+
P: 634
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblWomen
  3. 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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblWomen
  3. 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
Jul 26 '07 #14

Expert 100+
P: 344
First well found.

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblWomen
  3. 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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblWomen
  3. 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:)
Jul 26 '07 #15

P: 42
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.
Apr 5 '10 #16

Expert 100+
P: 344
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:)
Apr 6 '10 #17

P: 1
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. ORDER BY qry_pp_myqueue.wr_Client_Name, qry_pp_myqueue.wr_ID;
damir -
May 26 '10 #18

P: 42
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,...
May 26 '10 #19

P: n/a
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
Nov 19 '10 #20

Post your reply

Sign in to post your reply or Sign up for a free account.