Text Numeric Deja Vu

S

shep

Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1 has Race
options that frmPatientV1presents in a combobox, and selection is stored in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field, and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race = tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
 
D

Duane Hookom

Your error message suggests a data type issue. Are all of the joined fields
of the same data type? Are you entering proper dates into the form fields?
Whate happens if you remove the criteria for one or all columns?
 
S

shep

Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either. That
seems to be the issue; in previous that enabled getting the status text vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

Duane Hookom said:
Your error message suggests a data type issue. Are all of the joined fields
of the same data type? Are you entering proper dates into the form fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


shep said:
Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1 has
Race
options that frmPatientV1presents in a combobox, and selection is stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field, and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
 
D

Duane Hookom

How are the Race and ID different? Is it just Long vs Autonumber or more
significant? (why didn't you provide this information in your reply rather
than having to be asked?)

I didn't expect you to remove the criteria permanently. Sometimes we just
change things temporarily. This is called trouble-shooting/debugging.

--
Duane Hookom
MS Access MVP


shep said:
Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
That
seems to be the issue; in previous that enabled getting the status text
vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

Duane Hookom said:
Your error message suggests a data type issue. Are all of the joined
fields
of the same data type? Are you entering proper dates into the form
fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


shep said:
Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1 has
Race
options that frmPatientV1presents in a combobox, and selection is
stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field,
and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date
Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date
Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
S

shep

Race in tblPatientV1 is a text field; ID in tblRaceV1 is primary key(auto
number) inserted by ACCESS. As for the parenthetical question, I'll just
say that I posted for help so I have no motive to with hold information; ergo
it must not have occured to me. I do respect your intelligence, but I do not
respect your disparagement of mine.

Removing the criteria did not affect the error message.

Duane Hookom said:
How are the Race and ID different? Is it just Long vs Autonumber or more
significant? (why didn't you provide this information in your reply rather
than having to be asked?)

I didn't expect you to remove the criteria permanently. Sometimes we just
change things temporarily. This is called trouble-shooting/debugging.

--
Duane Hookom
MS Access MVP


shep said:
Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
That
seems to be the issue; in previous that enabled getting the status text
vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

Duane Hookom said:
Your error message suggests a data type issue. Are all of the joined
fields
of the same data type? Are you entering proper dates into the form
fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1 has
Race
options that frmPatientV1presents in a combobox, and selection is
stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field,
and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date
Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date
Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I'm just hoping to get the information required so this thread doesn't have
to get too long.

You can't join a text and numeric field. This is causing the error. Have you
looked at the actual values to see which value from tblRaceV1 you are
actuallly storing in tblPatientV1? I would expect to see a Race field in
tblPatientV1 of numeric long data type. Are the values in the field numeric?

--
Duane Hookom
MS Access MVP


shep said:
Race in tblPatientV1 is a text field; ID in tblRaceV1 is primary key(auto
number) inserted by ACCESS. As for the parenthetical question, I'll just
say that I posted for help so I have no motive to with hold information;
ergo
it must not have occured to me. I do respect your intelligence, but I do
not
respect your disparagement of mine.

Removing the criteria did not affect the error message.

Duane Hookom said:
How are the Race and ID different? Is it just Long vs Autonumber or more
significant? (why didn't you provide this information in your reply
rather
than having to be asked?)

I didn't expect you to remove the criteria permanently. Sometimes we just
change things temporarily. This is called trouble-shooting/debugging.

--
Duane Hookom
MS Access MVP


shep said:
Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
That
seems to be the issue; in previous that enabled getting the status text
vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

:

Your error message suggests a data type issue. Are all of the joined
fields
of the same data type? Are you entering proper dates into the form
fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1
has
Race
options that frmPatientV1presents in a combobox, and selection is
stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field,
and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date
Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date
Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
T

tina

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
Click to expand...

PMFJI. the above statement is technically correct, shep, but could be
misleading. my solution for you in the earlier thread was predicated on the
fact the the data type of tblPatientV1.Status is Long Integer, and the data
type of tblStatusV1.ID is AutoNumber. those are the only two data types that
you can "mix" in a primary key / foreign key link. otherwise linked table
fields' data types must match, such as Text to Text.

take another look at the data types of the tables' linking fields that
you're using in your current query. fields tblPatientV1.ChartNumber and
tblAppointmentsV1.ChartNumber must have either the exact same data type OR
Autonumber / Long Integer data types. same goes for tblPatientV1.Race and
tblRaceV1.ID.
(btw, i don't remember if this point was raised in the earlier thread, but
if you have any Lookup fields in any of your tables, suggest you get rid of
the lookups. they add no value, because data is entered in forms, not
tables; and they can cause numerous problems, beyond the immediate issue of
hiding the actual values that are being stored in table fields.)

also check the data type of tblAppointmentsV1.InitialVisit. if it is Text
data type, then the criteria should be enclosed in double quotes, as

HAVING tblAppointmentsV1.InitialVisit="Yes"

if the data type is Yes/No, then try changing the Yes to True, as

HAVING tblAppointmentsV1.InitialVisit=True

