search/criteria code to order by????

[jomuir]

I have a form that has search fields in the header and the results in the details section, I have the details section populated via a query that I have created that is ordered via “Issued Date”. However when I view the data it is not ordered by “Issued Date”, but ordered by “Account Number”

Is their any code I can add to my search or my criteria coding that could make this data be ordered by tblAccount.[Issued Date] ?

I have the following search code:-

Private Sub btnSearch_Click()

' Update the record source
Form_frmMain.RecordSource = "SELECT tblCustomer.[Customer Name], tblAccount.[Issued Date], tblCustomer.[Customer Ref], tblCustomer.[Street Number], tblCustomer.[Tot Bal O/S], tblAccount.[Account Number] FROM tblAccount INNER JOIN tblCustomer ON tblAccount.[Customer Reference]=tblCustomer.[Customer Ref]" & BuildFilter
Form_frmMain.Caption = "Your Search Results"

End Sub


Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter

' Check for LIKE Customer Name
If Me.txtcustname > "" Then
varWhere = varWhere & "[Customer Name] LIKE ""*" & Me.txtcustname & "*"" AND "
End If

' Check for LIKE Customer Reference
If Me.txtCustRef > "" Then
varWhere = varWhere & "[Customer Reference] LIKE ""*" & Me.txtCustRef & "*"" AND "
End If

' Check for LIKE Account Number
If Me.txtAccNum > "" Then
varWhere = varWhere & "[Account Number] LIKE ""*" & Me.txtAccNum & "*"" AND "
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

 

[DCrake]

Simple Software Solutions

Hi

On th end of your BuildFilter string add ORDER BY [Date] (DESC

DESC if you want the last date first in your list.

Code Master

 

[jomuir]

Sorry, I am fairly new to the coding part of Access and as a result I cannot figure out where to add this code as I keep on receiving errors then I try to add it to the Buildfilter. As a result could you please be a bit more specific (sorry – I am trying!!) as to where the Orderby code should go, and also if I have it right i.e. should it start with AND or & and should it have “ ” or not……………..

I am really sorry, but I cannot find where to put that part of the code, I have tried quite a few places and just get errors ???

I am adding the following code:- OrderBy tblAccount.[Issued Date]

I have tired this with -

1. & "OrderBy tblAccount.[Issued Date]"
2. AND "OrderBy tblAccount.[Issued Date]"
3. OrderBy tblAccount.[Issued Date]

as well as to lots of different parts of the Buildfilter (the end part) but cannot get it to work

 

[Fear Naught]

Try

BuildFilter = varWhere & " Order By tblAccount.[Issued Date]"

What is important is the space between the opening quote mark and the word Order.