Re: Selecting records matching user input

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 03/19/04 

Date: Thu, 18 Mar 2004 20:33:42 -0500

Report order is controlled by the Sorting and Grouping dialog of the report.

What I meant by SOMETIMES the sort order of the query works is just that. This
is not a bug, it is a happy coincidence that the records get returned in the
order you want and the report accepts that. BY DESIGN, the report uses the
order set using the Sorting and Grouping dialog.

ctdak wrote:
>
> Thanks for your answer. I should have thought of that. I have removed everything from Grouping and Sorting (I didn't need the grouping anyway) and now ORDER BY in my SELECT query is working as it should.
>
> Can you elaborate on SOMETIMES this works. Any specific circumstances? Does this mean that this is one area of Access that's a bit flakey?
>
> ctdak
>
> ----- John Spencer (MVP) wrote: -----
>
> Sorting in REPORTS is controlled by the report's Sorting and Grouping dialog.
> SOMETIMES the sort order of the underlying query will be used as the default
> sort if no sort has been specified.
>
> The solution is to open the report in design mode and choose Sorting and
> Grouping from the View menu.
>
> ctdak wrote:
> >> Tom,
> >> You were quite correct, your example was simple but effective to get me going. Thanks very much for your answer. I was successful to a point and then got stumped. I'm a rookie at forming SQL statements. Maybe you can help me again by spotting what's wrong from this code:
> >> Dim strTable As String
> > Dim strCriteria As String
> > Dim strSortOrder As String
> >> strTable = "SELECT * FROM [tbl_History] "
> > strCriteria = "WHERE [HistAccountNo] = " & gstrSingleAcctNo & " "
> > strSortOrder = "ORDER BY [HistFundNo];"
> >> Me.RecordSource = strTable & strCriteria & strSortOrder
> >> The report generates just fine with the SELECT and WHERE strings. I get the records desired for any individual account. But the ORDER BY string is not working. The report will generate but it's not sorting at all no matter which field I have in the ORDER BY string. Any idea why?
> >> ctdak
> >> ----- TPratt wrote: -----
> >> Below is an example of a form that would ask the user to enter a last
> > name into a text box (txtLastName) and click a command button named "CmdGo".
> > The form would then return a list of all people in the table with that last
> > name.
> >>> Const strSQLselect = "Select [LastName], [FirstName] From [TblNames] "
> > Const strSQLwhere = "Where (([LastName]) = "
> >> Dim strSQL as String
> >> Private Sub CmdGo_Click()
> >> strSQL = strSQLselect & strSQLwhere & """" & txtLastName.Value & """"
> >& ")"
> >> me.recordsource = strSQL
> >> End Sub
> >> A simplified example but should be enough to get you going...
> >> Tom
> >>> "ctdak" <anonymous@discussions.microsoft.com> wrote in message
> > news:B9BC7E38-38E4-4819-9352-0012F3ED350E@microsoft.com...
> >> My application asks the user to specify a combination of criteria for
> > selecting records they want out of a table. They input things like:
> > beginning and ending dates (a date range) and the values of two particular
> > fields. They also specify which of three ways to sort the records. Records
> > matching these criteria will then appear on a report in the sort order
> > requested.
> >>> I can't use a query for this since user input will be different each
> > time. How do I take the user input and use it to select out and sort the
> > records they want? Can it be done directly from the table with a recordset
> > statement like this?:
> >>> Set rs = db.OpenRecordset( ... )
> >>> ctdak
> >>

Loading