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

Cross tab query data type mispatch criteria expression

P: n/a
I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am filtering
some data out in the criteria line.

I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on this
is much appreciated.

Thanks

Feb 11 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
For calculated fields in a query (particularly those involving Nz()), Access
is likely to get confused about the data type. The solution is to explicitly
typecast the expressions.

Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aa*****@gmail.comwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...
>I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am filtering
some data out in the criteria line.

I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on this
is much appreciated.
Feb 12 '07 #2

P: n/a
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
For calculated fields in a query (particularly those involving Nz()), Access
is likely to get confused about the data type. The solution is to explicitly
typecast the expressions.

Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aaro...@gmail.comwrote in message

news:11**********************@k78g2000cwa.googlegr oups.com...
I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on thisI
is much appreciated.
Thanks for the help. I took a look at your page and made some changes
to my query but I am still getting the same error. Below is the calc
string.

LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))

I tried simplifying by running a simple select query off of this field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing the
field as a number.

Thanks

Feb 12 '07 #3

P: n/a
Did you try specifying a value to use for null?

It's the 2nd argument of Nz():
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aa*****@gmail.comwrote in message
news:11**********************@a34g2000cwb.googlegr oups.com...
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>For calculated fields in a query (particularly those involving Nz()),
Access
is likely to get confused about the data type. The solution is to
explicitly
typecast the expressions.

Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

"aaronrm" <aaro...@gmail.comwrote in message

news:11**********************@k78g2000cwa.googleg roups.com...
>I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on thisI
is much appreciated.

Thanks for the help. I took a look at your page and made some changes
to my query but I am still getting the same error. Below is the calc
string.

LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))

I tried simplifying by running a simple select query off of this field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing the
field as a number.
Feb 12 '07 #4

P: n/a
On Feb 11, 10:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Did you try specifying a value to use for null?

It's the 2nd argument of Nz():
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aaro...@gmail.comwrote in message

news:11**********************@a34g2000cwb.googlegr oups.com...
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
For calculated fields in a query (particularly those involving Nz()),
Access
is likely to get confused about the data type. The solution is to
explicitly
typecast the expressions.
Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
"aaronrm" <aaro...@gmail.comwrote in message
>news:11**********************@k78g2000cwa.googleg roups.com...
I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on thisI
is much appreciated.
Thanks for the help. I took a look at your page and made some changes
to my query but I am still getting the same error. Below is the calc
string.
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))
I tried simplifying by running a simple select query off of this field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing the
field as a number.

I cut and pasted exactly as you stated and I still get the same
error. I also tried deleteing the criteria expression and that was no
help either. Any other thoughts?

Thanks for all the help.

Feb 12 '07 #5

P: n/a
Okay, tell us more about emdbuse_LoanSummary.LoanAmount
Is emdbuser_LoanSummary a table or query?

If emdbuser_LoanSummary is a table, what is the data type of LoanAmount?
Currency? Text? Number?

If query, is LoanAmount a calculated field? If so, you may need to typecast
there. If it's a query but not a calcualted field, go deeper and see what is
the Data Type of the field in the source table.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aa*****@gmail.comwrote in message
news:11*********************@h3g2000cwc.googlegrou ps.com...
On Feb 11, 10:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>Did you try specifying a value to use for null?

It's the 2nd argument of Nz():
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount], 0))

"aaronrm" <aaro...@gmail.comwrote in message

news:11**********************@a34g2000cwb.googleg roups.com...
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
For calculated fields in a query (particularly those involving Nz()),
Access
is likely to get confused about the data type. The solution is to
explicitly
typecast the expressions.
>Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
>"aaronrm" <aaro...@gmail.comwrote in message
>>news:11**********************@k78g2000cwa.google groups.com...
>I have a real simple cross-tab query that I am trying to sum on as
the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am
filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on
thisI
is much appreciated.
Thanks for the help. I took a look at your page and made some changes
to my query but I am still getting the same error. Below is the calc
string.
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))
I tried simplifying by running a simple select query off of this field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing the
field as a number.