hth


shep said:
Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either. That
seems to be the issue; in previous that enabled getting the status text vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

Duane Hookom said:
Your error message suggests a data type issue. Are all of the joined fields
of the same data type? Are you entering proper dates into the form fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


shep said:
Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1 has
Race
options that frmPatientV1presents in a combobox, and selection is stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field, and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
S

shep

That was indeed the problem and I now have it working. Thanks for your
patience and help!

Duane Hookom said:
I'm just hoping to get the information required so this thread doesn't have
to get too long.

You can't join a text and numeric field. This is causing the error. Have you
looked at the actual values to see which value from tblRaceV1 you are
actuallly storing in tblPatientV1? I would expect to see a Race field in
tblPatientV1 of numeric long data type. Are the values in the field numeric?

--
Duane Hookom
MS Access MVP


shep said:
Race in tblPatientV1 is a text field; ID in tblRaceV1 is primary key(auto
number) inserted by ACCESS. As for the parenthetical question, I'll just
say that I posted for help so I have no motive to with hold information;
ergo
it must not have occured to me. I do respect your intelligence, but I do
not
respect your disparagement of mine.

Removing the criteria did not affect the error message.

Duane Hookom said:
How are the Race and ID different? Is it just Long vs Autonumber or more
significant? (why didn't you provide this information in your reply
rather
than having to be asked?)

I didn't expect you to remove the criteria permanently. Sometimes we just
change things temporarily. This is called trouble-shooting/debugging.

--
Duane Hookom
MS Access MVP


Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
That
seems to be the issue; in previous that enabled getting the status text
vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

:

Your error message suggests a data type issue. Are all of the joined
fields
of the same data type? Are you entering proper dates into the form
fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1
has
Race
options that frmPatientV1presents in a combobox, and selection is
stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field,
and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date
Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date
Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
S

shep

Thanks again Tina!

I changed tblPatientV1.Race from text to number long integer, and the yes/no
to true/false and it works properly.

I think I understand the linking issue, so this old dog (69) is learning new
tricks.

Again, many thanks!

tina said:
tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
Click to expand...

PMFJI. the above statement is technically correct, shep, but could be
misleading. my solution for you in the earlier thread was predicated on the
fact the the data type of tblPatientV1.Status is Long Integer, and the data
type of tblStatusV1.ID is AutoNumber. those are the only two data types that
you can "mix" in a primary key / foreign key link. otherwise linked table
fields' data types must match, such as Text to Text.

take another look at the data types of the tables' linking fields that
you're using in your current query. fields tblPatientV1.ChartNumber and
tblAppointmentsV1.ChartNumber must have either the exact same data type OR
Autonumber / Long Integer data types. same goes for tblPatientV1.Race and
tblRaceV1.ID.
(btw, i don't remember if this point was raised in the earlier thread, but
if you have any Lookup fields in any of your tables, suggest you get rid of
the lookups. they add no value, because data is entered in forms, not
tables; and they can cause numerous problems, beyond the immediate issue of
hiding the actual values that are being stored in table fields.)

also check the data type of tblAppointmentsV1.InitialVisit. if it is Text
data type, then the criteria should be enclosed in double quotes, as

HAVING tblAppointmentsV1.InitialVisit="Yes"

if the data type is Yes/No, then try changing the Yes to True, as

HAVING tblAppointmentsV1.InitialVisit=True

hth


shep said:
Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either. That
seems to be the issue; in previous that enabled getting the status text vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

Duane Hookom said:
Your error message suggests a data type issue. Are all of the joined fields
of the same data type? Are you entering proper dates into the form fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1 has
Race
options that frmPatientV1presents in a combobox, and selection is stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field, and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
T

tina

no problem, shep. relationships are tricky, until you get a handle on them.
if you'd like to read up on relationships (database, not personal <g> ),
see the following website for some links:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Database Design 101 section.

hth


shep said:
Thanks again Tina!

I changed tblPatientV1.Race from text to number long integer, and the yes/no
to true/false and it works properly.

I think I understand the linking issue, so this old dog (69) is learning new
tricks.

Again, many thanks!

tina said:
tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
Click to expand...

PMFJI. the above statement is technically correct, shep, but could be
misleading. my solution for you in the earlier thread was predicated on the
fact the the data type of tblPatientV1.Status is Long Integer, and the data
type of tblStatusV1.ID is AutoNumber. those are the only two data types that
you can "mix" in a primary key / foreign key link. otherwise linked table
fields' data types must match, such as Text to Text.

take another look at the data types of the tables' linking fields that
you're using in your current query. fields tblPatientV1.ChartNumber and
tblAppointmentsV1.ChartNumber must have either the exact same data type OR
Autonumber / Long Integer data types. same goes for tblPatientV1.Race and
tblRaceV1.ID.
(btw, i don't remember if this point was raised in the earlier thread, but
if you have any Lookup fields in any of your tables, suggest you get rid of
the lookups. they add no value, because data is entered in forms, not
tables; and they can cause numerous problems, beyond the immediate issue of
hiding the actual values that are being stored in table fields.)

