Error in iif

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

Guest

I have a command which opens a word document, and I would like to insert an
instruction to first check that necessary fields have been completed and give
an error message if not.

I have tried
Dim CheckPostcode
CheckPostcode = IIf(ROPostcode = " ", "Postcode not entered")

This does not work. Where am I going wrong?

Thanks
Dudley
 
Well, it is probably because your post code field does not contain a single
space. The field is probably null.

Try the Nz function to replace a null value...

CheckPostcode = Nz([ROPostcode],"Postcode not entered")
 
Knowing what error message you get would help...

Fields without values are seldom equal to " ". It's far more common for
them to be Null, or equal to "". To check for both at the same time, you can
use Len(ROPostcode & "") = 0.

Also, the IIf statement has 3 parts: the boolean expression, what to do if
the expression is True, and what to do if the expression is False.

Try:

CheckPostcode = IIf(Len(ROPostcode & "") = 0, "Postcode not entered", "")

What is ROPostcode anyhow? If it's a control on your form, you should prefix
it with the Me. keyword:

CheckPostcode = IIf(Len(Me.ROPostcode & "") = 0, "Postcode not entered", "")
 
In case someone inadvertantly put a space in a control or field, I usually
add this:
Len(Trim(ROPostcode & "")) = 0
 
Thanks for all the helpful replies. Len and Trim and Nz stop error messages,
but I do not get the "postcode not entered" message box. Is there something
else I need to do?

I have tried another approach:

If Me.ROPostcode = "X" Then
MsgBox "Postcode not entered"
Exit Sub
End If

This works fine and gives the message box for specified text such as an "X",
but Nz and Len and is null do not work and I cannot find any way of searching
on a null field.

Dudley
 
Give this a try:
If Len(Trim(Nz(Me.ROPostcode,""))) = 0 Then
MsgBox "Postcode not entered"
Exit Sub
End If

I know it works, I use it all the time
 
That works fine. Thanks very much.
Dudley

Klatuu said:
Give this a try:
If Len(Trim(Nz(Me.ROPostcode,""))) = 0 Then
MsgBox "Postcode not entered"
Exit Sub
End If

I know it works, I use it all the time
Click to expand...
 
Back
Top