While it's possible, I question whether it's really necessary. You can
always use WHERE Nz(Table1.Field1, "NA") = Nz(Table2.Field1, "NA") rather
than WHERE Table1.Field1 = Table2.Field1
Fred's right, though, that an Update query (or, more accurately, a series of
Update queries) is the correct way to go. It's almost always more efficient
to use SQL than VBA to accomplish the same task. Your Update queries would
be something like:
UPDATE Table1 SET Field1 = "NA" WHERE Field1 IS NULL
UPDATE Table1 SET Field3 = "NA" WHERE Field3 IS NULL
Now, you can certainly use VBA to loop through the tables to generate the
queries. Obviously you only need to worry about fields that are Text or
Memo, so code like the following should work:
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String
Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
If fldCurr.Type = dbText Or _
fldCurr.Type = dbMemo Then
strSQL = "UPDATE [" & tdfCurr.Name & "] " & _
"SET [" & fldCurr.Name & "] = 'NA' " & _
"WHERE [" & fldCurr.Name & "] IS NULL"
dbCurr.Execute strSQL, dbFailOnError
End If
Next fldCurr
End If
Next tdfCurr
Set dbCurr = Nothing
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
fambaus said:
Hi Fred -
I appreciate your time with my issue.
I will certainly provide more specifics.
The situation is this:
I have importred a great deal of data into Access.
The sourcce of the database was an extremely large Oracle db.
The data was exported from Oracle in CSV format and imported into Access
with the use of import specification for each file/table.
BTW - Linking the tables into Access was not an option either as the
Oracle
instance contains thousands of tables. Set up was tedious at best for
about
ten tables.
Turns out that altough some of the fields have the Not Null provision in
Oracle, Access has converted blank fields to Null. This is causing me
problem
with my Access queries since as we know Null is not equal Null.
I could conceivably created an update query on each table and convert each
occurance of Null to NA etc. I did this with two table and found it
cumbersome.
Thus my posting to this forum. About ten years ago I probably wrote a
function something similar to what I need. Unfortunately things have
advanced
and I have fallen behind.
Thank you once again,
Mike