SQL bracketing error

[Matt Brown]

Can anyone see a problem with this sql syntax:

SQL = "SELECT [newcust.CUST_REF], [newcust.SUB], [newcust.ADDR1], [newcust.ADDR2], " & _
" [newcust.ADDR3], [newcust.ADDR4], [newcust.PC], [newcust.country], [newcust.REP], " & _
" [newcust.ISE_CODE], [custgen.MFTR_CAT] , [newcust.CUR_CODE], [newcust.NAME] " & _
" FROM [custgen] INNER JOIN [newcust] ON [custgen.CONS_AC_NO] = [newcust.CONS_AC_NO] " & _
" where customerNo = '" & Trim(Me.CustomerNo) & "';"

It returns an "Invalid bracketing of name [custgen.CONS_AC_NO]" error.

cheers

Matt

 

[Mile-O]

ALL the bracketing is invalid. Each object should be enclosed within brackets:

i.e.

SELECT [newcust].[CUST_REF], [newcust].[SUB] etc.

 

[Matt Brown]

Thanks.

Didn't release each object had to be enclosed in brackets.

Mucho gracious

Matt

 

[Mile-O]

They don't always need to be. Any fields that have spaces, use underscores, special characters, or reserved words should be enclosed within brackets. Also, if there's only one table, you only need to mention it in the FROM clause

 

[Matt Brown]

How would i write the record sets out:

Would they be:

cName = rs1![newcust].[Name]

or

cName = rs1!newcust.Name

thanks

Matt

 

[Mile-O]

As rs should already be defined as a table or query already that's not necessary.

The best is to use:

cName = rs.Fields("Name")

Forget about the ! (unless referencing from a query) forever...

 

[Matt Brown]

Thankyou for your time and wisdom on this.

This is now working for me.

thanks again

Matt