Dealing with HASH in Sage MMS Field Name

  • Thread starter Thread starter studio_two
  • Start date Start date
S

studio_two

Hello,

I'm having difficulty referencing a database field name
"ADDRESS_LINE#1" within Sage MMS.

I have a select statement in VBA that gives a syntax error each time i
include the "ADDRESS_LINE#1" field. I get "Syntax Error in Date in
Query Expression"

As follows:

strSQLSelect = "SELECT ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NUMBER, "
_
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NAME, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#1, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#2, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#3, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#4, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.POST_CODE, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.TELEPHONE_NUMBER, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.FAX_NUMBER, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.CONTACT, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ANALYSIS_1 " _
& "INTO temp_slcu " _
& "FROM ACCOUNTING_SYSTEM_SALES_LEDGER; "

Any help would be greatly appreciated.

TIA,
Stephen
 
Hello,

I'm having difficulty referencing a database field name
"ADDRESS_LINE#1" within Sage MMS.

I have a select statement in VBA that gives a syntax error each time i
include the "ADDRESS_LINE#1" field. I get "Syntax Error in Date in
Query Expression"

As follows:

strSQLSelect = "SELECT ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NUMBER, "
_
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NAME, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#1, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#2, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#3, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#4, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.POST_CODE, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.TELEPHONE_NUMBER, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.FAX_NUMBER, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.CONTACT, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ANALYSIS_1 " _
& "INTO temp_slcu " _
& "FROM ACCOUNTING_SYSTEM_SALES_LEDGER; "

Any help would be greatly appreciated.
Click to expand...

The # character is a Date/Time delimiter. If you include it (or other
nonstandard characters such as blank, hyphen, slash, etc.) in
fieldnames, you must delimit the entire fieldname with square
brackets. Try

strSQLSelect = "SELECT ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NUMBER,
"
_
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ACCOUNT_NAME, " _
& "[ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#1], " _
& "[ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#2], " _
& "[ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#3], " _
& "[ACCOUNTING_SYSTEM_SALES_LEDGER.ADDRESS_LINE#4], " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.POST_CODE, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.TELEPHONE_NUMBER, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.FAX_NUMBER, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.CONTACT, " _
& "ACCOUNTING_SYSTEM_SALES_LEDGER.ANALYSIS_1 " _
& "INTO temp_slcu " _
& "FROM ACCOUNTING_SYSTEM_SALES_LEDGER; "

though it never hurts to bracket all fieldnames.

John W. Vinson[MVP]
 
John's given you a suggestion to try.

And, for such a simple query...I would not bother repeating the table
name...

You can use:
strSQLSelect = "ACCOUNT_NUMBER, ACCOUNT_NAME, " _
& "[ADDRESS_LINE#1]," _
& " [ADDRESS_LINE#2], " _
& "[ADDRESS_LINE#3], " _
& "[ADDRESS_LINE#4], " _
& "POST_CODE, TELEPHONE_NUMBER, FAX_NUMBER, " _
& "CONTACT, ANALYSIS_1 "
& "INTO temp_slcu " _
& "FROM ACCOUNTING_SYSTEM_SALES_LEDGER; "
 
Thanks a lot guys. The square brackets did the trick perfectly.

BTW, the Table Names are there because I just copied the sql straight from
the scratchpad. I will remove those now and tidy everything up.


Thanks again for you help,
Stephen
 
Back
Top