Select Case Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hope this is in the correct discussion group. I have a field in a table where
I have set a custom number format; if the value is an integer it will appear
normally and if the value is Null the words "no record" will appear. Works
great. I want to write a small piece of VBA to look at the value in this
field and calculate a second value based on it but am having a problem
because Select Case is not recognising or responding to the "no record"
value. If I pass an integer value into the function the code runs as expected
but if I pass in "no record" the code still runs as if "no record" were an
integer value. I have not declared SalBatch as any particular data type in my
function because I assume it defaults to Variant and will allow both integer
and "no record" values to be recognised. Any help appreciated on how best to
deal with this.

TIA

Select Case SalBatch
Case Is>=1700
more code here
Case Is<1700
more code here
Case Else
more code here
End Select
 
It sounds like you have Number type field, and you used the Format property
to display "no record" for Null.

If so, the field does not contain the value "no record" but the value Null.
Passing the Null value should trigger the Else in your Select Case, since
neither of the other two conditions are met by Null.

Select Case SalBatch
Case Is>=1700
'more code here
Case Is<1700
'more code here
Case Else
MsgBox "This case should fire for Null"
End Select
 
Problem solved! Thanks.

Allen Browne said:
It sounds like you have Number type field, and you used the Format property
to display "no record" for Null.

If so, the field does not contain the value "no record" but the value Null.
Passing the Null value should trigger the Else in your Select Case, since
neither of the other two conditions are met by Null.

Select Case SalBatch
Case Is>=1700
'more code here
Case Is<1700
'more code here
Case Else
MsgBox "This case should fire for Null"
End Select
Click to expand...
 
I have set a custom number format; if the value is an integer it will
appear normally and if the value is Null the words "no record" will
appear. Works great.
Click to expand...

It "works great" in the sense that the format property is passed to any
text box controls to which the field is bound, and (God forbid) columns in
table datasheets. It's only window dressing, though, and the value
underneath is still Null.

Strike up another one against setting Formats in table designs, and for
putting them where they belong: in forms and reports <sigh>...

All the best


Tim F
 
Back
Top