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!

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

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


Thanks!

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.

Hi,

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

Cheers

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

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.

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!

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.
:o

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?

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!

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

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