I cut and pasted exactly as you stated and I still get the same
error. I also tried deleteing the criteria expression and that was no
help either. Any other thoughts?

Thanks for all the help.
Feb 12 '07 #6

P: n/a
On Feb 12, 4:57 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Okay, tell us more about emdbuse_LoanSummary.LoanAmount
Is emdbuser_LoanSummary a table or query?

If emdbuser_LoanSummary is a table, what is the data type of LoanAmount?
Currency? Text? Number?

If query, is LoanAmount a calculated field? If so, you may need to typecast
there. If it's a query but not a calcualted field, go deeper and see what is
the Data Type of the field in the source table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aaro...@gmail.comwrote in message

news:11*********************@h3g2000cwc.googlegrou ps.com...
On Feb 11, 10:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Did you try specifying a value to use for null?
It's the 2nd argument of Nz():
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount], 0))
"aaronrm" <aaro...@gmail.comwrote in message
>news:11**********************@a34g2000cwb.googleg roups.com...
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
For calculated fields in a query (particularly those involving Nz()),
Access
is likely to get confused about the data type. The solution is to
explicitly
typecast the expressions.
Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
"aaronrm" <aaro...@gmail.comwrote in message
>news:11**********************@k78g2000cwa.googleg roups.com...
I have a real simple cross-tab query that I am trying to sum on as
the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am
filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help on
thisI
is much appreciated.
Thanks for the help. I took a look at your page and made some changes
to my query but I am still getting the same error. Below is the calc
string.
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))
I tried simplifying by running a simple select query off of this field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing the
field as a number.
I cut and pasted exactly as you stated and I still get the same
error. I also tried deleteing the criteria expression and that was no
help either. Any other thoughts?
Thanks for all the help.

emdbuser_loansummary is the source table. I am using an ODBC
connection to get tot the sql database. The source field in this
table for the loan amount is "text". I have written a select query
called Q_loansummary. This is where I am trying to re-format the
data. Thanks for help.

Feb 13 '07 #7

P: n/a
Okay, if you are working with a Text field, it will need typecasting at the
lowest level query.

Use Val() to get the value of the text string as a number. Val() can't hande
nulls, so you need to use Nz() inside that. And once it has been converted
to a numeric value, you probably want it converted to a Currency type. Try
something like:
CCur(Val(Nz([emdbuser_LoanSummary]![LoanAmount], "0"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aa*****@gmail.comwrote in message
news:11*********************@a34g2000cwb.googlegro ups.com...
On Feb 12, 4:57 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>Okay, tell us more about emdbuse_LoanSummary.LoanAmount
Is emdbuser_LoanSummary a table or query?

If emdbuser_LoanSummary is a table, what is the data type of LoanAmount?
Currency? Text? Number?

If query, is LoanAmount a calculated field? If so, you may need to
typecast
there. If it's a query but not a calcualted field, go deeper and see what
is
the Data Type of the field in the source table.

"aaronrm" <aaro...@gmail.comwrote in message

news:11*********************@h3g2000cwc.googlegro ups.com...
On Feb 11, 10:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Did you try specifying a value to use for null?
>It's the 2nd argument of Nz():
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount], 0))
>"aaronrm" <aaro...@gmail.comwrote in message
>>news:11**********************@a34g2000cwb.google groups.com...
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
For calculated fields in a query (particularly those involving
Nz()),
Access
is likely to get confused about the data type. The solution is to
explicitly
typecast the expressions.
>Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
>"aaronrm" <aaro...@gmail.comwrote in message
>>news:11**********************@k78g2000cwa.google groups.com...
>I have a real simple cross-tab query that I am trying to sum on as
the
action but I am getting the "data type mismatch criteria
expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am
filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in
the
past I had a solution for this but I can't remember. Any help on
thisI
is much appreciated.
Thanks for the help. I took a look at your page and made some
changes
to my query but I am still getting the same error. Below is the
calc
string.
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))
I tried simplifying by running a simple select query off of this
field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing
the
field as a number.
I cut and pasted exactly as you stated and I still get the same
error. I also tried deleteing the criteria expression and that was no
help either. Any other thoughts?
Thanks for all the help.


