replace values is null with another values

[dand_dd]

Hi!

This function remove emty spaces from table fields

Function KillAllBlank(TempString)

Dim Temp As String
Dim Zeichen As String
Dim NeuerString As String
Dim i As Integer

NeuerString = ""

If IsNull(TempString) Then
' Wenn kein Inhalt im Feld vorhanden ist,
' wird die Funktion abgebrochen
Exit Function
Else
Temp = CStr(TempString)
For i = 1 To Len(Temp)
Zeichen = Mid$(Temp, i, 1)
If Zeichen <> Chr(32) Then
NeuerString = NeuerString & Zeichen
End If
Next i
' Das Ergebnis wird an die Funktion wieder ?bergeben
KillAllBlank = NeuerString
End If

End Function


and this query UPDATE tmpNAP SET tmpNAP.F1 = KillAllBlank([F1]), tmpNAP.F2 = KillAllBlank([F2]), tmpNAP.F3 = KillAllBlank([F3]), tmpNAP.F4 = KillAllBlank([F4]), tmpNAP.F5 = KillAllBlank([F5]), tmpNAP.F6 = KillAllBlank([F6]), tmpNAP.F9 = KillAllBlank([F9]), tmpNAP.F10 = KillAllBlank([F10]), tmpNAP.F11 = KillAllBlank([F11]), tmpNAP.F12 = KillAllBlank([F12]), tmpNAP.F13 = KillAllBlank([F13]), tmpNAP.F14 = KillAllBlank([F14]), tmpNAP.F15 = KillAllBlank([F15]), tmpNAP.F16 = KillAllBlank([F16]), tmpNAP.F17 = KillAllBlank([F17]), tmpNAP.F18 = KillAllBlank([F18]), tmpNAP.F20 = KillAllBlank([F20]), tmpNAP.F21 = KillAllBlank([F21]), tmpNAP.F22 = KillAllBlank([F22]), tmpNAP.F23 = KillAllBlank([F23]), tmpNAP.F24 = KillAllBlank([F24]), tmpNAP.F25 = KillAllBlank([F25]), tmpNAP.F26 = KillAllBlank([F26]), tmpNAP.F27 = KillAllBlank([F27]), tmpNAP.F28 = KillAllBlank([F28]), tmpNAP.F29 = KillAllBlank([F29]), tmpNAP.F30 = KillAllBlank([F30]), tmpNAP.F31 = KillAllBlank([F31]), tmpNAP.F32 = KillAllBlank([F32]), tmpNAP.F33 = KillAllBlank([F33]), tmpNAP.F34 = KillAllBlank([F34]), tmpNAP.F35 = KillAllBlank([F35]), tmpNAP.F36 = KillAllBlank([F36]), tmpNAP.F38 = KillAllBlank([F38]), tmpNAP.F39 = KillAllBlank([F39]), tmpNAP.F40 = KillAllBlank([F40]), tmpNAP.F41 = KillAllBlank([F41]), tmpNAP.F42 = KillAllBlank([F42]), tmpNAP.F43 = KillAllBlank([F43]), tmpNAP.F45 = KillAllBlank([F45]), tmpNAP.F46 = KillAllBlank([F46]), tmpNAP.F47 = KillAllBlank([F47]), tmpNAP.F48 = KillAllBlank([F48]), tmpNAP.F49 = KillAllBlank([F49]), tmpNAP.F51 = KillAllBlank([F51]), tmpNAP.F52 = KillAllBlank([F52]), tmpNAP.F53 = KillAllBlank([F53]), tmpNAP.F54 = KillAllBlank([F54]), tmpNAP.F55 = KillAllBlank([F55]), tmpNAP.F56 = KillAllBlank([F56]), tmpNAP.F57 = KillAllBlank([F57]), tmpNAP.F58 = KillAllBlank([F58]), tmpNAP.F59 = KillAllBlank([F59]), tmpNAP.F60 = KillAllBlank([F60]), tmpNAP.F61 = KillAllBlank([F61]), tmpNAP.F62 = KillAllBlank([F62]), tmpNAP.F63 = KillAllBlank([F63]);


The function works perfectly
I want to replace empty fields (is null ) from table with a value x.
I can use one query for one row (but i have 60 rows).
How to modify this funtion ?

[Oorang]

If all you want to do is set null values to zero length strings, you can do that with an update query:

UPDATE MyTable SET MyTable.MyField = "" WHERE MyTable.MyField Is Null;

[dand_dd]

Yes. I only want to write a value where fields are empty.But i have 60 My field. So i have to make 60 queries for this process? Or it?s much simple with a VBA function and one query?

UPDATE MyTable SET MyTable.MyField = "x" WHERE MyTable.MyField Is Null;

[CreganTur]

Yes. I only want to write a value where fields are empty.But i have 60 My field. So i have to make 60 queries for this process? Or it?s much simple with a VBA function and one query?

UPDATE MyTable SET MyTable.MyField = "x" WHERE MyTable.MyField Is Null;

I'm shooting from the hip here, but I think you can accomplish this by using a good, old-fashioned wildcard (*). In SQL using an asterisk means that you want to pull all of something. So try:



UPDATE MyTable SET MyTable.* = "" WHERE MyTable.* Is Null;


That code should update all fields in the selected table, where the field IsNull, to an empty string ("").

Try that out and see if it works. But, as I said at the start, I'm only shooting from the hip here.

[dand_dd]

Unfortunately it' not working.

[Oorang]

This is just throw-away code (which I have not tested), so don't play with it on your production copy ;) But try this:
Sub Example()
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim strTblNm As String
strTblNm = "MyTable"
Set td = Access.CurrentDb.TableDefs(strTblNm)
For Each fld In td.Fields
DoCmd.RunSQL "UPDATE MyTable SET [" & strTblNm & "].[" & fld.Name & _
"] = """" WHERE [" & strTblNm & "].[" & fld.Name & "] Is Null;"
Next
End Sub