"Argument not optional" when using Iif

[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

 

[Guest]

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)

 

[Guest]

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

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

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

 

[Guest]

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

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

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

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

 

[John Nurick]

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