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. 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.
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.
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.
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.
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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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". Here's an example table that I'm working with...
...
|
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...
|
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...
|
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...
|
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...
|
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: 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.
...
|
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...
|
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...
|
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:
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...
|
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:
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...
|
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: 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: 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: 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...
| |