testing for "" blank nulls

[Tony]

vs 2002/xp pro/win forms/MS access 2002

I want to query my database where fieldA = "" (Blank)

Select MYDB where fieldA = "" or Blank or Null

What is the correct syntax for checking for "" blanks nulls
thanks

 

[William Ryan eMVP]

I'm guessing you mean on the DB side vs DataTable.Select. Anyway, the
syntax is checking for '' (single quotes) or IS NULL (in access ISNULL
returns true or false if you don't put a space, it's a function instead of a
condition so make sure you use two words).

In general, client side if you want to test for '' use string.Empty.

HTH,

BIll

 

[Tony]

I've tried


SQL="select * from DB where RecordStatus = " & "'" & "'" & " order by
CompanyName"
Select * from DB where RecordStatus = '' order by CompanyName
and returns no records

also tried
SQL = "Select * from DB where RecordStatus = Is Null order by CompanyName"
also returns no records

also tried
SQL="select * from DB where RecordStatus = " & "'" & "Is Null" & "'" & "
order by CompanyName"
select * from DB where RecordStatus = 'Is Null' order by CompanyName"
also retrurns no records

The field RecordStatus is a newly created field and all records have a blank
or Null value
Where am I going wrong?

Thanks

 

[Norman Yuan]

SQL="SELECT * FROM DBTable WHERE (RecordStatus='') OR (RecordStatus IS
NULL) ORDER BY CompanyName"

 

[Matt Berther]

Hello Tony,

SELECT * FROM DB Where RecordStatus IS NULL OR RecordStatus = ''

 

[William Ryan eMVP]

Remember back from the old Relational Database class, "Null doesn't equal
anything not even itself"

Anyway you can't use an = sign if you are testing for null. Ever.

SQL = "Select * from DB where RecordStatus Is Null order by CompanyName"

If you just take the = out like above, you'll be fine.

HTH,

Bill

 

[Tony]

Thank you thank you and thank you

Remember back from the old Relational Database class, "Null doesn't equal
anything not even itself"

Anyway you can't use an = sign if you are testing for null. Ever.

SQL = "Select * from DB where RecordStatus Is Null order by CompanyName"

If you just take the = out like above, you'll be fine.

HTH,

Bill