Change IF Statement

[Matizo]

Hi,

Could someone please add to the IF Statement below one more field: BusinessName. Besically, if the Forename and Surname are NULL the BusinessName show appear and vice versa.

Client Name: IIf(IsNull([Surname]),IIf(IsNull([Forename]),[Forename]),IIf(IsNull([Forename]),[Surname],[Forename] & " " & [Surname]))

Thank you guys!

 

[neileg]

How about

Client Name: IIf(IsNull([Surname]&[Forename]),[BusinessName]), [Forename] & " " & [Surname]))

 

[Matizo]

I get an error when I try to input this expression.
Error: "The expression you entered contains invalid syntax".


Thanks!

 

[wilpeter]

Looks like you didn't get a follow-up answer. Try this:
ClientName: IIf(IsNull([Surname]),IIf(IsNull([Forename]),[BusinessName],[Forename],IIf(IsNull([Forename]),[Surname],[Forename]&" "&[Surname]))
Sorry for the delay.

 

[Matizo]

Hi,

Thanks for the reply, but I'm still getting an error "wrong number of arguments".

Cheers

 

[namliam]

Suspect neil was a bit to quick on the copy paste...
Two brackets to many...
Client Name: IIf(IsNull([Surname]&[Forename]),[BusinessName], [Forename] & " " & [Surname])

 

[wilpeter]

My apologies also. I left out the closing parenthesis before the second ISNULL or it would have worked. The only drawback with the shorter version, namliam, is that if there is only a surname, there'll be a blank space before it. Otherwise, it serves well.

 

[Matizo]

Thanks guys, it works now.

One more question, why is my delete query not working if I use the SQL below?

DELETE tblAllocations.*, ([tblAllocations_1].[EmployeeNo]=[tblAllocations].[EmployeeNo])
FROM tblAllocations, tblAllocations AS tblAllocations_1
WHERE (((([tblAllocations_1].[EmployeeNo]=[tblAllocations].[EmployeeNo]))=True));

THANKS!

 

[neileg]

Suspect neil was a bit to quick on the copy paste...
Two brackets to many...
Client Name: IIf(IsNull([Surname]&[Forename]),[BusinessName], [Forename] & " " & [Surname])

Oops, too quick as you say namliam.

 

[neileg]

Thanks guys, it works now.

One more question, why is my delete query not working if I use the SQL below?

DELETE tblAllocations.*, ([tblAllocations_1].[EmployeeNo]=[tblAllocations].[EmployeeNo])
FROM tblAllocations, tblAllocations AS tblAllocations_1
WHERE (((([tblAllocations_1].[EmployeeNo]=[tblAllocations].[EmployeeNo]))=True));

THANKS!

Doesn't make sense to me. What are you trying to do?

 

[Matizo]

Basically I'm trying to delete double allocations. for example employee A cannot be allocated twice to the same job. Hence, I don't know how to prevent double allocations I'm trying to run the query to delete them when the occur...


Cheers!

 

[namliam]

Lets start at the base, How would you find duplicates?

 

[Matizo]

I think it's solved
http://www.access-programmers.co.uk/forums/showthread.php?t=185438