Argument not optional error in VB module

[Gilz]

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

[Bob Quintal]

Re: Argument not optional error in VB module

"Gilz" <gdonald@tlpsys tems.co.uk> wrote in
news:1145876641 .524344.87540@i 40g2000cwc.goog legroups.com:
[color=blue]
> 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.
>[/color]
You didn't. :-) You think you did, but you copied it wrong.
[color=blue]
> 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
>[/color]
PE_Initials = (Left([PE_First_Name], 1) _
& IIf(InStr(1, [PE_First_Name], " ") <> 0, _
Mid([PE_First_Name], _

above is good. Below is nonsense to Access

InStr(1, [PE_First_Name], " "), 2)))

--
Bob Quintal

PA is y I've altered my email address.

[Gilz]

Re: Argument not optional error in VB module

Hi

Thanks for that Bob but i have double checked the query and that is the
correct string and it works perfectly to give me the second initial is
the field person name has two words in the query.

I am running access 2000 but the code definately works in my update
query.

Gillian