CONVERT in WHERE statement

  • Thread starter Thread starter SReed via AccessMonster.com
  • Start date Start date
S

SReed via AccessMonster.com

I am trying to populate a form using variables. I want the form to open with
all the records from the table displayed. A couple of the fields I am using
as variables are integers and I am unable to get the table to populate when
it is opened if these variables are null. I can CONVERT the variable from an
integer to a varchar and the query runs fine but when the form opens it gives
me and error that the value for the field I converted can not be null. Any
ideas on how to accomplish this task?
 
I am trying to populate a form using variables. I want the form to open with
all the records from the table displayed. A couple of the fields I am using
as variables are integers and I am unable to get the table to populate when
it is opened if these variables are null. I can CONVERT the variable from an
integer to a varchar and the query runs fine but when the form opens it gives
me and error that the value for the field I converted can not be null. Any
ideas on how to accomplish this task?
Click to expand...

Please post your code.

John W. Vinson [MVP]
 
Sorry about that, I actually have figured out how to get the data I need
using:

WHERE (PO# LIKE '%' + COALESCE (@PO#, N'%') + '%') AND (CustomerID =
COALESCE (@CustomerID, CustomerID)) AND (carrierID = COALESCE (@carrierID,
carrierID))

I am trying to populate a form using variables. I want the form to open with
all the records from the table displayed. A couple of the fields I am using
Click to expand...
[quoted text clipped - 3 lines]
me and error that the value for the field I converted can not be null. Any
ideas on how to accomplish this task?
Click to expand...

Please post your code.

John W. Vinson [MVP]
Click to expand...
 
Sorry about that, I actually have figured out how to get the data I need
using:

WHERE (PO# LIKE '%' + COALESCE (@PO#, N'%') + '%') AND (CustomerID =
COALESCE (@CustomerID, CustomerID)) AND (carrierID = COALESCE (@carrierID,
carrierID))
Click to expand...

So you're using SQL Server rather than Access JET (the subject of this
newsgroup), it seems. Glad it's working for you.

John W. Vinson [MVP]
 
Back
Top