Filtering subform causes null value issues

[vavroom]

I am using Split() to display part of the value of a field in a
subform. The subform has a filter to view only those records that have
a field checkbox=true.

This works well as long as I am viewing only the subform

When I view the form with the subform, if a record on the form happens
not to have records with that true checkbox, then I get an invalid use
of null error.

It points to this bit of code:
===
strFileName = Me.PaperConcat
varValues = Split(strFileName, " ")
If UBound(varValues) > 0 Then
lngNumber = CLng(varValues(0))
strPaper = varValues(0)
End If
Me.txtPaper = strPaper
===

I tried including that in its own if/else statement, checking first to
see if me.paperconcat is null, if it is, do one thing, if it's not,
then run that split routine. But that didn't work (possibly because I
wouldn't have written it properly?

I'm not sure how to modify this code to handle the null value? I guess
what I'd like it to do is that if me.paperconcat returns a null value,
then it should be ignored and txtPaper return display nothing.

 

[Douglas J. Steele]

How is strFileName declared? If it's a String (as the name implies), its
value cannot be Null. If it's a variant (which it would be if you haven't
explicitly declared it, which you should), it can be Null.

Assuming it can be Null:

strFileName = Me.PaperConcat
If Not IsNull(strFileName) Then
varValues = Split(strFileName, " ")
If UBound(varValues) > 0 Then
lngNumber = CLng(varValues(0))
strPaper = varValues(0)
End If
End If
Me.txtPaper = strPaper

 

[vavroom]

Thanks Doug,

That did indeed do the trick. I was declaring it as a string, as the
value in PaperConcat field is "stringey"... I didn't realise that you
couldn't have a Null value for such. Changing to variant made the
entire difference! Huzzah, thanks a lot

 

[vavroom]

Ok, so, it's working on a form, but I can't get it to work on a report


I'm still having the same field that I need to split into its 4
individual components. I obviously don't want to have them in actual
fields (why carry extra data if you can access it differently).

so a typical record for field ConcatPaper looks like this: "125225
0601 E 33"

I want to display only the first bit on the report.

If I put code on the report's On Page, it returns one value only, even
with several different ConcatPaper. If I put the code in the report's
On Open, I get a "you entered an expression that has no value" and
highlights the Concatpaper.

I'm feeling lost on this one. Any idea?

 

[vavroom]

I'm feeling lost on this one. Any idea?

Well, I've answered my own question... Doing searches on different,
not necessarily obvious, words sometimes bring results.

I looked up "on activate" report and went through some posts, someone
suggested putting code in the "on format" of the report's section
rather than "on activate" of the report itself. Tried it, works.

<phew>

Cheers and thanksa gain