473,287 Members | 1,582 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.

Error: Data type mismatch in criteria expression

Hi,
I have posted this problem before. Apprently, the suggestion took care of
the problem. However, still I am getting the above error message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most cases this
session variable value should be zero. However, instead, it is giving null
value and this null value is messing up the update statement which is
supposed to update the table in the database. This session variable value
being null is trying to update a currency field which gives the above error.
The sql statement via response.write is as follows:
UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$0.00' ,
tblGMISExpenditures_Quarter.MBCCShare= '' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$0.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$0.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$0.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '' ,
tblGMISExpenditures_Quarter.Remarks= ' ' , tblGMISExpenditures_Quarter.Name=
' ' , tblGMISExpenditures_Quarter.Title= ' ' ,
tblGMISExpenditures_Quarter.AreaCode= ' ' ,
tblGMISExpenditures_Quarter.Phone1= ' ' , tblGMISExpenditures_Quarter.Phone2=
' ' , tblGMISExpenditures_Quarter.Date= '1/3/2005' where
tblGMISExpenditures_Quarter.SubgrantIntID = 6032;
Any help or alternative to use a calculated field(not user input field) to
update a database is appreciated. Thanks.
Jul 22 '05 #1
7 2587
If IsNull(TheValue) Then TheValue = 0
'// code to update the DB

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...
Hi,
I have posted this problem before. Apprently, the suggestion took care of
the problem. However, still I am getting the above error message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most cases this session variable value should be zero. However, instead, it is giving null
value and this null value is messing up the update statement which is
supposed to update the table in the database. This session variable value
being null is trying to update a currency field which gives the above error. The sql statement via response.write is as follows:
UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$0.00' ,
tblGMISExpenditures_Quarter.MBCCShare= '' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$0.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$0.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$0.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '' ,
tblGMISExpenditures_Quarter.Remarks= ' ' , tblGMISExpenditures_Quarter.Name= ' ' , tblGMISExpenditures_Quarter.Title= ' ' ,
tblGMISExpenditures_Quarter.AreaCode= ' ' ,
tblGMISExpenditures_Quarter.Phone1= ' ' , tblGMISExpenditures_Quarter.Phone2= ' ' , tblGMISExpenditures_Quarter.Date= '1/3/2005' where
tblGMISExpenditures_Quarter.SubgrantIntID = 6032;
Any help or alternative to use a calculated field(not user input field) to
update a database is appreciated. Thanks.

Jul 22 '05 #2
Jack wrote:
Hi,
I have posted this problem before. Apprently, the suggestion took
care of the problem. However, still I am getting the above error
message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most
cases this session variable value should be zero. However, instead,
it is giving null value and this null value is messing up the update
statement which is supposed to update the table in the database. This
session variable value being null is trying to update a currency
field which gives the above error. The sql statement via
response.write is as follows: <snip> tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,
Assuming CurrentOutlay is one of your currency fields, you need to be aware
that currency is a numeric datatype. Are you really going to the trouble of
putting the quotes and "$" in these values? There is no need to do so.
Currency is a numeric datatype. This SET statement can be changed to:

CurrentOutlay=0

with the same effect as the statement you currently are using, only much
simpler. (Simpler still would be a saved parameter query, but I think I've
already mentioned that to you.)

Also, you have a single table in your query. There is no chance that the
query engine is going to get confused as to which table the fields
referenced in your query are coming from, so there is no need to fully
qualify each field name with that god-awfully long table name: it makes it
so much harder to read. Just use the field names. Only use the table name
qualifier if the query includes two or more tables, and better yet, use
table aliases when you do.


You need to check the session variable value and substitute the word NULL
for it if it is empty:

dim value
value = session("variablename")
if len(value) = 0 then value = "NULL"

sSQL = "...., CurrentOutlay= " & value & ", ..."

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #3
Thanks for your advise here, Steve. I have couple of questions here.
Why is the session variable value when set to 0 in the form gives null value
in the confirmation asp page? The other question is if in a form, there are
few fields that are input by user, while there is one calculated field based
on these input and the calculated field needs to be updated in a database,
what would be the right method to handle the calculated field? It seems there
should be a way other than session variable concept to handle the calculated
field. Does input type hidden is of any use here. Looking for alternatives.
Thanks. Regards,
"Steven Burn" wrote:
If IsNull(TheValue) Then TheValue = 0
'// code to update the DB

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...
Hi,
I have posted this problem before. Apprently, the suggestion took care of
the problem. However, still I am getting the above error message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most cases

this
session variable value should be zero. However, instead, it is giving null
value and this null value is messing up the update statement which is
supposed to update the table in the database. This session variable value
being null is trying to update a currency field which gives the above

error.
The sql statement via response.write is as follows:
UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$0.00' ,
tblGMISExpenditures_Quarter.MBCCShare= '' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$0.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$0.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$0.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '' ,
tblGMISExpenditures_Quarter.Remarks= ' ' ,

