Quotation Mark Question

[jim]

I'm trying to format a CSV spec from a query and so want quotes on some
fields and other without quotes -because of embedded commas in some fields.
Can someone help me with the proper quotes? Here's what I have:

CustID: """" & Left(tblInvoicesBrad.CustomerName,19) &"""
IIf(Len(tblInvoicesBrad.CustomerName)=40,"""Right(tblInvoicesBrad.CustomerName,1)"""," ")

I need the first left 19 characters of CustomerName concatenated to the
Rightmost character if CustomerName is 40 positions long, if CustomerName
isn't 40 positions I want to concatenate a space. I can't seem to get the
quotes right.
Thanks

 

[John Spencer]

CustID: Chr(34) & Left(CustomerName,19)&
IIF(Len(CustomerName)>39,Right(CustomerName,1)," ") & Chr(34)


Or
CustID: """" & Left(CustomerName,19)&
IIF(Len(CustomerName)>39,Right(CustomerName,1)," ") & """"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

Jim,

Unless you actually want to display the quotes in the field your query
returns, I don't think you need the quotes stuff at all. But what if the
length of CustomerName is > 40 characters? Try

CustID = left(tblInvoicesBrad.CustomerName, 19) _
& IIF(len(tblInvoicesBrad.Customer) = 40, _
Right(tblInvoicesBrad.CustomerName, 1), "")

But I think you have a bigger problem. The name of your table
"tblInvoicesBrad" implies that you have multiple tables with the same
structure and different names (tblInvoicesJim, ....). If that is the case,
your data structure needs serious revision. Instead of having multiple
invoice tables, one for each user, you should have a single invoice table
(tblInvoices) and have a column in that table to record "Brad" or "Jim" or
whoever.

 

[jim]

Thanks John, it's working great -haven't been able to locate this in order to
respond.