empty string issue

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I have a variable defined as Long and when I set it, the data is sometimes an
empty string:

lngReportID= Nz(Me.PartyID.Column(4),0)

If Column(4) happens to be an empty string, I get a type mismatch. I also
tried

lngReportID=Nz(CInt(Me.PartyID.Column(4)),0) with no improvement

The recordsource for Me.PartyID is a passthrough from SQL and when there is
no data in the column, it comes across as an empty string. How can I solve
this either by changing the passthrough or Access vba?

Thanks!

Sam
 
With Me.PartyID
If Not IsError(.Column(4)) Then
If IsNumeric(.Column(4))
lngReportID = .Column(4)
End If
End If
End With
 
On Fri, 20 Mar 2009 06:31:01 -0700, Sam

Check out the IIf function. You can test for empty string and return
0.

-Tom.
Microsoft Access MVP
 
I have a variable defined as Long and when I set it, the data is sometimes an
empty string:

lngReportID= Nz(Me.PartyID.Column(4),0)

If Column(4) happens to be an empty string, I get a type mismatch. I also
tried

lngReportID=Nz(CInt(Me.PartyID.Column(4)),0) with no improvement

The recordsource for Me.PartyID is a passthrough from SQL and when there is
no data in the column, it comes across as an empty string. How can I solve
this either by changing the passthrough or Access vba?

Thanks!

Sam
Click to expand...

Well, a NULL is not the same as an empty string; the NZ() function will not
interpret "" as NULL.

If the field will *never* be NULL (only an zero-length-string) use

lngReportID = IIF(Me.PartyID.Column(4) = "", 0, Val(Me.PartyID.Column(4)))

If it might be null use

Me.PartyID.Column(4) & "" = ""
 
If it might be null use

Me.PartyID.Column(4) & "" = ""
Click to expand...

Michka always said that testing Len() = 0 was faster than comparing
two empty strings. Obviously, that difference is only going to
matter in a loop, but it's worth noting. Also, I'd write that using
the pre-defined constants:

Me.PartyID.Column(4) & vbNullString = vbNullString

Again, it won't matter on one line, but in a loop, it can make a big
difference.

Also, keep in mind that code that doesn't have an explicit loop,
could get called in what amounts to a loop, such as the case of a
function that you is called in each row of a query. In that case,
these little optimizations could very well make a significant
difference.
 
Back
Top