tblGMISExpenditures_Quarter.Name=
' ' , tblGMISExpenditures_Quarter.Title= ' ' ,
tblGMISExpenditures_Quarter.AreaCode= ' ' ,
tblGMISExpenditures_Quarter.Phone1= ' ' ,

tblGMISExpenditures_Quarter.Phone2=
' ' , tblGMISExpenditures_Quarter.Date= '1/3/2005' where
tblGMISExpenditures_Quarter.SubgrantIntID = 6032;
Any help or alternative to use a calculated field(not user input field) to
update a database is appreciated. Thanks.


Jul 22 '05 #4
A hidden field would certainly be an easier way of doing it (IMHO anyway).

As for why the value is not null in one page and is null after being passed,
I would guess it is most likely caused by whatever method your using to
"extract" the value.

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:CF**********************************@microsof t.com...
Thanks for your advise here, Steve. I have couple of questions here.
Why is the session variable value when set to 0 in the form gives null value in the confirmation asp page? The other question is if in a form, there are few fields that are input by user, while there is one calculated field based on these input and the calculated field needs to be updated in a database,
what would be the right method to handle the calculated field? It seems there should be a way other than session variable concept to handle the calculated field. Does input type hidden is of any use here. Looking for alternatives. Thanks. Regards,
"Steven Burn" wrote:
If IsNull(TheValue) Then TheValue = 0
'// code to update the DB

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...
Hi,
I have posted this problem before. Apprently, the suggestion took care of the problem. However, still I am getting the above error message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most cases
this
session variable value should be zero. However, instead, it is giving

null value and this null value is messing up the update statement which is
supposed to update the table in the database. This session variable value being null is trying to update a currency field which gives the above

error.
The sql statement via response.write is as follows:
UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$0.00' ,
tblGMISExpenditures_Quarter.MBCCShare= '' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$0.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$0.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$0.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '' ,
tblGMISExpenditures_Quarter.Remarks= ' ' ,

tblGMISExpenditures_Quarter.Name=
' ' , tblGMISExpenditures_Quarter.Title= ' ' ,
tblGMISExpenditures_Quarter.AreaCode= ' ' ,
tblGMISExpenditures_Quarter.Phone1= ' ' ,

tblGMISExpenditures_Quarter.Phone2=
' ' , tblGMISExpenditures_Quarter.Date= '1/3/2005' where
tblGMISExpenditures_Quarter.SubgrantIntID = 6032;
Any help or alternative to use a calculated field(not user input field) to update a database is appreciated. Thanks.


Jul 22 '05 #5
Hi Bob,
Sorry for not being able to reply back early. Got caught up with something.
Thanks for your generous help again. I think I am not sure why would you
convert the zero length to null as advised by you at the end of your
comments. Would it not be the other way round i.e. from null to 0. Thanks.

"Bob Barrows [MVP]" wrote:
Jack wrote:
Hi,
I have posted this problem before. Apprently, the suggestion took
care of the problem. However, still I am getting the above error
message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most
cases this session variable value should be zero. However, instead,
it is giving null value and this null value is messing up the update
statement which is supposed to update the table in the database. This
session variable value being null is trying to update a currency
field which gives the above error. The sql statement via
response.write is as follows:

<snip>
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,


Assuming CurrentOutlay is one of your currency fields, you need to be aware
that currency is a numeric datatype. Are you really going to the trouble of
putting the quotes and "$" in these values? There is no need to do so.
Currency is a numeric datatype. This SET statement can be changed to:

CurrentOutlay=0

with the same effect as the statement you currently are using, only much
simpler. (Simpler still would be a saved parameter query, but I think I've
already mentioned that to you.)

Also, you have a single table in your query. There is no chance that the
query engine is going to get confused as to which table the fields
referenced in your query are coming from, so there is no need to fully
qualify each field name with that god-awfully long table name: it makes it
so much harder to read. Just use the field names. Only use the table name
qualifier if the query includes two or more tables, and better yet, use
table aliases when you do.


You need to check the session variable value and substitute the word NULL
for it if it is empty:

dim value
value = session("variablename")
if len(value) = 0 then value = "NULL"

sSQL = "...., CurrentOutlay= " & value & ", ..."

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 22 '05 #6
Jack wrote:
Hi Bob,
Sorry for not being able to reply back early. Got caught up with
something. Thanks for your generous help again. I think I am not sure
why would you convert the zero length to null as advised by you at
the end of your comments.
zero length string <> Null
Would it not be the other way round i.e.
from null to 0. Thanks.
Depends on what you want. If your field is not required, you can enter a
Null in it. If it is required, then you probably want to enter a zero. What
you cannot do is this:

...., fieldname=, ...

That will cause an error. Either this:

...., fieldname = Null, ...

or this:

...., fieldname = 0, ...

will work
"Bob Barrows [MVP]" wrote:
Jack wrote:
Hi,
I have posted this problem before. Apprently, the suggestion took
care of the problem. However, still I am getting the above error
message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most
cases this session variable value should be zero. However, instead,
it is giving null value and this null value is messing up the update
statement which is supposed to update the table in the database.
This session variable value being null is trying to update a
currency field which gives the above error. The sql statement via
response.write is as follows:

