Hardcoding in Field Name in Query Design View

TiffBrassy

Registered User.
Local time
Yesterday, 23:59
Joined
Jan 22, 2014
Messages
10
Hi,

Simple question:

I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this

IDENTITY:"TEXT(ROW(A17),"-0")"

What am I doing wrong???
 
Go into SQL view, copy the whole query and paste it here. Someone might be able to help if they can see the whole picture.
 
I hope this helps: The issue is this part of SQL TEXT(ROW([A17]),"-0") AS [IDENTITY],

SELECT tbl_transaction_details_b.SHIPTOCUSTOMERNAME, tbl_transaction_details_b.SHIPTOADDR1, tbl_transaction_details_b.SHIPTOCITY, tbl_transaction_details_b.SHIPTOSTATE, tbl_transaction_details_b.SHIPTOPOSTALCD, tbl_transaction_details_b.MAKE_MODEL, tbl_transaction_details_b.MFG_SERIAL_NUMBER, tbl_transaction_details_b.INVNUMBER, "METER" AS PAYMENTTYPE, [BILLEDFROM] & " TO " & [BILLEDTO] AS PERIODCOVERED, tbl_transaction_details_b.METER_NAME_TYPE AS METERTYPE, tbl_transaction_details_b.BEGINMETERREAD, tbl_transaction_details_b.ENDMETERREAD, tbl_transaction_details_b.TOTALIMAGES, tbl_transaction_details_b.Rate, tbl_transaction_details_b.OVERAGECHARGES, tbl_transaction_details_b.INVOICE_LINE_TAX_AMOUNT, tbl_transaction_details_b.INVOICELINESUBTOTAL, tbl_transaction_details_b.PONUM, tbl_transaction_details_b.DESCRIPTION, TEXT(ROW([A17]),"-0") AS [IDENTITY], Null AS ConcatenateInvNo, tbl_transaction_details_b.BILLACCOUNTNUMBER, tbl_transaction_details_b.ATTNLINE, tbl_transaction_details_b.BILLTONAME, tbl_transaction_details_b.BILLTOADDR1, [BILLTOCITY] & " " & [BILLTOSTATE] & " " & [BILLTOPOSTALCODE] AS BILLTO, tbl_transaction_details_b.PAYMENTTERMS, tbl_transaction_details_b.CCCADDRESS_LINE1, tbl_transaction_details_b.CCCTOWNREGIONCODE, tbl_transaction_details_b.REMITTOADDR1, [REMITTOCITY] & " " & [REMITTOSTATE] & " " & [REMITTOPOSTALCD] AS REMITTO, tbl_transaction_details_b.CONSOLIDATEDINVNUMBER
FROM tbl_transaction_details_b
WHERE (((tbl_transaction_details_b.TRANSACTION_TYPE) Not Like "INV-INTRL LEASE RENT") AND ((tbl_transaction_details_b.BASECHARGES)=0));
 
Ah! TEXT and ROW are Excel functions, I've never seen them in Access.
 
Yes! They are, but I need that actual wording hardcoded in the column. I eventually export it to an Excel file and need that formula in order to complete the process in Excel.
 
OK, gotcha now!

Try this, with single quotes around TEXT(ROW([A17]),"-0")

SELECT tbl_transaction_details_b.SHIPTOCUSTOMERNAME, tbl_transaction_details_b.SHIPTOADDR1, tbl_transaction_details_b.SHIPTOCITY, tbl_transaction_details_b.SHIPTOSTATE, tbl_transaction_details_b.SHIPTOPOSTALCD, tbl_transaction_details_b.MAKE_MODEL, tbl_transaction_details_b.MFG_SERIAL_NUMBER, tbl_transaction_details_b.INVNUMBER, "METER" AS PAYMENTTYPE, [BILLEDFROM] & " TO " & [BILLEDTO] AS PERIODCOVERED, tbl_transaction_details_b.METER_NAME_TYPE AS METERTYPE, tbl_transaction_details_b.BEGINMETERREAD, tbl_transaction_details_b.ENDMETERREAD, tbl_transaction_details_b.TOTALIMAGES, tbl_transaction_details_b.Rate, tbl_transaction_details_b.OVERAGECHARGES, tbl_transaction_details_b.INVOICE_LINE_TAX_AMOUNT, tbl_transaction_details_b.INVOICELINESUBTOTAL, tbl_transaction_details_b.PONUM, tbl_transaction_details_b.DESCRIPTION, 'TEXT(ROW([A17]),"-0")' AS [IDENTITY], Null AS ConcatenateInvNo, tbl_transaction_details_b.BILLACCOUNTNUMBER, tbl_transaction_details_b.ATTNLINE, tbl_transaction_details_b.BILLTONAME, tbl_transaction_details_b.BILLTOADDR1, [BILLTOCITY] & " " & [BILLTOSTATE] & " " & [BILLTOPOSTALCODE] AS BILLTO, tbl_transaction_details_b.PAYMENTTERMS, tbl_transaction_details_b.CCCADDRESS_LINE1, tbl_transaction_details_b.CCCTOWNREGIONCODE, tbl_transaction_details_b.REMITTOADDR1, [REMITTOCITY] & " " & [REMITTOSTATE] & " " & [REMITTOPOSTALCD] AS REMITTO, tbl_transaction_details_b.CONSOLIDATEDINVNUMBER
FROM tbl_transaction_details_b
WHERE (((tbl_transaction_details_b.TRANSACTION_TYPE) Not Like "INV-INTRL LEASE RENT") AND ((tbl_transaction_details_b.BASECHARGES)=0));
 
YAY! Thank you! I knew it was a simple answer. I truly appreciate you sticking with me until I got this thing figered out.

You are awesome!
 
You're welcome. It's often difficult trying to explain / understand detailed technical problems first time when all you've got is a screen & a keyboard and you have no idea of how savvy the other person is!!!
 

Users who are viewing this thread

Back
Top Bottom