New Post on Syntax Help For Ken

[cvegas]

This is the code that Ken gave me previously and it works great. Problem is
I have added a field, from another table, and don't know how to address the
INNER Join portion of the syntax

'*******This code works OK except I did not put in the UserFFText1 in this
SQL Statement
strFilter = "SELECT tblAccounts.AccountNumber, " & _
"tblAccounts.AccountName, tblAccounts.City, tblAccounts.State," & _
"tblAccounts.PostalCode , tblAccounts.AccountType, " & _
"tblAccounts.PrimaryPhoneNumberFormatted, " & _
"tblAccounts.PrimaryFaxNumberFormatted, " & _
"tblAccounts.PrimaryEmailAddress, tblAccounts.URL1 " & _
"FROM tblAccounts WHERE AccountName Like '" & _
strFilterName & "%" & "' AND AccountType<>'Prospect' " & _
"AND AccountType<>'Customer'"
'**** END OF WORKING CODE ********

I am trying to get this code to exactly the same as above but with these
fields.

I understand that the line continuation is probably wrong here but this is
the way I have it in my code window in Access.

strFilter = "SELECT tblAccounts.AccountNumber, tblAccounts.AccountName,
tblAccounts.City, tblAccounts.State, " & _
"tblAccounts.PostalCode, tblAccounts.AccountType,
tblAccounts.PrimaryPhoneNumber, " & _
"tblAccounts.PrimaryFaxNumber, tblCustomAccount.UserFFText1 " & _
"FROM tblAccounts INNER JOIN tblCustomAccount ON
tblAccounts.AccountNumber = tblCustomAccount.AccountNumber, & _
WHERE AccountName Like '" & _
strFilterName & "%" & "' AND AccountType<>'Prospect' " & _
"AND AccountType<>'Customer'"

Thanks again for any assistance you may be able to give.

 

[Ken Snell [MVP]]

This line
"FROM tblAccounts INNER JOIN tblCustomAccount ON
tblAccounts.AccountNumber = tblCustomAccount.AccountNumber, & _

needs to be this line instead:
"FROM tblAccounts INNER JOIN tblCustomAccount ON
tblAccounts.AccountNumber = tblCustomAccount.AccountNumber, " & _

 

[cvegas]

Ken I am getting an incorrect syntax new keyword WHERE wth the following but
it complies OK ?

strFilter = "SELECT tblAccounts.AccountNumber, " & _
"tblAccounts.AccountName, tblAccounts.City, tblAccounts.State, " & _
"tblAccounts.PostalCode, tblAccounts.AccountType, " & _
"tblAccounts.PrimaryPhoneNumber, tblAccounts.PrimaryFaxNumber,
tblCustomAccount.UserFFText1 " & _
"FROM tblAccounts INNER JOIN tblCustomAccount ON " & _
"tblAccounts.AccountNumber = tblCustomAccount.AccountNumber," & _
"WHERE AccountName Like '" & _
strFilterName & "%" & "' AND AccountType<>'Prospect' " & _
"AND AccountType<>'Customer'"

Thanks

 

[Ken Snell [MVP]]

In the code line above the one that contains the WHERE word, there is a
comma at the end that should be replaced with a space:

strFilter = "SELECT tblAccounts.AccountNumber, " & _
"tblAccounts.AccountName, tblAccounts.City, tblAccounts.State, " & _
"tblAccounts.PostalCode, tblAccounts.AccountType, " & _
"tblAccounts.PrimaryPhoneNumber, tblAccounts.PrimaryFaxNumber,
tblCustomAccount.UserFFText1 " & _
"FROM tblAccounts INNER JOIN tblCustomAccount ON " & _
"tblAccounts.AccountNumber = tblCustomAccount.AccountNumber " & _
"WHERE AccountName Like '" & _
strFilterName & "%" & "' AND AccountType<>'Prospect' " & _
"AND AccountType<>'Customer'"