IIf Len Question

[JIM]

I'm trying to conctenate a field in a query for an export file. If length of
CustomerName is 40 I want the leftmost 38 positions and the 40th position in
a concatenated field CustName. Otherwise, if CustomerName is less that 40
positions I want CustName to be the leftmost 39 positions. My SQL is:

IIf (Len([Invoices].[CustomerName])=40, (Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)), Left([Invoices].[CustomerName]), 39)
AS CustName

I keep getting errors and can't get the right syntax. TIA

 

[John W. Vinson]

IIf (Len([Invoices].[CustomerName])=40, (Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)), Left([Invoices].[CustomerName]), 39)
AS CustName

Looks like a parentheis nesting problem. Let's parse this out...

IIf (Len([Invoices].[CustomerName])=40,
(Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)),
Left([Invoices].[CustomerName]), 39)
AS CustName

OK, an extra left paren before the first Left() function call, and no closing
paren. And... lots of other such, for instance you're closing the parentheses
in your Left function calls BEFORE the 38 or 39, the required second argument.

Try

IIf(Len([Invoices].[CustomerName])=40,
Left([Invoices].[CustomerName], 38) & Right([Invoices].[CustomerName], 1),
Left([Invoices].[CustomerName], 39))
AS CustName

all on one line.

 

[JIM]

Thanks John

IIf (Len([Invoices].[CustomerName])=40, (Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)), Left([Invoices].[CustomerName]), 39)
AS CustName

Looks like a parentheis nesting problem. Let's parse this out...

IIf (Len([Invoices].[CustomerName])=40,
(Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)),
Left([Invoices].[CustomerName]), 39)
AS CustName

OK, an extra left paren before the first Left() function call, and no closing
paren. And... lots of other such, for instance you're closing the parentheses
in your Left function calls BEFORE the 38 or 39, the required second argument.

Try

IIf(Len([Invoices].[CustomerName])=40,
Left([Invoices].[CustomerName], 38) & Right([Invoices].[CustomerName], 1),
Left([Invoices].[CustomerName], 39))
AS CustName

all on one line.