also check the data type of tblAppointmentsV1.InitialVisit. if it is Text
data type, then the criteria should be enclosed in double quotes, as

HAVING tblAppointmentsV1.InitialVisit="Yes"

if the data type is Yes/No, then try changing the Yes to True, as

HAVING tblAppointmentsV1.InitialVisit=True

hth


shep said:
Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either. That
seems to be the issue; in previous that enabled getting the status
Click to expand...
text
vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

:

Your error message suggests a data type issue. Are all of the joined fields
of the same data type? Are you entering proper dates into the form fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race.
Click to expand...
tblRaceV1
has
Race
options that frmPatientV1presents in a combobox, and selection is stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no
Click to expand...
field,
and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
Click to expand...
Click to expand...
Click to expand...
 
S

shep

Thanks! I will indeed use that resource

tina said:
no problem, shep. relationships are tricky, until you get a handle on them.
if you'd like to read up on relationships (database, not personal <g> ),
see the following website for some links:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Database Design 101 section.

hth


shep said:
Thanks again Tina!

I changed tblPatientV1.Race from text to number long integer, and the yes/no
to true/false and it works properly.

I think I understand the linking issue, so this old dog (69) is learning new
tricks.

Again, many thanks!

tina said:
tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.

PMFJI. the above statement is technically correct, shep, but could be
misleading. my solution for you in the earlier thread was predicated on the
fact the the data type of tblPatientV1.Status is Long Integer, and the data
type of tblStatusV1.ID is AutoNumber. those are the only two data types that
you can "mix" in a primary key / foreign key link. otherwise linked table
fields' data types must match, such as Text to Text.

take another look at the data types of the tables' linking fields that
you're using in your current query. fields tblPatientV1.ChartNumber and
tblAppointmentsV1.ChartNumber must have either the exact same data type OR
Autonumber / Long Integer data types. same goes for tblPatientV1.Race and
tblRaceV1.ID.
(btw, i don't remember if this point was raised in the earlier thread, but
if you have any Lookup fields in any of your tables, suggest you get rid of
the lookups. they add no value, because data is entered in forms, not
tables; and they can cause numerous problems, beyond the immediate issue of
hiding the actual values that are being stored in table fields.)

also check the data type of tblAppointmentsV1.InitialVisit. if it is Text
data type, then the criteria should be enclosed in double quotes, as

HAVING tblAppointmentsV1.InitialVisit="Yes"

if the data type is Yes/No, then try changing the Yes to True, as

HAVING tblAppointmentsV1.InitialVisit=True

hth


Thanks for your response.

tblePatientV1.Race and tblRaceV1.ID are not the same data type. But in
previous case, tblPatientV1.Status and qryStatusV1.ID are not either.
That
seems to be the issue; in previous that enabled getting the status text
vice
ID number, which is what I am trying to do now.

Dates are entered properly and the procedure is same as used in several
queries that work as desired.

Removing either criteria does not seem to be an option since they are
requisite to generating required information.

:

Your error message suggests a data type issue. Are all of the joined
fields
of the same data type? Are you entering proper dates into the form
fields?
Whate happens if you remove the criteria for one or all columns?

--
Duane Hookom
MS Access MVP


Ref "Why is my text being stored as Numeric" 13-Sep-05

Tina provided final solution to my question/need with this code.
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber,
tblStatusV1.Status, [Date] AS ReportDate
FROM (tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID) INNER JOIN qryPatientNameV1 ON tblPatientV1.ID =
qryPatientNameV1.ID
GROUP BY tblStatusV1.Status, [Date]
HAVING (((tblStatusV1.Status)="Active"));

Now I need to count New Admissions for a period by Race. tblRaceV1
has
Race
options that frmPatientV1presents in a combobox, and selection is
stored
in
tblPatientV1. tblAppointmentsV1 stores New Admissions,yes/no field,
and
appointment date.

I created qryNewAdmRaceV1 to get count of New Admissions by Race for
designated period. Here is SQL:
SELECT Count(tblAppointmentsV1.ChartNumber) AS CountOfChartNumber,
tblRaceV1.Race
FROM (tblPatientV1 INNER JOIN tblRaceV1 ON tblPatientV1.Race =
tblRaceV1.ID)
INNER JOIN tblAppointmentsV1 ON tblPatientV1.ChartNumber =
tblAppointmentsV1.ChartNumber
WHERE (((tblAppointmentsV1.ApptDate)>=[forms]![Report Date
Range]![Start
Date] And (tblAppointmentsV1.ApptDate)<=[forms]![Report Date
Range]![End
Date]))
GROUP BY tblRaceV1.Race, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.InitialVisit)=Yes));

When I run query, I get error "Type Mismatch in Expression"
I would appreciate help solving this
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

Need to Count Active Records 4
Need to Count by two different criteria in same Totals query 4
Find Max Value of Text Field 7
Issues with Left Join 2
Slow Code/Query Execution 9
Unable to get the PivotFields property of the PivotTable Class 2

Top