Help With Error Please?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I built a database used once a quarter and it has worked in the past, but I
am now getting an error “Data Type Mismatch in Criteria Expression†when the
following query is run:

SELECT qry_AgeValidation.[Acct#], qry_AgeValidation.Name,
qry_AgeValidation.DateOfBirth, qry_AgeValidation.DOD,
qry_AgeValidation.DateandTime, qry_AgeValidation.Result,
qry_AgeValidation.[Age At Test], qry_AgeValidation.Physician,
IIf([Result]>=[TBIL_75th],"Yes",IIf([TBIL_75th] Is Null,"Age Out Of
Parameters","No")) AS [At Risk?], Nomogram.TBIL_75th
FROM Nomogram RIGHT JOIN qry_AgeValidation ON Nomogram.AGE =
qry_AgeValidation.ValidatedAge
WHERE (((qry_AgeValidation.DOD) Between [Forms]![frm_Action]![Begin] And
[Forms]![frm_Action]![End]))
ORDER BY qry_AgeValidation.DateandTime DESC;

I cannot find where the error is coming from. Can anyone see where the
problem is? Sure appreciate the help. Thanks
 
What are the values in text boxes Begin and End on frm_Action? Are they
consistent with the data type of DOD?
 
The text boxes hold a beginning date and End date specifying the time period
for which the query would pull records. Initially when I got the error I
removed that date criteria but I got the error anyway.

This is the database I used your "Better Date Diff" function in.

Douglas J Steele said:
What are the values in text boxes Begin and End on frm_Action? Are they
consistent with the data type of DOD?



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
I built a database used once a quarter and it has worked in the past, but I
am now getting an error "Data Type Mismatch in Criteria Expression" when the
following query is run:

SELECT qry_AgeValidation.[Acct#], qry_AgeValidation.Name,
qry_AgeValidation.DateOfBirth, qry_AgeValidation.DOD,
qry_AgeValidation.DateandTime, qry_AgeValidation.Result,
qry_AgeValidation.[Age At Test], qry_AgeValidation.Physician,
IIf([Result]>=[TBIL_75th],"Yes",IIf([TBIL_75th] Is Null,"Age Out Of
Parameters","No")) AS [At Risk?], Nomogram.TBIL_75th
FROM Nomogram RIGHT JOIN qry_AgeValidation ON Nomogram.AGE =
qry_AgeValidation.ValidatedAge
WHERE (((qry_AgeValidation.DOD) Between [Forms]![frm_Action]![Begin] And
[Forms]![frm_Action]![End]))
ORDER BY qry_AgeValidation.DateandTime DESC;

I cannot find where the error is coming from. Can anyone see where the
problem is? Sure appreciate the help. Thanks
Click to expand...
Click to expand...
 
Your error message talks about Criteria, but you say you got the same error
even when you removed the criteria.

The only other Criteria are in the IIf statement. [TBIL_75th] Is Null should
probably be IsNull([TBIL_75th]), but I can't see that being the cause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
The text boxes hold a beginning date and End date specifying the time period
for which the query would pull records. Initially when I got the error I
removed that date criteria but I got the error anyway.

This is the database I used your "Better Date Diff" function in.

Douglas J Steele said:
What are the values in text boxes Begin and End on frm_Action? Are they
consistent with the data type of DOD?



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
I built a database used once a quarter and it has worked in the past,
Click to expand...
but
I
am now getting an error "Data Type Mismatch in Criteria Expression"
Click to expand...
when
the
following query is run:

SELECT qry_AgeValidation.[Acct#], qry_AgeValidation.Name,
qry_AgeValidation.DateOfBirth, qry_AgeValidation.DOD,
qry_AgeValidation.DateandTime, qry_AgeValidation.Result,
qry_AgeValidation.[Age At Test], qry_AgeValidation.Physician,
IIf([Result]>=[TBIL_75th],"Yes",IIf([TBIL_75th] Is Null,"Age Out Of
Parameters","No")) AS [At Risk?], Nomogram.TBIL_75th
FROM Nomogram RIGHT JOIN qry_AgeValidation ON Nomogram.AGE =
qry_AgeValidation.ValidatedAge
WHERE (((qry_AgeValidation.DOD) Between [Forms]![frm_Action]![Begin] And
[Forms]![frm_Action]![End]))
ORDER BY qry_AgeValidation.DateandTime DESC;

I cannot find where the error is coming from. Can anyone see where the
problem is? Sure appreciate the help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
I am beginning to wonder if my "JOIN" in the query is considered a criteria
and that my data types are not matching there.

The query Age Validation uses the following expression as a field: Age At
Test: Diff2Dates("h",[DateOfBirth],[DateandTime]), this returns the age in
hours as ex: "63 Hours". I then have another field: ValidatedAge: Val([AGE
At Test]) which returns 63 for the example.

The query in question has table nomogram field age data type number as a
long integer formated general number.with a right sided join to Validated age
which is formated as a general number in the query age validation.

I have tried building the query again and when it runs, it works displaying
all the derived data correctly but only for about 10 seconds, then it
displays a data type mismatch and blows up. Never seen that before!


Douglas J Steele said:
Your error message talks about Criteria, but you say you got the same error
even when you removed the criteria.

The only other Criteria are in the IIf statement. [TBIL_75th] Is Null should
probably be IsNull([TBIL_75th]), but I can't see that being the cause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
The text boxes hold a beginning date and End date specifying the time period
for which the query would pull records. Initially when I got the error I
removed that date criteria but I got the error anyway.

This is the database I used your "Better Date Diff" function in.

Douglas J Steele said:
What are the values in text boxes Begin and End on frm_Action? Are they
consistent with the data type of DOD?



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I built a database used once a quarter and it has worked in the past, but
I
am now getting an error "Data Type Mismatch in Criteria Expression" when
the
following query is run:

SELECT qry_AgeValidation.[Acct#], qry_AgeValidation.Name,
qry_AgeValidation.DateOfBirth, qry_AgeValidation.DOD,
qry_AgeValidation.DateandTime, qry_AgeValidation.Result,
qry_AgeValidation.[Age At Test], qry_AgeValidation.Physician,
IIf([Result]>=[TBIL_75th],"Yes",IIf([TBIL_75th] Is Null,"Age Out Of
Parameters","No")) AS [At Risk?], Nomogram.TBIL_75th
FROM Nomogram RIGHT JOIN qry_AgeValidation ON Nomogram.AGE =
qry_AgeValidation.ValidatedAge
WHERE (((qry_AgeValidation.DOD) Between [Forms]![frm_Action]![Begin] And
[Forms]![frm_Action]![End]))
ORDER BY qry_AgeValidation.DateandTime DESC;

I cannot find where the error is coming from. Can anyone see where the
problem is? Sure appreciate the help. Thanks
Click to expand...
Click to expand...
Click to expand...
 
I discovered the error, there was one record out of 3600 new records that had
a null value, I should know by now that if the database suddenly doesn't
work, the data I put in it is probably messed up. Thank you for your time
and help.

Douglas J Steele said:
Your error message talks about Criteria, but you say you got the same error
even when you removed the criteria.

The only other Criteria are in the IIf statement. [TBIL_75th] Is Null should
probably be IsNull([TBIL_75th]), but I can't see that being the cause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
The text boxes hold a beginning date and End date specifying the time period
for which the query would pull records. Initially when I got the error I
removed that date criteria but I got the error anyway.

This is the database I used your "Better Date Diff" function in.

Douglas J Steele said:
What are the values in text boxes Begin and End on frm_Action? Are they
consistent with the data type of DOD?



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I built a database used once a quarter and it has worked in the past, but
I
am now getting an error "Data Type Mismatch in Criteria Expression" when
the
following query is run:

SELECT qry_AgeValidation.[Acct#], qry_AgeValidation.Name,
qry_AgeValidation.DateOfBirth, qry_AgeValidation.DOD,
qry_AgeValidation.DateandTime, qry_AgeValidation.Result,
qry_AgeValidation.[Age At Test], qry_AgeValidation.Physician,
IIf([Result]>=[TBIL_75th],"Yes",IIf([TBIL_75th] Is Null,"Age Out Of
Parameters","No")) AS [At Risk?], Nomogram.TBIL_75th
FROM Nomogram RIGHT JOIN qry_AgeValidation ON Nomogram.AGE =
qry_AgeValidation.ValidatedAge
WHERE (((qry_AgeValidation.DOD) Between [Forms]![frm_Action]![Begin] And
[Forms]![frm_Action]![End]))
ORDER BY qry_AgeValidation.DateandTime DESC;

I cannot find where the error is coming from. Can anyone see where the
problem is? Sure appreciate the help. Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with dCount - “Data Type mismatch in criteria expression†2

Back
Top