updating field whose value is the word NULL and Not a typical 'null' value

[russi]

I was given a table where in a text field, the value is NULL. (Actually spelled out like that.)
I need to convert that field to a date/time one. Which is being hindered by these values of NULL.

The problem is that when I put the word NULL in the field criteria spot, Access believes that I am referring to a value of 'null' and will not do the update.
I have done "NULL", NULL, =NULL, ="NULL", is NULL... and no success.
Please remember that the actual value is not 'null'. It is a text field that has the word NULL in the field.

Ideas?

Russ

 

[WineSnob]

There may be a better way than this BUT -
You can export the table to excel then sort the column with the "Null" and delete the "Null" Values. Then attach the excel sheet and do an update query. HTH.

 

[pbaldy]

I just tested this and it worked fine:

SELECT...
FROM...
WHERE DeveloperID="null"

It returned a test record with that text in the field, and properly excluded a record with a Null value.

 

[MSAccessRookie]

I was given a table where in a text field, the value is NULL. (Actually spelled out like that.)
I need to convert that field to a date/time one. Which is being hindered by these values of NULL.

The problem is that when I put the word NULL in the field criteria spot, Access believes that I am referring to a value of 'null' and will not do the update.
I have done "NULL", NULL, =NULL, ="NULL", is NULL... and no success.
Please remember that the actual value is not 'null'. It is a text field that has the word NULL in the field.

Ideas?

Russ

If I understand correctly, you need to:

• Convert a Text Field to a date/time Field.
• Determine a way to handle Text Fields with values of "NULL".

As I see it, How to handle Text Fields with values of "NULL" depends on what you want to do with them.

If you want to ignore them:

IIf(YourTextField="NULL", "", cDate(YourTextField))

If you want to Include them:

IIf(YourTextField="NULL", {Default Date in Text Format}, cDate(YourTextField))

Try one of these.

-- Rookie