Using "iif" and "between" in vba error

[neelsfer]

I would like to use the following code in a form with vba, to populate a textbox, but get a compile error: syntax
It seems not to like the "between". This code works 100% in the control source of the textbox.
Any suggestions please? I use Access 2007.

me.title=IIf(Mid([idno],7,1) Between 0 And 4,"Ms","Mr")

[Stewart Ross]

In VBA you do not have access to statements which work in the query engine. Between is one of them. You also should use a standard IF statement and not an in-line IF. Please note also that Mid is a string function, so the comparison is to the text values '0' and '4'.

Dim Ch as String
Ch = Mid([idno],7,1)
If(Ch >= '0' And Ch <= '4')
  me.title = "Ms"
Else
  me.title = "Mr"
End If

You may need to refer explicitly to the control containing the [idno] value to make this work - as I don't know which form it is on I can't show the syntax here.
-Stewart

[zmbd]

personally replace the if then with select

Select Ch
   case 0 to 4
      me.title = "Ms"
   case >4
      me.title = "Mr"
   case else
      me.title "Err"
end select
 
 

otherwise the same comments as Mr. Ross.

[neelsfer]

thx Stewart and zmbd its appreciated

[zmbd]

ahh.. now I wouldn't say I have the best answer as it's the equivalent to Mr. Ross'
It's a prefernce I have and I can see in your code where you might include "Mrs." at some point or perhaps "Mme." etc; thus, the code might be easier to alter in such cases.

I'd also start looking at a recordset/table solution at that point. (^_^)