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 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.
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
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.
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.
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.
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.
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.
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
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?
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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"...
|
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...
|
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....
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
| |