Conditional Replace

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need to find a way to replace a blank field with characters.

Any field that has 'no data' to contain 'UK'

Had a question about this here a while ago but it has been deleted...

Thanks

SGC
 
Need to find a way to replace a blank field with characters.

Any field that has 'no data' to contain 'UK'

UPDATE MyTable
SET MyField = "UK"
WHERE MyField IS NULL
OR LEN(MyField)=0;


Hope that helps


Tim F
 
hi Steve,

Steve said:
Need to find a way to replace a blank field with characters.

Any field that has 'no data' to contain 'UK'

Had a question about this here a while ago but it has been deleted...
or:

UPDATE MyTable
SET MyField = "UK"
WHERE LEN(TRIM(NZ(MyField, "")))=0;


mfG
--> stefan <--
 
Thanks

Where do I enter these?

SGC

Stefan Hoffmann said:
hi Steve,


or:

UPDATE MyTable
SET MyField = "UK"
WHERE LEN(TRIM(NZ(MyField, "")))=0;


mfG
--> stefan <--
 
hi Steve,

Steve said:
Where do I enter these?
You need some VBA coding. Put a command button on a form, use the On
Click event:

Private Sub cmd_Click()

On Local Error GoTo LocalError

Dim SQL As String

SQL = "UPDATE MyTable SET MyField = 'UK' " & _
"WHERE LEN(TRIM(NZ(MyField, ")))=0;"

CurrentDb.Execute SQL, dbFailOnError

Exit Sub

LocalError:
MsgBox "Error: " & Err.Description

End Sub


mfG
--> stefan <--
 
Sorry, you have lost me.

I already have the database. Some of the records the 'Country' field is
blank (contains no data).

I need to replace all the blanks in the field 'Country' and replace with 'UK'

There was an answer to this here, maybe a year ago, that worked but I lost
the PC with the solution on. Can't remember how it was entered but it may
have been a Query(?)

Thanks

SGC
 
Found it - Pan be praised for backups...

Create a new Query field.

Give it this name -

All Country: IIf([country] Is Null Or Trim([country])="","UK",[country])

Taking that a field is called 'country'

Thanks for being there.

SGC
 
hi Steve,

Steve said:
All Country: IIf([country] Is Null Or Trim([country])="","UK",[country])
Or shorter:
All Country: IIf(Len(Trim(Nz([country]))),"UK",[country])


mfG
--> stefan <--
 
Back
Top