ASC Or DESC Sort Based On Another Field
Aug 12, 2014
I have a query that lists items in locations in our warehouse. Each location is broken down by Aisle-Bay-Level-Position, for example 50-101-01B; or 51-106-02 with all the even Bays on one side and Odd bays on the other side of the aisle. I have the query set up so it separates odd and even so I can go all the way down one side of the aisle then I have to walk back to the beginning of the aisle to check the other side.
What I am trying to do is change the sort based on another field that I have called SORT with a value of AZ or ZA depending on if I need that section to be sorted ASC or DESC. So for example all even bays in aisle 51 need to be sorted DESC while all ODD bays would be ASC. The way our warehouse is laid out it is not always the odd or even side that needs to be sorted DESC, that's why I made the SORT field. The formula I used for the SORT field is as follows:
SORT: IIf(([Aisle]='50' And [OE]='ODD') Or ([Aisle]='51' And [OE]='ODD') Or ([Aisle]='53' And [OE]='ODD') Or ([Aisle]='52' And [OE]='EVEN'),'AZ','ZA')
Is there a way to make it sort the BAY in ASC or DESC based on the value in SORT?
As an example here is how I want the order to be:
50-101-01A
50-101-01B
50-103-02A
50-103-02B
51-101-01
[Code] .....
View Replies
ADVERTISEMENT
Mar 8, 2013
My table has 3 fields, Employee Number (text), Job Description (text) and Current (yes/no). The table keeps track of the Job Descriptions that an Employee has had along with his current Job Description (actually Title is more appropriate a word but client requested Description) So the table has records such as:
0001 - Floor Sweeping - no
0001 - Ceiling Cleaning - yes
0001 - Dumpster Turning - no
The yes signifies that that is the current Job Description for employee #0001 and the others with no are previous Job Descriptions that the employee #0001 has held.
Now - I created a form to add these records to the table and on that form I want a list box to show the records in the table for this employee but I want to sort it such that the current Job Description is listed first and the rest show up in alphabetical order following. I have the query to list all of the Description, but how can I get the current one to always appear on the top of the list?
View 14 Replies
View Related
Feb 5, 2014
In a form is there a way to change sort order based on which field is active?
I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.
View 3 Replies
View Related
Nov 3, 2005
Hi
I am looking for an equivalent for the DESC command in Access.
I need to extract the table definitions: name of each fields (at least).
I know I can print that from the menu in Access, but it's not what I want. (and it does not print the comments section of the fields)
I need to be able to generate some kind of text file cause I need to manipulate the output myself with a Text Editor for documentation purpose.
Is there such a function in Access? Or is there any module that can do that?
Thanks
View 7 Replies
View Related
Mar 5, 2007
I want to sort my query results by VendorNumber (which is an AutoNumber won't be sequential due to deletions). I want to sort the query by what vendor number the current form is on. So if my vendor numbers are 1, 2, 14, 16, 17 and the vendor number on the form is 14, I want the query to be sorted with the Items from vendor 14 first then 16, 17, 1, 2.
Your time is appreciated,
JOe K.
View 7 Replies
View Related
Jan 28, 2006
Below is a script that I want to present in descending order but it is coming up with the error:
Microsoft VBScript runtime error '800a000d'
Type mismatch
/tribute2.asp, line 175
Could it be that the remid and gbid are in two different tables in the database .... or have I just got the scripting all screwie ... the line where the error is occuring is the first set of code ... the second set of code is the area that it relates to ...
View 7 Replies
View Related
Jun 7, 2007
I have a query that I want to sort based on a table. (SS BELOW) I want the OS in qry2FINAL to be sorted based on Trn-OSstops table. So the OS column of the query should be sorted as which is the order shown in the table. I tried linking the 2 tables in my query but I had no clue what to do from there. Any help?MV6HP108PT337HP113 (sometimes there is an extra field, but can I make it show up last?)http://i10.tinypic.com/68hkwhe.gifEdit: I looked at my SS and it might be confusing. Ignore all of the records in the Trn-OSstops table except for the highlighted one.
View 14 Replies
View Related
Oct 14, 2005
I would like to sort records based on fiscal year for a chart. The fiscal year would be 7/01/2003 to 06/30/2004. I would like to sort this based on oldest date to newest date. This query could span several fiscal years. Any help would be greatly appreciated.
View 7 Replies
View Related
Sep 10, 2013
This is something I occasionally see in Access and has been bugging me for quite a while.
As an example, when I have a table (all text fields except for the ID field which is an Autonumber with a unique index - ie just what Access creates when you import data) and I try to make a new table from a query by indexing the Autonumber field in descending order (ie to reverse the order of the table), it doesn't work properly.
So if I have:
SELECT [mytable].* INTO [mytable sorted] FROM [mytable] ORDER BY [mytable].[ID] DESC;
When I preview the data (ie run the select query to have a look at it), it looks fine.
When I change the query to a 'Make Table' and I then I check the table it makes, the order changes part-way down the list, so looking at the ID field it runs from number 2669 down to 2087 correctly, then it goes from 1960 to 1956, then 1803 to 1799, then 1751 to 1747, etc etc etc. After a while it seems to correct itself again, and orders normally down to #1
I'm using Access 2002.
View 5 Replies
View Related
Dec 13, 2013
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
View 12 Replies
View Related
Oct 9, 2014
I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.
View 6 Replies
View Related
Sep 23, 2013
I am trying to put together a form where employees will be entering maintenance information into my database. One of the requirements for this data is that when they perform repair work on an asset, they have to list a problem, cause and remedy. These are codes that are specific to each asset.
The closest thing I can think of to what I am trying to acheive is the selectors on car search websites. You select a MAKE, and then the MODEL list is narrowed down based on that, then you pick the TRIM, which is narrowed down based on the model selection. I have an Excel spreadsheet of the Assets(Failure Class), Problems, Causes, and Remedies...I'm just not sure how to put this into Access to get the desired output. I have attached a sample of the Excel spreadsheet for illustration.
I believe I'm going to have to put each of the columns in a seperate table and use relationships..but I'm not sure about that.
View 2 Replies
View Related
Jan 4, 2014
WinXPPro Sp3
Access 2007
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
List_Order
3
1
15
5
4
12
2
7
View 3 Replies
View Related
Mar 4, 2015
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])
If I select the year from the combo, it works, if I select "ALL" nothing is shown.
here is the full SQL
SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth
[Code] .....
View 5 Replies
View Related
Aug 8, 2013
When I "Add Existing Fields" I'd like the list to be sorted alphabetically...how to do this?
View 4 Replies
View Related
Aug 17, 2013
I created a table by importing data from an Excel spreadsheet. Now I have discovered that I am unable to filter the data, i.e. do an ascending and.or descending sort, in the first field of the table, which is a long text field. It is the only field that has text, all others are yes/no fields. Is there any way to fix this?
View 5 Replies
View Related
Jul 21, 2006
I have a query that draws from two tables, and the field in question looks like this:
X: [TableData]![FieldA]*[TableNumbers]![A]+[TableData]![FieldB]*[TableNumbers]![B]
It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB.
Is there a work-around for this within the query?
The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.
View 2 Replies
View Related
Mar 16, 2008
I have an expression in a query
Expire: IIf([payterm]="X","",DateAdd([payterm],1,[orderdate]))
However when I sort it it does not sort in correct manner
it's goes like
1/11/2007
1/15/2008
10/10/2006
10/16/2007
10/31/2007
10/5/2006
I have the field properties set to Short Date.
What do I need to do for this to sort right?
View 2 Replies
View Related
Sep 16, 2005
I have a form that has a bunch of project information and scrolling buttons at the bottom to browse by next/last.
Right now the form is sorted by the ID associated with the project, which kinda sucks because they were and are not put in alphabetically.
I discovered if you right click on a field and click "Sort Ascending" or the opposite it works fine but when I open the database it is right back to normal ID sort
I have checked the Data and Other tabs but can't find anything with regards to this as well as I have tried sorting the actual table by name and that changed nothing.
Any help would be greatly appreciated.
View 1 Replies
View Related
Sep 14, 2004
I have a lookup field for my table that pulls its data from Pay period table
When I click the drop down in my table, the dates aren't in any order. I think this is because lookup fields are considered text fields.
Is there a way to sort my drop down box on the table?
KellyJo
View 2 Replies
View Related
Sep 19, 2004
I am trying to remove a "sort and grouping" field from my report. I delete the field from the menu by using the "backspace" key on my key board. I am getting any error, invalid sort field. When I put something into the field, the error goes away. How do I delete a sorting field I do not want??
thanks for your help
newbie and learning
View 1 Replies
View Related
Aug 1, 2012
I have a form where data can be added and it has different tabs. The 3rd tab has idVersion field and Comment field where users can add the version and comment, if needed. When they need to add another version, the blank fields will be added as next row, so on and so forth. The problem is users are adding a lot of versions but not sorted. So for example:
Row 1 of the form: idVersion field is "1" and comment has "Test 1".
Row 2 of the form: idVersion field is "8" and comment has "Test 8".
Row 3 of the form: idVersion field is "2" and comment has "Test 2".
Row 2 of the form: idVersion field is "5" and comment has "Test 5".
So how can I fix it where after they saved the version and comment, when they close the database and re-open it again, the data will show up as:
Row 1 of the form: idVersion field is "1" and comment has "Test 1".
Row 2 of the form: idVersion field is "2" and comment has "Test 2".
Row 3 of the form: idVersion field is "5" and comment has "Test 5".
Row 2 of the form: idVersion field is "8" and comment has "Test 8".
So even if they add a new version and comment (example version "3"), it will be automatically sorted when the database is reopen.
View 6 Replies
View Related
Jan 11, 2007
Below is the sql code I have for a CrossTab Query. Total as GrandTotal
shows GrandTotal of all weekly columns. Is there anyway I can get the
Query Display to sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY Sum([Trends-1-3TON-WEEK].Totals)
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
View 1 Replies
View Related
Dec 1, 2006
hi
i found this code here and it works IF the number comes first and is preceded by a letter
123AA
but it does not work if the letters come first
AA123
here's the code
Public Function GetString(WholeString As String) As String
Dim i As Integer
Dim Temp As String
Temp = CStr(WholeString)
For i = 1 To Len(WholeString)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetString = Mid(Temp, i)
Exit Function
End If
Next i
GetString = Temp
End Function
Public Function GetNumber(WholeString As String) As Double
Dim Temp As String
Dim i As Integer
Temp = CStr(WholeString)
For i = 1 To Len(Temp)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetNumber = Mid(Temp, 1, i - 1)
Exit Function
End If
Next i
GetNumber = Temp
End Function
the probelm is with this line but i'm not sure what it is
GetNumber = Mid(Temp, 1, i - 1)
(i also get runtime error 13) but my data is in the same format as the example i downloaded.
any ideas anyone?
thanks in advance and thanks to the person who created the code
View 1 Replies
View Related
Apr 22, 2013
I have a query with a float/delta column which is the expression:
Code:
Float: [Date1] - [Date2]
When I try to change the order by in the column filter drop-down i get a data type mismatch in criteria expression error.
View 1 Replies
View Related
Mar 19, 2014
We're trying to create a database to read quotes from a system based on changes made to components.
We have the database set up to store the quotes happily. We're pleased with the input forms and data capture however we are struggling with a query to get useful data from the database.
I have a main quote data table listing all the required fields such as costs and supplier data for the quotes, a table storing components that may be changed as part of a quote and a table listing alterations that could be made to these components. Each quote could have a number of changes made to a number of components. All these changes are stored in a changes made table which lists the quoteID, ComponentID being changed and The AlterationID of the alteration being made.
I want to be able to input a varied amount of changes via a form and be shown a list of all quotes where at least one change matches. I've managed to get this far using a lot of OR statements however the complexity is introduced as we need to sort these by an extra column produced by the query displaying the percentage the changes made in the quote match the search input.
If a quote appears matches my changes and there are no other changes on the quote - (100%)
If a quote matches all changes I have input but I input 5 changes and the quote has 6 - (5/6 - 83%)
If I input 1 change and a quote matches but has 8 changes on the quote - (1/8 12.5%)
View 2 Replies
View Related
