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


[code=sql]
SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",Da teValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]) AS LMPMonths
FROM tblWomen
WHERE (((tblWomen.Cur rentlyPregnant) =True) AND ((tblWomen.Ifye sLMPDay) Is Not Null) AND ((tblWomen.Ifye sLMPMonth) Is Not Null) AND ((tblWomen.Ifye sLMPYear) Is Not Null) AND ((DateDiff("m", DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]))>10));
[/code]

[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


[code=sql]
SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",Da teValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]) AS LMPMonths
FROM tblWomen
WHERE (((tblWomen.Cur rentlyPregnant) =True) AND ((tblWomen.Ifye sLMPDay) Is Not Null) AND ((tblWomen.Ifye sLMPMonth) Is Not Null) AND ((tblWomen.Ifye sLMPYear) Is Not Null) AND ((DateDiff("m", DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered]))>10));
[/code]

This may be a long shot but try this

WHERE (((tblWomen.Cur rentlyPregnant) =True) AND ((tblWomen.Ifye sLMPDay) Is Not Null) AND ((tblWomen.Ifye sLMPMonth) Is Not Null) AND ((tblWomen.Ifye sLMPYear) 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.Cur rentlyPregnant) =True) AND ((tblWomen.Ifye sLMPDay) Is Not Null) AND ((tblWomen.Ifye sLMPMonth) Is Not Null) AND ((tblWomen.Ifye sLMPYear) 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)
CurrentlyPregna nt 1
IfyesLMPDay 2
IfyesLMPMonth 3
IfyesLMPYear 1958

query SQL is
[code=sql]
SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",Da teSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]),[whenentered]) AS LMPMonths, tblWomen.Curren tlyPregnant, tblWomen.IfyesL MPDay, tblWomen.IfyesL MPMonth, tblWomen.IfyesL MPYear
FROM tblWomen
WHERE (((tblWomen.Cur rentlyPregnant) =True) AND ((tblWomen.Ifye sLMPDay) Is Not Null) AND ((tblWomen.Ifye sLMPMonth) Is Not Null) AND ((tblWomen.Ifye sLMPYear) Is Not Null));
[/code]

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)
CurrentlyPregna nt 1
IfyesLMPDay 2
IfyesLMPMonth 3
IfyesLMPYear 1958

query SQL is
[code=sql]
SELECT Format([Village_ID],"0000") & Format([Womens_ID],"0000") AS WomenKey, DateDiff("m",Da teSerial([ifyeslmpyear],[ifyeslmpmonth],[ifyeslmpday]),[whenentered]) AS LMPMonths, tblWomen.Curren tlyPregnant, tblWomen.IfyesL MPDay, tblWomen.IfyesL MPMonth, tblWomen.IfyesL MPYear
FROM tblWomen
WHERE (((tblWomen.Cur rentlyPregnant) =True) AND ((tblWomen.Ifye sLMPDay) Is Not Null) AND ((tblWomen.Ifye sLMPMonth) Is Not Null) AND ((tblWomen.Ifye sLMPYear) Is Not Null));
[/code]

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:)