Invalid procedure call

[wingforward]

I'm trying to parse an e-mail address in query (to filter out duplicate domain names for a one time debugging routine). My code is Mid(Email.Address, InStr(Email.Address, "@"), 100)

I'm getting an "Invalid procedure call" error. Why is that?

The InStr() part works on its own and the Mid() works if I sub in an arbitrary number.

 

[MSAccessRookie]

I'm trying to parse an e-mail address in query (to filter out duplicate domain names for a one time debugging routine). My code is Mid(Email.Address, InStr(Email.Address, "@"), 100)

I'm getting an "Invalid procedure call" error. Why is that?

The InStr() part works on its own and the Mid() works if I sub in an arbitrary number.

Your Query (modified for my Table/Column names) works for me with one exception. I get a failure whenever the email address is Null. Is this a possibility in your case? If it is try the following:

IIf(Email.Address Is Null, "", Mid(Email.Address, InStr(Email.Address, "@"), 100))

 

[jdraw]

Your Query (modified for my Table/Column names) works for me with one exception. I get a failure whenever the email address is Null. Is this a possibility in your case? If it is try the following:

IIf(Email.Address Is Null, "", Mid(Email.Address, InStr(Email.Address, "@"), 100))

I think the Is Null code should be

IIf([COLOR="Green"]IsNull([/COLOR]Email.Address[COLOR="Green"])[/COLOR], "", Mid(Email.Address, InStr(Email.Address, "@"), 100)

 

[MSAccessRookie]

I think the Is Null code should be

IIf([COLOR=green]IsNull([/COLOR]Email.Address[COLOR=green])[/COLOR], "", Mid(Email.Address, InStr(Email.Address, "@"), 100)

You are correct to a point, but I would like to point out that IsNull() is a VB Function, while IS NULL is an SQL Function. While either method will give the same results in this instance, I was presenting an SQL only solution.