Thanks, I sort of thought this from a response from someone else but I am
just learning so I wanted to do a little with ADO.
To answer your other question about nulls. I have a report that uses a
parameter query as a record source and draws the parameter values from a LOT
of combo boxes. If any records are null in fields that I have set a parameter
"criteria" then that record will not be returned by the query.
I think I see what you are getting at. I will give it a try.
many thanks!
P.S- I looked to see who you were and saw your message. I also did a little
wine making. Really don't have the time for it anymore. I have a yeast (I
think, i still have some of the brew) that would not grow on artificial media
such as nutrient agar but grew well in certain brews (could see active
budding etc. under microscope) thus i am assuming that it used the
metabolites of other organisms as an energy source. Interestingly it gave a
brew with tast very like sherry without all the trouble. I use to use it in
cooking. This (the use of consortia or non-typical yeasts) was something that
i never saw mention with brewers but, for me, a fun topic.
thanks again!
"Douglas J Steele" wrote:
> I don't understand why you're doing what you are are in the recordset. It
> would be far simpler to create queries that use the Nz function to set the
> null values as you're doing, then use the queries rather than the tables.
>
> Even if you want the changes to be made permanently, using Update queries is
> going to be far more efficient than using recordsets and looking at each
> value in each field programmatically.
>
> For your last question, the answer's no, numeric and date fields cannot
> contain ZLS.
>
> Why can't you work with Null values anyhow?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "salmonella" <(E-Mail Removed)> wrote in message
> news:A9FE4781-51EA-47E5-AE6C-(E-Mail Removed)...
> > I am very new to programing (learning as i need it). Basically I want to
> make
> > sure that all fields have a non-null value. The code I have written below
> > works fine for the table I am generating the record set from but I have 15
> > other tables I want to do the same thing with. My questions are:
> >
> > 1. Is there a simple way to loop through all tables in the current project
> > instead of having to generate a recordset for each table, one at a time?
> > 2. I am thinking of splitting the database. I assume that the connection
> > string needs to be modified. How is this done?
> > 3. Is there a way to use a zero length string with numbers instead of
> having
> > to put a 0 or dummy date so that the field is not null, as i did below?
> >
> > Many thanks!
> >
> > Dim cnn As New adodb.Connection
> > Dim rst As New adodb.Recordset
> > Dim fld As adodb.Field
> >
> > Set cnn = CurrentProject.Connection
> >
> > rst.open " select * from health_patient", cnn, adOpenStatic,
> adLockPessimistic
> >
> > With rst
> >
> > Do Until .EOF
> >
> > 'check each field in the table and, depending on data type, if null change
> > to a value
> > For Each fld In .Fields
> > If fld.Type = 203 Or fld.Type = 202 Then
> > If IsNull(fld.Value) Then
> > fld.Value = "No data"
> > End If
> >
> > ElseIf fld.Type = 3 Then
> > If IsNull(fld.Value) Then
> > fld.Value = 0
> > End If
> >
> > ElseIf fld.Type = 7 Then
> > If IsNull(fld.Value) Then
> > fld.Value = #1/1/1111#
> > End If
> >
> > End If
> > Next
> >
> > .MoveNext
> > Loop
> > End With
> > Set cnn = Nothing
> > Set rst = Nothing
> >
>
>
>
Similar Threads
