correct syntax for setting up a SQL string for CurrentDb.Execute 1

[irethedo]

I use the CurrentDb.Execute command quite often to launch a query within my DBA code but sometimes the
syntax doesn't exactly match the SQL of my query like in the following:

This works:

    strSql = "delete * from SameOrd_tbl where License_PartNo is null"

    CurrentDb.Execute strSql

This is totally wrong:

    strSql = "Delete * FROM SameOrd_tbl WHERE (((SameOrd_tbl.License_PartNo)=" "))"

    CurrentDb.Execute strSql

How do I fix the totally wrong?


Thanks

 

[PWise]

i dont know you want to delete
try
strSql = "Delete * FROM SameOrd_tbl WHERE (((SameOrd_tbl.License_PartNo)=''))"
or
strSql = "Delete * FROM SameOrd_tbl WHERE (((SameOrd_tbl.License_PartNo)=' '))"

do you know what the difference between
1)null
2)empty string
3)space
is?

 

[dhookom]

If the field actually might contain a single space then try:

strSql = "Delete * FROM SameOrd_tbl WHERE License_PartNo=' '"

Or

strSql = "Delete * FROM SameOrd_tbl WHERE License_PartNo="" """

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

How is the [tt]License_PartNo[/tt] field defined in the table? A text? A number? Is NULL allowed?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Thanks Duane-

strSql = "Delete * FROM SameOrd_tbl WHERE License_PartNo=' '"

This did the trick