Re: Error: Data type mismatch in criteria expression

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 11:57:13 -0500

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.

Relevant Pages

  • Re: Error: Data type mismatch in criteria expression
    ... Assuming CurrentOutlay is one of your currency fields, ... you have a single table in your query. ... qualify each field name with that god-awfully long table name: ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Error: Data type mismatch in criteria expression
    ... > Assuming CurrentOutlay is one of your currency fields, ... > that currency is a numeric datatype. ... (Simpler still would be a saved parameter query, ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: 2nd try, left join
    ... Thank Bob. ... and it seems to loop forever and return results in the millions when the highest result doing the query on Pet column at a time is less then 15000. ... >> You're going to need to provide some sample data and desired results ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: How do I give same value as primary key to other field
    ... > I also have a Field called ParentID ... After you run the above query you can get the value by ... Bob Barrows ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Dates between
    ... This is done in the query, ... >> Bob, ... >> Please only reply to this newsgroup. ... >> I do not reply to personal email. ...
    (microsoft.public.access.gettingstarted)