IIf Len Question

  • Thread starter Thread starter JIM
  • Start date Start date
J

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
 
IIf (Len([Invoices].[CustomerName])=40, (Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)), Left([Invoices].[CustomerName]), 39)
AS CustName
Click to expand...

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.
 
Thanks John

John W. Vinson said:
IIf (Len([Invoices].[CustomerName])=40, (Left([Invoices].[CustomerName]), 38
& Right ([Invoices].[CustomerName], 1)), Left([Invoices].[CustomerName]), 39)
AS CustName
Click to expand...

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.
Click to expand...
 
Back
Top