473,287 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Cross tab query data type mispatch criteria expression

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
10 6172
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
5
by: SuffrinMick | last post by:
Hello - I'm a newbie to coding! I'm working on an access 2000 database which has three tables: tblContacts - A list of customer contacts. tblOrgTypes - A list of organisational types....
3
by: martlaco1 | last post by:
Trying to fix a query that (I thought) had worked once upon a time, and I keep getting a Data Type Mismatch error whenever I enter any criteria for an expression using a Mid function. Without the...
2
by: igor.barbaric | last post by:
Hello! I have created a very simple query like this: SELECT Tasks.Name, DurationHrs(,) AS Duration FROM Tasks INNER JOIN Log ON Tasks.TaskID=Log.TaskID; The above query works fine....
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
3
by: Sheldon | last post by:
I have the following query expression - Like Format((!!)) & "/*/" & (! !) which would translate to e.g. 04/*/2007 if someone is running a report for last month. The above expression is part of a...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
17
by: sharsy | last post by:
Hello guys, I would like some help in generating query criteria that will identify credit cards that have expired on an access database. The specific Field is formatted with a Data Type of...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.