SQL to avoid empty text field in access

[cf59102]

I am running a query to pull some database records, but I want to avoid records that have an empty text field.

So I was thinking of something like this:
<CFQUERY>
.
.
.
.
AND (Staff.MName <> '')
</CFQUERY>

but that did not seem to work - there is no space between my single quotes in the CFQuery above.

Any suggestions would be welcome!

 

[webmigit]

AND (Staff.Mname != '')
AND NOT (Staff.Mname = '')

And various others should all work.

Try...

AND NOT ([red]Trim([/red]Staff.Mname[red])[/red] = '')

Its possible because of your field type or because of misentered data that the field may contain a space or multiple spaces. Trim() will fix that.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[cf59102]

Thanks - will give that a shot.

I do have a follow up...will I have to change this CFQUERY code if later if I upgrade this database to SQL server?

I do use the trim function and CFQUERYPARAM when I am inserting and updating records to eliminate padding and spaces...

Appreciate your feedback on the above.

 

[webmigit]

Well Access & SQL Server are both my Microsoft.. and they kept a lot of basic things the same, but yeah, there'll be same changes.. annoying ones.

DELETE * FROM TABLE will become DELETE FROM TABLE.. which I kinda get.. you can't delete half a row.. but at the same time, they should make both valid in both languages.

But it won't be as annoying as upgrading to mysql or oracle or anything. They kept most basic things the same, and usually whatever Access can do, Server can do better.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.