<snip>
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,


Assuming CurrentOutlay is one of your currency fields, you need to
be aware that currency is a numeric datatype. Are you really going
to the trouble of putting the quotes and "$" in these values? There
is no need to do so. Currency is a numeric datatype. This SET
statement can be changed to:

CurrentOutlay=0

with the same effect as the statement you currently are using, only
much simpler. (Simpler still would be a saved parameter query, but I
think I've already mentioned that to you.)

Also, you have a single table in your query. There is no chance that
the query engine is going to get confused as to which table the
fields referenced in your query are coming from, so there is no need
to fully qualify each field name with that god-awfully long table
name: it makes it so much harder to read. Just use the field names.
Only use the table name qualifier if the query includes two or more
tables, and better yet, use table aliases when you do.


You need to check the session variable value and substitute the word
NULL for it if it is empty:

dim value
value = session("variablename")
if len(value) = 0 then value = "NULL"

sSQL = "...., CurrentOutlay= " & value & ", ..."

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #7
Got it Bob. Thanks.

"Bob Barrows [MVP]" wrote:
Jack wrote:
Hi Bob,
Sorry for not being able to reply back early. Got caught up with
something. Thanks for your generous help again. I think I am not sure
why would you convert the zero length to null as advised by you at
the end of your comments.


zero length string <> Null
Would it not be the other way round i.e.
from null to 0. Thanks.


Depends on what you want. If your field is not required, you can enter a
Null in it. If it is required, then you probably want to enter a zero. What
you cannot do is this:

...., fieldname=, ...

That will cause an error. Either this:

...., fieldname = Null, ...

or this:

...., fieldname = 0, ...

will work

"Bob Barrows [MVP]" wrote:
Jack wrote:
Hi,
I have posted this problem before. Apprently, the suggestion took
care of the problem. However, still I am getting the above error
message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most
cases this session variable value should be zero. However, instead,
it is giving null value and this null value is messing up the update
statement which is supposed to update the table in the database.
This session variable value being null is trying to update a
currency field which gives the above error. The sql statement via
response.write is as follows:
<snip>
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,

Assuming CurrentOutlay is one of your currency fields, you need to
be aware that currency is a numeric datatype. Are you really going
to the trouble of putting the quotes and "$" in these values? There
is no need to do so. Currency is a numeric datatype. This SET
statement can be changed to:

CurrentOutlay=0

with the same effect as the statement you currently are using, only
much simpler. (Simpler still would be a saved parameter query, but I
think I've already mentioned that to you.)

Also, you have a single table in your query. There is no chance that
the query engine is going to get confused as to which table the
fields referenced in your query are coming from, so there is no need
to fully qualify each field name with that god-awfully long table
name: it makes it so much harder to read. Just use the field names.
Only use the table name qualifier if the query includes two or more
tables, and better yet, use table aliases when you do.

You need to check the session variable value and substitute the word
NULL for it if it is empty:

dim value
value = session("variablename")
if len(value) = 0 then value = "NULL"

sSQL = "...., CurrentOutlay= " & value & ", ..."

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 22 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Stephen Briley | last post by:
For some reason, my posts are scrubbed as attachments. Lets hope that sending from the yahoo account works. I'm new to Python and I'm trying to do some database work with MS Access, but I can't...
2
by: Steve Briley | last post by:
I'm new to Python and I'm trying to do some database work with MS Access, but I can't seem to get around a "datatype mismatch error".&nbsp; Here's an example table that I'm working with... ...
7
by: Jack | last post by:
Hi, I am trying to test a sql statement in Access which gives me the error as stated in the heading. The sql statement is built as a part of asp login verification, where the userid and password...
3
by: Laurel | last post by:
this is driving me crazy. i need to use a form control as a criteria in a select query, and the control's value is set depending upon what a user selects in an option group on the form. the query...
0
by: news.paradise.net.nz | last post by:
I have been developing access databases for over 5 years. I have a large database and I have struck this problem with it before but can find nothing in help or online. Access 2000 I have a query...
1
by: ArcadeJr | last post by:
Good morning all! I have been getting a Run-time Error message #3464 - Data Type mismatch in criteria expression. While trying to run a query. I have a database where the field Asset_Number...
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...
1
by: amitbadgi | last post by:
I am getting the following error while converting an asp application to asp.net Exception Details: System.Runtime.InteropServices.COMException: Data type mismatch in criteria expression. ...
4
by: bleighfield | last post by:
Hi everyone Hope someone can help with this one.. Background: I work in vehicle fleet, I have built something to 'predict' when a car/van service is due (it's fairly simple, calculates...
1
by: DC | last post by:
Any idea why this code would cause a error? The SQL that gets executed seems to be (using record 1132 as an example) DELETE FROM user_table WHERE ID = '1132'; And the value 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:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
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
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: 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: 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 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.