OrderBy Problem

Apr 15, 2005

In my db I have a report that I would like to sort by different fields. I tried coding this using option buttons, a command button, and the "Case" statement. While putting in the OrderBy portion of the code I keep getting errors in relation to the syntax. I'm not sure how long I worked with it and got absolutely nowhere. Can someone clue me in please? Here's my code:

Private Sub Command14_Click()
Select Case Me!Frame5.Value
Case 1
'Sort by Military Occupational Specialty (MOS) ascending
DoCmd.OpenReport ("Report1")
OrderBy MOS

Case 2
'Sort by Military Occupational Specialty (MOS) descending
DoCmd.OpenReport ("Report1")
OrderBy MOS, DESC

Case Else
MsgBox ("Please select an option before attempting to open the report.")

End Select
End Sub

View Replies


ADVERTISEMENT

Orderby

Apr 26, 2006

Hallo everyone,

I have a table with one of its column value as "B" or "D" or "E".

Now i am in need to display the rows using the select statement with the following conditions.

1. First display the rows with value "D"
2. then display the rows with value "E"
3. then display the rows with value "B"

Is it possible to specify the above condition in a orderby clause.

thanks,

aravind.s

View 1 Replies View Related

OrderBy With Wildcards

Oct 13, 2006

I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.

ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";

View 2 Replies View Related

Listbox OrderBy

Jun 20, 2006

Hi,
I have a list box based on a quite a complex query. I would like give the user the option to dynamically sort the list box.

How can I refer to my listbox's SQL rowsource and add a sort order using VB?
i.e.
Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL

I am aware of how I could do this if my SQL was in VB i.e.:

Dim strSQL as String
Dim strSortOrder as String
Dim optSort as Integer

Select Case optSort.Value
Case 1
strSortOrder = "ORDER BY LastName;"
Case 2
strSortOrder = "ORDER BY FirstName;"
End Select

strSQL = "SELECT FirstName, LastName FROM TblName " & strSortOrder
Me.ListBox.Rowsource = strSQL


....but my query is far more complex than this and I dont want to have to keep calling the code each time I need to requery my form.

Thanks
BF

View 14 Replies View Related

Orderby Syntax

Nov 2, 2004

I want to basically perform a "Sort by ascending" Function.

I presume I have to grab the focus of the Item I want to order by.

So :

me.(Field).Setfocus

But then what is the code for running the Sort by ascending/Descending?


My data is currently in a list of text boxes. When I normally 'Sort by ascending' the function works fine.. so I just need to activate that....

View 4 Replies View Related

Weird OrderBy Problem

Feb 21, 2006

Hi all,
Found a weird problem when editing records in continuous form view (and datasheet view) when a particular memo field is being used by OrderBy to sort the data. When i have just created a new record and tab to the next record, the record i was just in jumps to the top of the records, but in its place is an exact copy of the record above. Very confusing, and looks like a redraw bug but it happens on all PCs.

What happens is ... (letters relate to records going down the screen)

A
B
C
D ....new record being entered then <TAB> and ...

D ....has jumped up here
A
B
C
C ....duplicate of C - but duplicate record never created in the table
E ....new record

When i quit the form and come back in, everything is ok

As far as i know, OrderBy only takes effect if you refresh the form (which i'm not doing). It works fine if i take off OrderBy, or if i use another field with OrderBy. So is there some problem using OrderBy with a memo field, and how is this making my records jump about?

help!

Pete

View 1 Replies View Related

Using OrderBy Gives Error At Reopening Form.

Nov 15, 2006

I have a form where the recordsource is a stored procedure.

This forms shows a list. When the user clicks on a column title, the column is sorted by using OrderBy in VBA:

f.i.:

Private Sub lblCode_Click()

Me.OrderBy = "strCode"

End Sub

As a result, in the properties of the form, for the property Order By, the fieldname is filled in (f.i. strCode).

This works fine, but when the form is closed, access saves the property Order By.

Next time the form is opened, it gives an error: The recordsource <storedprocedurename> specified on this form does not exist.

When I remove the fieldname in the Order By property of the form, the form opens without an error.

I have tried to clear the Order By property by using Me.OrderBy = "" in the Close event of the form, but this does not work.

Any solution ?

View 2 Replies View Related

Modules & VBA :: OrderBy When Opening A Form

Nov 29, 2013

I'm trying to have a form sorted in a particular order when it's opened.

When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.

However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.

So I tried to add the following code :

Code:
Private Sub Form_Open()
Me.OrderBy "employee_name ASC"
Me.OrderByOn = True
End Sub

Which failed epically.

View 6 Replies View Related

Transfering RecordSource/OrderBy Of Form To Report

Oct 17, 2006

I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:

Reports!rpt_frmTaskMain.RecordSource = Forms!frmTaskMain.frmTaskSub1.RecordSource
Reports!rpt_frmTaskMain.OrderBy = Forms!frmTaskMain.frm.TaskSub1.OrderBy

On execution I get error: "Object doesn't support this property or method"

I am thinking this can be done, and likely have the references wrong. Help?

View 1 Replies View Related

Forms :: Can't Clear Filter Or Orderby Permanently On Form

May 14, 2013

I have a tabular form that is based on a select statement (Query) in the source property. It filters & sorts from this select statement. permanently

I have just added a
Me.Filter = "[Form1].[InDate] = #" & MyValue & "# "
Me.OrderBy = "Indate DESC"

This all works fine, filters & sorts on the existing results.However this new filter & sort seems to remain no matter what I do.I have used every variation one me.filter ="" with Me.FilterOn = True, False & every combination.

Me.Filter="" & Me.FilterOn = False does initially seem to clear the lsit to show all records but as soon as type anything that filters the Form on the original, existing underlying select statement the extra Filter Property & Sort properties are occupied with the last filter/sort.

I have manually deleted any remaining Filter Or Sort properties direct from design view, saved, compacted & saved again.Once I use the new me.filter it will always returned as soon an any filter on the form is applied.I definately am not setting it again anywhere. Is there something better than me.filter ="", seen clearallfilters mentioned but not sure if this would apply just to the current form but reluctant to use a blanket clear as I do have quite a lot of other things going on.

Not sure when but many months (If not years) ago my form source stopped being a query and started using the query (sql) direct from the source property.I have left it this way as not sure if any consequence of moving back but don't remember how or how it happened. What are the pros cons of using a separate query.

View 4 Replies View Related

Modules & VBA :: Form Filter And OrderBy Pass To Report

Aug 16, 2014

I am able to filter a data on a continuous form using drop downs and then the following code attached to a cmdbutton to create a report of the filtered data.

Code:
Private Sub Command30_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "rptconveyorerrors", acViewReport, , strWhere
End Sub

On the same form where I filter the data i can sort it by clicking the headings aswell, however when i generate the report using the above VBA it doesn't take the sort with it and just generates it without the sort.

I am using the following VBA to sort my form

Code:
Me.OrderByOn = True
If Me.OrderBy = "[empname] DESC" Then
Me.OrderBy = "[empname] ASC"
Else
Me.OrderBy = "[empname] DESC"
End If
Me.Refresh

I thought it may be possible to use the following sort of VBA to pass the sort however i cant get it to work:

Code:
Private Sub Command30_Click()
Dim strOrder As String
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
If Me.OrderByOn Then strOrder=Me.OrderByOn

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved