ummmmmmmmmmm ... mmmmmmmmmmmm ... mmmmmmm
obviously not so good, else I would be able to get the message. either to or from, but at least a message.
for me, it is not so much the doing which is difficult, which SEEMS like it could be simplistically accomplished.
just to 'get over it'
A way to do it
MAY be to follow
GHoldens' suggestion:
Not In (1,2,3,4,5,6,9,8,9)
in the criteria ROW of each FIELD which you want to "adjust", with the Update To ROW as
0
In this approach, you must do each field SEPERATLY, as the criteria apply to all the fields collectively (as either AND or OR, depending on the row(s) for the criteria).
I also "re-wrote" you function:
Code:
Public Function basNull2Zero(strTblName As String)
'Michael Red 1/8/2003 Tek-Tips thread700-440522
'for Darlene Sippiodsippio@ comtechsystems.com
'a.k.a DIVINEDAR0956
'? basNull2Zero("tblSalary")
Dim dbs As DAO.Database 'Define a db object
Dim rst As DAO.Recordset 'Define a recordset objets
Dim Idx As Integer
Set dbs = CurrentDb 'Instantiate the Database as the currnt db
'Instantiate the recordset using the db and the given table name as dynamic
Set rst = dbs.OpenRecordset(strTblName, dbOpenDynaset)
While Not rst.EOF 'Loop through the WHOLE table one rec at a time
Idx = 0
While Idx <= rst.Fields.Count - 1 'Check each field for the name game
If (Left(rst.Fields(Idx).Name, 5) = "Month") Then 'Only do Montly ones
'Here because SOME field is a "Month"
'Check content is number
If (Not IsNumeric(rst.Fields(Idx))) Then
'Here because it doesn't have a number in it!
rst.Edit 'So edit it
rst.Fields(Idx) = 0
rst.Update
End If
End If
Idx = Idx + 1
Wend
rst.MoveNext
Wend
End Function
O.K. So NOW, I HAVE shown "how". Perhaps you would be willing to discuss the WHY part?
There really is NO pratical difference between the [empty (""
| Null | ANY COMMON VALUE] in the db. NULL, Empty or any common value may be found, or sorted or relaced throughought the (well formed) recordset. Null actually has the advantage of NOT being included in aggregate functions. Your "insistance" on replacing nothing with something is just not generally the way to maintain a database. Think about what you are doing. A rule I like to impose on operations is that unless there is a specific objective which will be acomplished and alter the data in a predictable (and NON-uniform) manner, the operation should NOT be carried out. You ARE violating this in at least one way, and possibly more.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
