Access200 VBA: Empty variable

A

Arvi Laanemets

Hi

I need to read row-wise data from text file (network scanning information),
and add according data rows into access table. The problem is, that
depending on text file, values for some column can be missing at all, or in
some row some single value is missing/nonvalid.

I'm storing field values, read from text row, as memory variables - so I can
use them (in append query) as field values, when adding a rows into access
table. I need that for all fields not present in source file, or not having
valid value, according Access table field will be empty (i.e. not 0 or "",
but Null). At moment I'm writing a code for text row reading and editing,
and I used formilas like this one:
varPing = IIf(posPing = 0, Null, Trim(Mid(TempLine, posPing * 22 - 21, 22)))
(i.e. when there exist a column with ping values in text file, a value from
this column is returned, otherwise nothing is returned)
But I have a bad feeling about this code, it looks like I can't set a
variable to have a Null value.

What will be best solution here? Maybe use some string value to indicate
empty values, like:
varPing = IIf(posPing = 0, "Null", Trim(Mid(TempLine, posPing * 22 - 21,
22)))
and use formulas like Iif(varPing="Null",Null,varPing) in append query, or
when editing new recordset fields (after rs.AddNew). I'm somewhat confused
here!
 
N

Nikos Yannacopoulos

Arvi,

Remembering a previous post of yours, you seem to have decided to go the
directly-reading-the-text-file way, right? In this case, it would be
much more efficient to use a recordset oeration to append records, than
to run an append query for each! Moreover, if you go that way it is very
easy to not set a field's value at all when you want it null, with
something like:

rst.AddNew
rst.fields(x) = ...
rst.fields(y) = ...
If posPing <> 0 Then
rst.fields(z) = Trim(Mid(TempLine, posPing * 22 - 21, 22)
End If
....
rst.Update

HTH,
Nikos
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top