bobby6055
asked on
Replace function in query
TableA.Field1 has several "null" value in several records in the table.
I will like to replace all the Null value in TableA.Field1 using a where Clause...like this:
'Update Field1 in TableA
sSQL = "UPDATE TableA Replace (Field1 = Null with nothing ('')
sSQL = sSQL & "WHERE Field7='LT' And Field1 = 'Null';"
DoCmd.RunSQL sSQL
In otherwords, Field1 current has "Null" placed as the value in that field. What I want to do is run a query that remove all "Null" value in field1
using a WHERE Clause Field7 = 'LT' And Field1 = Null
I will like to replace all the Null value in TableA.Field1 using a where Clause...like this:
'Update Field1 in TableA
sSQL = "UPDATE TableA Replace (Field1 = Null with nothing ('')
sSQL = sSQL & "WHERE Field7='LT' And Field1 = 'Null';"
DoCmd.RunSQL sSQL
In otherwords, Field1 current has "Null" placed as the value in that field. What I want to do is run a query that remove all "Null" value in field1
using a WHERE Clause Field7 = 'LT' And Field1 = Null
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
No, Patrick's
To be clear:
What does Field1 currently contain - Null or the word "Null"?
Do you want to set it to Null, or to an empty string? If empty string, does your table design for this field allow empty strings (that is, is Allow Zero Length set to Yes)?
WHERE Field1 Is Null
is correct. Null has no equality.To be clear:
What does Field1 currently contain - Null or the word "Null"?
Do you want to set it to Null, or to an empty string? If empty string, does your table design for this field allow empty strings (that is, is Allow Zero Length set to Yes)?
If you mean that Field1 actually has the *word* Null as a value, and you want to set that to an actual Null (nothing) ... then try this:
UPDATE Table1 SET Table1.FIELD1 = Null
WHERE (((Table1.FIELD1)="Null") AND ((Table1.FIELD7)="LT"))
mx
UPDATE Table1 SET Table1.FIELD1 = Null
WHERE (((Table1.FIELD1)="Null") AND ((Table1.FIELD7)="LT"))
mx
Hi,
Try this....
Try this....
UPDATE Table1
SET Table1.FIELD1 = ''
WHERE
IsNull(Table1.FIELD1,'NULL')='Null'
AND Table1.FIELD7)="LT"ASKER
To All:
I actually tweaked Matthew's previous suggested query and it works as follows:
sSQL = "UPDATE TableA " & _
"SET Field1 = '' " & _
"WHERE Field1 Is Null And Field7 = 'LT';"
I actually tweaked Matthew's previous suggested query and it works as follows:
sSQL = "UPDATE TableA " & _
"SET Field1 = '' " & _
"WHERE Field1 Is Null And Field7 = 'LT';"
ASKER
sorry....I hit return key too fast...
it should be..
sSQL = "UPDATE TableA " & _
"SET Field1 = '' " & _
"WHERE Field7 = 'LT' AND Field1 = 'Null';"
it should be..
sSQL = "UPDATE TableA " & _
"SET Field1 = '' " & _
"WHERE Field7 = 'LT' AND Field1 = 'Null';"
ASKER
I tweak Mathew's original suggested solution.
Setting a Field to a Space (" ") which is what you have done is not a good is idea. In fact, really it is a bad idea. A Space looks like a Null, but it's not. But you cannot 'see' the difference visually. So, in the future when you query IsNull (or similar), you will get the wrong results. Fields should never be set to a Space or "" (empty string) unless there is some very specific reason for doing so.
FYI ... the solution I posted sets the Field to a real Null value.
mx
FYI ... the solution I posted sets the Field to a real Null value.
mx
ASKER
Mx:
Thanks, I'll consider it.
Thanks, I'll consider it.
ASKER
The sql did not remove the word "Null" in Field1
...your sql ........"WHERE Field1 Is Null
..should be......
"WHERE Field1 = Null (Field1 actually has Null value as the data placed in that field...this is the value I am trying to replace with no data ...set to nothing}.