simple query

[TheRiver]

I have following query within a module. All I want to do is include where Census_Date = NULL then this also is updated with str_censusdate. I don't think <> does this.


Create.Execute &quot;UPDATE [WL CMDS (RNA00)] SET Census_Date = '&quot; & str_censusdate & &quot;' WHERE Census_Date <> '&quot; & str_censusdate & &quot;'&quot;

 

[jsteph]

the correct syntax is &quot;IS NULL&quot; or &quot;IS NOT NULL&quot;, so if & str_censusdate is a string, it can't be null, and check to see what datatype Census_Date is, you're using a string for criteria, which will work, but i'd consider using the # delimiter.

You'll need to change the code to include the &quot;=&quot; sign for all non-empty str_censusdate variables, then if it's an empty string, make the code say &quot;IS NULL&quot;.
--jsteph

 

[jsteph]

...sorry...I didn't even read the entire sql--I was thinking this was a WHERE clause.

Just remove the delimiters, so the final sql says Set Census_Date = NULL, what it sees now(assuming you have the word NULL in the variable) is:
Set Census_Date = 'NULL'
..which is an error.
--jsteph

 

[TheRiver]

I'm lost!!!

 

[JonFer]

You want the sql to be:

Update Table Set Field=Null Where Field Is Not Null;

Notice that there are not quotes. The word Null is a keyword and you do not enclose it in quotes. When you set a field to Null, it doesn't put the text string &quot;NULL&quot; in it, this actually blanks out the field.