Sorting Problem

S

Secret Squirrel

I have on my main form some combo boxes that allow users to filter records on
my subform. The command button that fires this sort has this code behind it:

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "

If Me![txtRegion] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Region
Like """ & txtRegion & "*"""
End If

If Me![txtMarket] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Market Like
""" & txtMarket & "*"""
End If

If Me![txtState] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.State
Like """ & txtState & "*"""
End If

If Me![txtSource] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Source
Like """ & txtSource & "*"""
End If

If Me![txtSalesRep] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.SalesRep
Like """ & txtSalesRep & "*"""
End If


If Nz(DCount("*", "qrySearchCriteriaSub", right(sCriteria,
Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT DISTINCT [RECORDID],
[COMPANYNAME],[CITY],[STATE],[ZIPCODE],[PHONENO], [CONTACTNAME1], [WEBSITE],
[PRIORITYCODE], [SOURCE] from qrySearchCriteriaSub " & sCriteria

Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
Else

MsgBox "The search failed find any records" & vbCr & vbCr & _
"that matches your search criteria?", vbOKOnly + vbQuestion, "Search
Record"
txtSource = Null
txtSalesRep = Null
txtMarket = Null
txtRegion = Null
txtState = Null

End If


The problem I'm having is I can't sort the records by company name. In the
query I have the company name set to sort ascending but for some reason when
they fire the filter it won't sort by the company name. I'm assuming that the
problem must lie in this code because when I test the query out alone it
sorts fine. Can anyone maybe shed some light on why it won't carry the sort
from the query to the form?
 
S

Secret Squirrel

I tried that but then I get no results from the filter.

Dennis said:
Add ORDER BY [COMPANYNAME] to the end of your built sSQL string

Secret Squirrel said:
I have on my main form some combo boxes that allow users to filter records on
my subform. The command button that fires this sort has this code behind it:

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "

If Me![txtRegion] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Region
Like """ & txtRegion & "*"""
End If

If Me![txtMarket] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Market Like
""" & txtMarket & "*"""
End If

If Me![txtState] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.State
Like """ & txtState & "*"""
End If

If Me![txtSource] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Source
Like """ & txtSource & "*"""
End If

If Me![txtSalesRep] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.SalesRep
Like """ & txtSalesRep & "*"""
End If


If Nz(DCount("*", "qrySearchCriteriaSub", right(sCriteria,
Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT DISTINCT [RECORDID],
[COMPANYNAME],[CITY],[STATE],[ZIPCODE],[PHONENO], [CONTACTNAME1], [WEBSITE],
[PRIORITYCODE], [SOURCE] from qrySearchCriteriaSub " & sCriteria

Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
Else

MsgBox "The search failed find any records" & vbCr & vbCr & _
"that matches your search criteria?", vbOKOnly + vbQuestion, "Search
Record"
txtSource = Null
txtSalesRep = Null
txtMarket = Null
txtRegion = Null
txtState = Null

End If


The problem I'm having is I can't sort the records by company name. In the
query I have the company name set to sort ascending but for some reason when
they fire the filter it won't sort by the company name. I'm assuming that the
problem must lie in this code because when I test the query out alone it
sorts fine. Can anyone maybe shed some light on why it won't carry the sort
from the query to the form?
Click to expand...
Click to expand...
 
B

BruceM

What is WHERE 1=1? Also, is CompanyName a lookup field?

Secret Squirrel said:
I tried that but then I get no results from the filter.

Dennis said:
Add ORDER BY [COMPANYNAME] to the end of your built sSQL string

Secret Squirrel said:
I have on my main form some combo boxes that allow users to filter
records on
my subform. The command button that fires this sort has this code
behind it:

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "

If Me![txtRegion] <> "" Then
sCriteria = sCriteria & " AND
qrySearchCriteriaSub.Region
Like """ & txtRegion & "*"""
End If

If Me![txtMarket] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Market
Like
""" & txtMarket & "*"""
End If

If Me![txtState] <> "" Then
sCriteria = sCriteria & " AND
qrySearchCriteriaSub.State
Like """ & txtState & "*"""
End If

If Me![txtSource] <> "" Then
sCriteria = sCriteria & " AND
qrySearchCriteriaSub.Source
Like """ & txtSource & "*"""
End If

If Me![txtSalesRep] <> "" Then
sCriteria = sCriteria & " AND
qrySearchCriteriaSub.SalesRep
Like """ & txtSalesRep & "*"""
End If


If Nz(DCount("*", "qrySearchCriteriaSub", right(sCriteria,
Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT DISTINCT [RECORDID],
[COMPANYNAME],[CITY],[STATE],[ZIPCODE],[PHONENO], [CONTACTNAME1],
[WEBSITE],
[PRIORITYCODE], [SOURCE] from qrySearchCriteriaSub " & sCriteria

Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource
= sSql

Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
Else

MsgBox "The search failed find any records" & vbCr & vbCr & _
"that matches your search criteria?", vbOKOnly + vbQuestion,
"Search
Record"
txtSource = Null
txtSalesRep = Null
txtMarket = Null
txtRegion = Null
txtState = Null

End If


The problem I'm having is I can't sort the records by company name. In
the
query I have the company name set to sort ascending but for some reason
when
they fire the filter it won't sort by the company name. I'm assuming
that the
problem must lie in this code because when I test the query out alone
it
sorts fine. Can anyone maybe shed some light on why it won't carry the
sort
from the query to the form?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

concatenating problem 2
Sorting Records 1
Lookup value from another table ado. 3
Concurrency violation: Problem dynamically creating DataAdapter commands 2
help please..problem with set identity_insert off 1

Top