emdbuser_loansummary is the source table. I am using an ODBC
connection to get tot the sql database. The source field in this
table for the loan amount is "text". I have written a select query
called Q_loansummary. This is where I am trying to re-format the
data. Thanks for help.
Feb 13 '07 #8

P: n/a
On Feb 12, 4:39 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Okay, if you are working with a Text field, it will need typecasting at the
lowest level query.

Use Val() to get the value of the text string as a number. Val() can't hande
nulls, so you need to use Nz() inside that. And once it has been converted
to a numeric value, you probably want it converted to a Currency type. Try
something like:
CCur(Val(Nz([emdbuser_LoanSummary]![LoanAmount], "0"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aaro...@gmail.comwrote in message

news:11*********************@a34g2000cwb.googlegro ups.com...
On Feb 12, 4:57 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Okay, tell us more about emdbuse_LoanSummary.LoanAmount
Is emdbuser_LoanSummary a table or query?
If emdbuser_LoanSummary is a table, what is the data type of LoanAmount?
Currency? Text? Number?
If query, is LoanAmount a calculated field? If so, you may need to
typecast
there. If it's a query but not a calcualted field, go deeper and see what
is
the Data Type of the field in the source table.
"aaronrm" <aaro...@gmail.comwrote in message
>news:11*********************@h3g2000cwc.googlegro ups.com...
On Feb 11, 10:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Did you try specifying a value to use for null?
It's the 2nd argument of Nz():
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount], 0))
"aaronrm" <aaro...@gmail.comwrote in message
>news:11**********************@a34g2000cwb.googleg roups.com...
On Feb 11, 4:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
For calculated fields in a query (particularly those involving
Nz()),
Access
is likely to get confused about the data type. The solution is to
explicitly
typecast the expressions.
Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
"aaronrm" <aaro...@gmail.comwrote in message
>news:11**********************@k78g2000cwa.googleg roups.com...
I have a real simple cross-tab query that I am trying to sum on as
the
action but I am getting the "data type mismatch criteria
expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am
filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in
the
past I had a solution for this but I can't remember. Any help on
thisI
is much appreciated.
Thanks for the help. I took a look at your page and made some
changes
to my query but I am still getting the same error. Below is the
calc
string.
LoanAmount2: CCur(nz([emdbuser_LoanSummary]![LoanAmount]))
I tried simplifying by running a simple select query off of this
field
in a new query and I can getting the same error. It appears as is
stated on your website that Access is having problems recognizing
the
field as a number.
I cut and pasted exactly as you stated and I still get the same
error. I also tried deleteing the criteria expression and that was no
help either. Any other thoughts?
Thanks for all the help.
emdbuser_loansummary is the source table. I am using an ODBC
connection to get tot the sql database. The source field in this
table for the loan amount is "text". I have written a select query
called Q_loansummary. This is where I am trying to re-format the
data. Thanks for help.

That did it thanks for the help. One thing it is doing, is reading
900,000 to 900. I can multiple it back out to get the right number,
but is there a cleaner way to do this?

Thanks

Feb 13 '07 #9

P: n/a
You can dump the commas:
Val(Replace(Nz([MyField], "0"), ",", ""))

If that doesn't work, and you are using Access 2000, see:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aa*****@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...
>
That did it thanks for the help. One thing it is doing, is reading
900,000 to 900. I can multiple it back out to get the right number,
but is there a cleaner way to do this?
Feb 13 '07 #10

P: n/a
On Feb 12, 9:47 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
You can dump the commas:
Val(Replace(Nz([MyField], "0"), ",", ""))

If that doesn't work, and you are using Access 2000, see:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aaronrm" <aaro...@gmail.comwrote in message

news:11**********************@m58g2000cwm.googlegr oups.com...
That did it thanks for the help. One thing it is doing, is reading
900,000 to 900. I can multiple it back out to get the right number,
but is there a cleaner way to do this?
That did it. Thanks for all the help on this.

Feb 13 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.