"Argument not optional" when using Iif

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

Guest

Hi

I have an after update event on a field on my people form.


When i try to debug the code i get an "Argument not optional" error in
relation to the IIf statement.


I used the same code in an update query and it worked perfectly.


PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "), 2)))


Can anyone tell me why this doesn't work in the code module.


Thanks


Gillian
 
I think this will do it

PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "))),2)
 
Hi

Thanks for your reply when i try that i get an error saying expected close
bracket and highlights the last comma in the string.

Gillian

Klatuu said:
I think this will do it

PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "))),2)

gdonald20 said:
Hi

I have an after update event on a field on my people form.


When i try to debug the code i get an "Argument not optional" error in
relation to the IIf statement.


I used the same code in an update query and it worked perfectly.


PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "), 2)))


Can anyone tell me why this doesn't work in the code module.


Thanks


Gillian
Click to expand...
Click to expand...
 
I see no missing brackets in the code you originaly posted. Check it against
what is actually in your query now.

gdonald20 said:
Hi

Thanks for your reply when i try that i get an error saying expected close
bracket and highlights the last comma in the string.

Gillian

Klatuu said:
I think this will do it

PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "))),2)

gdonald20 said:
Hi

I have an after update event on a field on my people form.


When i try to debug the code i get an "Argument not optional" error in
relation to the IIf statement.


I used the same code in an update query and it worked perfectly.


PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "), 2)))


Can anyone tell me why this doesn't work in the code module.


Thanks


Gillian
Click to expand...
Click to expand...
Click to expand...
 
Hi Gillian,

Your expression is missing the third argument of the IIf(), and this is
what's making it fail in VBA even though it works in a query.

Some functions behave differently in VBA code and in queries, and IIf()
is one of them. In VBA, all three arguments of IIf() must be present and
correct (will evaluate without error), while IIf() called from a query
takes a more relaxed attitude. Functions called by VBA Eval() seem to
work like functions called from queries. Try this in the Immediate pane:

? IIf(1=1, 99)
[Compile error: argument not optional]

? Eval("IIf(1=1, 99)")
99

? IIf(1=0, 99/0, 88)
[Runtime error: Division by zero]

? Eval("IIf(1=0, 99/0, 88)")
88



Hi

I have an after update event on a field on my people form.


When i try to debug the code i get an "Argument not optional" error in
relation to the IIf statement.


I used the same code in an update query and it worked perfectly.


PE_Initials = (Left([PE_First_Name], 1) & IIf(InStr(1, [PE_First_Name],
" ") <> 0, Mid([PE_First_Name], InStr(1, [PE_First_Name], " "), 2)))


Can anyone tell me why this doesn't work in the code module.


Thanks


Gillian
Click to expand...
 
Back
Top