Syntax Help

[Guest]

This is the syntax I use to display data in my grid control when I load my
form. It work properly

SELECT tblAccounts.AccountNumber, tblAccounts.AccountName, tblAccounts.City,
tblAccounts.State, tblAccounts.PostalCode, tblAccounts.AccountType,
tblAccounts.PrimaryPhoneNumberFormatted,
tblAccounts.PrimaryFaxNumberFormatted, tblAccounts.PrimaryEmailAddress,
tblAccounts.URL1
FROM tblAccounts
WHERE (((tblAccounts.AccountType)<>'Prospect' And
(tblAccounts.AccountType)<>'Customer'))
ORDER BY tblAccounts.AccountName;

This is the syntax I use to filter my grid based on what is typed into
txtAccountName
It works properly except that on each keystorke it dispays records from
tblAccounts that have "Prospect" and "Customer" in the AccountType.

Private Sub txtAccountName_KeyUp(KeyCode As Integer, Shift As Integer)
Dim strFilter As String
Dim strFilterName
strFilterName = Me.txtAccountName.Text
strFilter = "SELECT * FROM tblAccounts WHERE AccountName Like '" &
strFilterName & "%" & "'"

I know that it is selecting all records in tblAccounts because of the *
What I need help with is putting the SELECT statement together so that it
only pulls the records that meet the <>Prospect AND <>"Customer" criteria

'Remainder of code that filters the grid
Forms!frmVendorlist.grdVendorList.DBActive = False
Forms!frmVendorlist.grdVendorList.DBRecordSource = strFilter
Forms!frmVendorlist.grdVendorList.DBActive = True
End Sub

Thanks in advance for any help with putting this together.

 

[Ken Snell [MVP]]

You should be able to just concatenate the WHERE clause into the select
string that you're already building:

strFilter = "SELECT * FROM tblAccounts WHERE AccountName Like '" & _
strFilterName & "%" & "' AND AccountType<>'Prospect' " & _
"AND AccountType<>'Customer'"

 

[Guest]

It appears that if I use the * that my grid does not display correctly so I
must specify the fields I want. I get a complie error at the FROM line when
I use the following syntax.

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'"

Thanks for your previous reply.

 

[Ken Snell [MVP]]

What I posted was meant to be directly copied/pasted into your code. The _
characters at the end of each line are continuation characters to tell the
VB editor that the code is continuing onto another line.

What you've posted here is not correct, as you've moved the _ characters
inside the text string.

Paste what I posted into your code and try it.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Of course, just substitute your field names for the * that I'd used from
your first post:

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'"

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

YDM

Thanks so much for this help.

Of course, just substitute your field names for the * that I'd used from
your first post:

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'"