Empty field in .mdb to be filled with 9999 vba

Question

I have tblTFM that has new inserts from TempTable_Update, and TempTable has an blank field, which I want to see ,for example, as 9999.

This is my code to insert new fields, it works fine.

myDB.Execute "INSERT INTO tblTFM(TFMAgreNu)" _
                & "SELECT DISTINCT [TempTable_Update].[TFMAgreNu] " _
                & "FROM TempTable_Update " _
                & "WHERE (((Exists (SELECT TFMAgreNu FROM tblTFM WHERE tblTFM.TFMAgreNu = TempTable_Update.TFMAgreNu))=False));"


myDB.Execute "UPDATE TempTable_Update " _
                & "INNER JOIN tblTFM ON [TempTable_Update].[TFMAgreNu] = [tblTFM].[TFMAgreNu] " _
                & "SET [TempTable_Update].[TFM_ID] = [tblTFM].[TFM_ID] " _
                & "WHERE [TempTable_Update].[TFMAgreNu] = [tblTFM].[TFMAgreNu];"

But I did research how to do UPDATE to set blank fields as 9999 and tried this...

strQuery = "UPDATE [tblTFM] SET [tblTFM].[TFMAgreNu] = 0 " _
            & "WHERE ((([tblTFM].[TFMAgreNu])="")); " _
            & "UPDATE [tblTFM] SET [tblTFM].[TFMArgeNu] = 9999 " _
            & "WHERE ((([tblTFM].[TFMAgreNu])= 0 ));"

It didnt work well

and this...

strQuery = "UPDATE tblTFM SET tblTFM.TFMAgreNu = 9999 WHERE tblTFM.TFMAgreNu IS NULL

Didn't work as well...

Could somebody please help me with the code for this update?

Answer 1

You can't have two UPDATE statements in one query, as in your first try.

If TFMAgreNu is a number field, then this should work:

strQuery = "UPDATE tblTFM SET tblTFM.TFMAgreNu = 9999 " & _
           "WHERE Nz(tblTFM.TFMAgreNu, 0) = 0"

It checks for NULL and 0 by using the Nz() function.

EDIT

For a text field, use

strQuery = "UPDATE tblTFM SET tblTFM.TFMAgreNu = '9999' " & _
           "WHERE Nz(tblTFM.TFMAgreNu, '') = ''"