CONVERT in WHERE statement

[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?

 

[John W. Vinson]

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?

Please post your code.

John W. Vinson [MVP]

 

[SReed via AccessMonster.com]

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

[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?

Please post your code.

John W. Vinson [MVP]

 

[John W. Vinson]

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))

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]