Hi,

I have successfully employed the following syntax in MS Access but cannot
get it to work running a SQL query from within Visual Basic.

SELECT GLAFS.ACCTID, GLAMF.ACCTDESC, Left([GLAMF]![ACSEGVAL01],4) &
Left([GLAMF]![ACSEGVAL03],3) AS AcctGroup,
GLAFS.OPENBAL+GLAFS.NETPERD1+GLAFS.NETPERD2+GLAFS. NETPERD3+GLAFS.NETPERD4+GLAFS.NETPERD5+GLAFS.NETPE RD6 AS Expr1, GLAFS.NETPERD6
FROM GLAFS RIGHT JOIN GLAMF ON GLAFS.ACCTID = GLAMF.ACCTID
WHERE (((GLAFS.FSCSDSG)='1') AND ((GLAFS.FSCSYR)='2006') AND
((GLAMF.ACCTTYPE)='I') AND ((GLAFS.CURNTYPE)='F'))
ORDER BY GLAFS.ACCTID;

The problem seems to be with the AcctGroup field and the "&" character where
I am trying to concatenate sections of 2 text fields. There are some
variables in the VB statement which looks like this:

strSqlGLTrialBalance = "SELECT " _
& "GLAFS.ACCTID As AccountID, left(GLAFS.ACSEGVAL01),4) &
Left((GLAMF.ACSEGVAL03),3) AS AcctGroup, GLAMF.ACCTDESC As Description, " _
& strSqlPeriodBalance & strSqlMovement & "FROM GLAFS " _
& "RIGHT JOIN GLAMF ON GLAFS.ACCTID = GLAMF.ACCTID " _
& "WHERE GLAFS.FSCSDSG='" & strActualBudget & "' AND GLAFS.FSCSYR='" &
strYear & "' " _
& "AND GLAMF.ACCTTYPE" & strAccountTypeCondition & " AND
GLAFS.CURNTYPE='F' " _
& strSqlWHEREClause _
& "ORDER BY GLAFS.ACCTID"


If anyone can assist, I would be really grateful!