How to do an IF statement with a cell with no data in it 2

[Ogart]

Greetings:

I can't quite get this syntax right.
A is a form that I'm entering data in. The field "PipeSize" won't have data in it until the user puts in data (from a pull down if that matters). If the cell is empty, that's a problem for the calculation downstream, so what I need to do first is see if that cell is empty. If it is, I'll skip the calculation, basically.

I thought I got this to work, but what's turning out to be the problem is my code isn't skipping the calculation the way it should becuase it isn't recognizing the cell of data is empty, the way it should. Wierd.

Here's the piece of code I wrote to figure out what syntax will work. When the cell has a value in it, life is good. When the cell is emtpy, (it's text), I get the final debug message ("apparently none of these apply&quot.

Oh, one more thing, the first thing I get is "Null" This is from the first line of code below.

Debug.Print A!PipeSize
If A!PipeSize = Null Then
Debug.Print "Pipe size is null"
ElseIf A!PipeSize = "" Then
Debug.Print "Pipe size is double quote"
ElseIf A!PipeSize = 0 Then
Debug.Print "PipeSize is zero"
Else
Debug.Print "apparently, none are the case"
End If

After some cursing, more than a little RTFMing, and finally a shrugged shoulder, I bow to the collective greatness that is "outside help".

-NPSHr

-->Just because you can, doesn't mean you should.

 

[Ogart]

NEVERMIND

This is probably posted int eh wrong place. I'll post it in the Access Module forum. Appologies. I'm tired.

-->Just because you can, doesn't mean you should.

 

[VBAva]

I only have a few months of experience with VBA but im pretty sure if you want to check what is in a text box you have to use
TextBox1.Text or TextBox1.Value.

so if you want to check if the box is empty then you should try something like this

If TextBox1.Text = "" Then
'Whatever
End If

 

[xlbo]

NPSHr - I think the reason that "none of these apply" is that you are being lazy - not being nasty but you are using the default property of the object (which is VALUE) instead of explicitly referencing the TEXT
try:
ElseIf A!PipeSize.text = "" Then

you may get some joy

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[TonyJollans]

Hi NPSHr,

The problem is the line ..

If A!PipeSize = Null Then

You cannot check for nulls this way; the result is always False. Use, instead, ..

If IsNull(A!PipeSize) Then

Enjoy,
Tony

 

[Sylv4n]

TonyJollans has the right answer but I prefer to use:

if LEN(A!PipeSize) = 0 then

simply becouse it works on a null value and also no value where it = ""

 

[TonyJollans]

Hi Sylv4n,

Thanks for the confirmation (and the star if it was you), but ...

... I beg to differ. If the field is Null then using LEN will give an "Invalid Use of Null" error. But you could use:

If Len(Nz(A!pipesize,"")) = 0

.. to catch both cases.

Enjoy,
Tony

 

[Sylv4n]

true,
I was working off memeory and I forgot the Nz command

 

[Ogart]

SWEET.

Next time someone says peer review of code isn't effective, tell 'em I said, "Piffle".

-Chris

-->Just because you can, doesn't mean you should.