Query problem

G

Gamefewel

Could you please tell me what is wrong with this SQL statement. I am trying
to get the patient info from one table and the results from another table. I
want the Diastolic to be over 90 AND the systolic to be over 140. I am
getting a data mismatch error. THe diastolic and systolic are text.

Thanks in advance for your help.

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic FROM Patients INNER JOIN [Lab
Results] ON Patients.MRN = [Lab Results].MRN WHERE [Systolic]>140 And
[Diastolic]>90 ORDER BY Patients.[Last Name], Patients.[First Name];
 
G

Gina Whipp

Gamefewel,

You will get the Data Mismatch error when trying to perform numeric
functions on text if you don't convert that text to numeric. I added Val()
to convert your text to numeric thereby allowing it to analyze your request.

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic FROM Patients INNER JOIN [Lab
Results] ON Patients.MRN = [Lab Results].MRN WHERE Val([Systolic])>140 And
Val([Diastolic])>90 ORDER BY Patients.[Last Name], Patients.[First Name];

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John Spencer

It would be better if diastolic and systolic were number fields. You can
force a number value using the Val function.

WHERE Val(Systolic) > 140 and Val(Diastolic) > 90

If the field could be null the Val function will error. So you might need

WHERE IIF(IsNumeric(Systolic),Val(Systolic),Null) > 140 AND
IIF(IsNumeric(Diastolic),Val(Diastolic),Null) > 90

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
G

Gamefewel

Thank you both. I tried both and both worked. I have more queries to write
so I did change the text fields to number fields.
thanks again

John Spencer said:
It would be better if diastolic and systolic were number fields. You can
force a number value using the Val function.

WHERE Val(Systolic) > 140 and Val(Diastolic) > 90

If the field could be null the Val function will error. So you might need

WHERE IIF(IsNumeric(Systolic),Val(Systolic),Null) > 140 AND
IIF(IsNumeric(Diastolic),Val(Diastolic),Null) > 90

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Could you please tell me what is wrong with this SQL statement. I am trying
to get the patient info from one table and the results from another table. I
want the Diastolic to be over 90 AND the systolic to be over 140. I am
getting a data mismatch error. THe diastolic and systolic are text.

Thanks in advance for your help.

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic FROM Patients INNER JOIN [Lab
Results] ON Patients.MRN = [Lab Results].MRN WHERE [Systolic]>140 And
[Diastolic]>90 ORDER BY Patients.[Last Name], Patients.[First Name];
Click to expand...
.
Click to expand...
 
G

Gina Whipp

Gamefewel,

We are always glad to help. You did note that turning those fields to
numeric eliminates the need for the Val() function? If not, you know now.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gamefewel said:
Thank you both. I tried both and both worked. I have more queries to
write
so I did change the text fields to number fields.
thanks again

John Spencer said:
It would be better if diastolic and systolic were number fields. You can
force a number value using the Val function.

WHERE Val(Systolic) > 140 and Val(Diastolic) > 90

If the field could be null the Val function will error. So you might
need

WHERE IIF(IsNumeric(Systolic),Val(Systolic),Null) > 140 AND
IIF(IsNumeric(Diastolic),Val(Diastolic),Null) > 90

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Could you please tell me what is wrong with this SQL statement. I am
trying
to get the patient info from one table and the results from another
table. I
want the Diastolic to be over 90 AND the systolic to be over 140. I am
getting a data mismatch error. THe diastolic and systolic are text.

Thanks in advance for your help.

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone,
[Lab
Results].Systolic, [Lab Results].Diastolic FROM Patients INNER JOIN
[Lab
Results] ON Patients.MRN = [Lab Results].MRN WHERE [Systolic]>140 And
[Diastolic]>90 ORDER BY Patients.[Last Name], Patients.[First Name];
Click to expand...
.
Click to expand...
Click to expand...
 
G

Gamefewel

This query worked but met with the Dr and he wants it to be OR, not AND. I
change the AND to OR in the SQL statement and it moved the second field down
a row, but it pulling in all info, not those within the parameters I set.
Any suggestions? Thanks
 
J

John Spencer

SELECT Patients.[Last Name]
, Patients.[First Name]
, Patients.Phone
, [Lab Results].Systolic
, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab > Results]
ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>140 or
[Diastolic]>90
ORDER BY Patients.[Last Name], Patients.[First Name];

That should get any patient with a systolic pressure over 140 and any patient
with a diastolic over 90. As long as the systolic and diastolic fields are
number fields this should work correctly. However you state they are text.
If you cannot change the field type to a number field, then you can use the
Val function to force the text to be converted into a number.

SELECT Patients.[Last Name]
, Patients.[First Name]
, Patients.Phone
, [Lab Results].Systolic
, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab > Results]
ON Patients.MRN = [Lab Results].MRN
WHERE Val([Systolic])>140 or
Val([Diastolic])>90
ORDER BY Patients.[Last Name], Patients.[First Name];

That could still error if Systolic or Diastolic is a null or a non-number
value such as "N/A".

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This query worked but met with the Dr and he wants it to be OR, not AND. I
change the AND to OR in the SQL statement and it moved the second field down
a row, but it pulling in all info, not those within the parameters I set.
Any suggestions? Thanks

Gamefewel said:
Could you please tell me what is wrong with this SQL statement. I am trying
to get the patient info from one table and the results from another table. I
want the Diastolic to be over 90 AND the systolic to be over 140. I am
getting a data mismatch error. THe diastolic and systolic are text.

Thanks in advance for your help.

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic FROM Patients INNER JOIN [Lab
Results] ON Patients.MRN = [Lab Results].MRN WHERE [Systolic]>140 And
[Diastolic]>90 ORDER BY Patients.[Last Name], Patients.[First Name];
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

Simply query question 10
Please help. Question about querying a subform 4
Date query from datasheet in subform 1
2 complicated queries 5
Query to Pull Most Recent Call with a Callback Date Scheduled 10
Finding Duplicates 9
unique records help please 6
Select Query (Probably easy but I can't figure it out) 1

Top