getting type mismatch

[John]

I am getting a type mismatch from the approved_limit below. The field
is actually a decimal (10,2) in sql server 2005 table. I am getting a
type mismatch error. I also suspect that NULL is going to need to be
managed. A SUB inside the form was created that controls all the
controls based on status. It works and has been there for many
years. It even predates my takeover of this db. That is where I
would like to add this. But first why am I getting a Type Mismatch
error plus how would I handle the Null issue?

Dim approved_limit As Double
approved_limit = "SELECT ApprovedAmount FROM PurchaseOrders WHERE
PONumber = " & PONumber & " "

Thanks for reviewing and responding.
John

 

[John W. Vinson]

I am getting a type mismatch from the approved_limit below. The field
is actually a decimal (10,2) in sql server 2005 table. I am getting a
type mismatch error. I also suspect that NULL is going to need to be
managed. A SUB inside the form was created that controls all the
controls based on status. It works and has been there for many
years. It even predates my takeover of this db. That is where I
would like to add this. But first why am I getting a Type Mismatch
error plus how would I handle the Null issue?

Dim approved_limit As Double
approved_limit = "SELECT ApprovedAmount FROM PurchaseOrders WHERE
PONumber = " & PONumber & " "

Thanks for reviewing and responding.
John

What's the datatype of the PONumber? I'm guessing it's Text, and that
delimiting the criterion with quotes will get rid of this message:

approved_limit = "SELECT ApprovedAmount FROM PurchaseOrders WHERE
PONumber = '" & PONumber & "'"

For readability (don't do it this way) that's

PONumber = ' " & PONumber & " ' "

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Douglas J Steele]

You've declared approved_limit as Double, which means it can only accept
numeric values, but you're assigning it a string!

I think what you want is:

approved_limit = Nz(DLookup("ApprovedAmount", "PurchaseOrders", "PONumber =
" & PONumber), 0)



"John" wrote in message

I am getting a type mismatch from the approved_limit below. The field
is actually a decimal (10,2) in sql server 2005 table. I am getting a
type mismatch error. I also suspect that NULL is going to need to be
managed. A SUB inside the form was created that controls all the
controls based on status. It works and has been there for many
years. It even predates my takeover of this db. That is where I
would like to add this. But first why am I getting a Type Mismatch
error plus how would I handle the Null issue?

Dim approved_limit As Double
approved_limit = "SELECT ApprovedAmount FROM PurchaseOrders WHERE
PONumber = " & PONumber & " "

Thanks for reviewing and responding.
John

 

[John]

You've declared approved_limit as Double, which means it can only accept
numeric values, but you're assigning it a string!

I think what you want is:

approved_limit = Nz(DLookup("ApprovedAmount", "PurchaseOrders", "PONumber =
" & PONumber), 0)

"John"  wrote in message


I am getting a type mismatch from the approved_limit below.  The field
is actually a decimal (10,2) in sql server 2005 table.  I am getting a
type mismatch error.  I also suspect that NULL is going to need to be
managed.   A SUB inside the form was created that controls all the
controls based on status.  It works and has been there for many
years.  It even predates my takeover of this db.  That is where I
would like to add this.  But first why am I getting a Type Mismatch
error plus how would I handle the Null issue?

    Dim approved_limit As Double
    approved_limit = "SELECT ApprovedAmount FROM PurchaseOrders WHERE
PONumber = " & PONumber & " "

Thanks for reviewing and responding.
John

I would like to thank you both for your response. But I got a call
about 30 mins ago and there have been some requirement changes, which
will affect what is here. Maybe even to the point of no longer
needed. Will post back again if the requirements are such that I have
further questions.
Thanks again.
John