i have a date field where about 80% of the data is entered in the format dd/mm/yyyy but the rest may be year only since the exact date is not known. Therefore, it does not have an input mask or a format set. Is it still possible to sort on that field? (As i write this i realise it is probably a dumb question but always worth a shot!)
View RepliesI have a field on a table that is a DateTime format and it needs to be that way. I am trying to query that table for all the dates to use in a combobox but I only wat the date not the time. The problem is when I format the field to just date I can't sort it anymore as a date and I can't group them. I still get multiple lines in my combobox because there multiple times for the same day. what is the proper way in a query to extract just the date from a field and still sort it as date ie 1/12/2015 is greater than 12/15/2014?
View 8 Replies View RelatedI have a subform which tracks all the notes our personnel have entered in a specific project (which is the main form).
The issue is that I want the most current note to show in the subform when the project is "opened up". The underlying query for the subform has the [DATE] field sorted on "Descending".
I have the data below in a query in Access, and I want it to sort by the Week Begin date.
I have put a sort on for 'Ascending' but it doesn't work. (Due to the 29th being after the 28th - regardless of month)
Is there any way I can force the sort in this query ?
It must be in the same query though, as I could probably do it by using this query in another one then sorting that. However, I don't want to do that for reasons I won't bore you with.
WeekBegin Count
05/09/05 137
12/09/05 90
19/09/05 22
26/09/05 55
29/08/05 122
PS - I should mention that I think it is because the WekBegin date is an expression based on an actual date field. Therefore I believe it's treating it as some sort of text field.
Any help would be appreciated.
Thanks.
J.
Hi,
I've just started playing with Access 2002 (Office XP) and I@m having a little bit of trouble.
I'm using the Query Wizard to summarise my database giving me average values for each month. When the query is first run it displays in the correct month order: April, May, June, July, August, September. But viewing the query again or when trying to graph the data in a Form, the sorting forces alphabetical order: April, August, July, June, May, September.
How can I get the query/form to return the results in true month order?
Here's the SQL info which the Wizard created:
SELECT DISTINCTROW Format$([Raw Data].[TimeStamp],'mmmm yyyy') AS [TimeStamp By Month], Avg([Raw Data].[Indoor Temperature]) AS [Avg Of Indoor Temperature], Min([Raw Data].[Indoor Temperature]) AS [Min Of Indoor Temperature], Max([Raw Data].[Indoor Temperature]) AS [Max Of Indoor Temperature]
FROM [Raw Data]
GROUP BY Format$([Raw Data].[TimeStamp],'mmmm yyyy'), Year([Raw Data].[TimeStamp])*12+DatePart('m',[Raw Data].[TimeStamp])-1;
Thanks in advance for your help.
David
I have a subform that simulates a listbox and I click on the header label to activate the sort. My problem is sorting by date. Below is the sort function and one of the date labels that I need to sort on.
Sort Function:Code:
==========================================
Private Function SortOrder(col As String, xorder As String) As Integer
Dim strSQL As String
Dim sf As Form
Set sf = Forms!frmMainEntry!fctlNotifications.Form
strSQL = "SELECT DISTINCTROW ProgramID, ProgramDescription, Facility, ResponsibleParty, DueDate, FrequencyOfService, AdvancedNoticeDate "
strSQL = strSQL & "FROM qryProgramList "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
sf.RecordSource = strSQL
sf.Form.Requery
End Function
==========================================
On-Click Date:Code:
==========================================
Private Sub lblDueDate_Click()
Dim response As Integer
If Me.txtSortOrder = "DESC" Then
response = SortOrder(CDate(DueDate), "asc")
Me.txtSortOrder = "asc"
Else
response = SortOrder(CDate(DueDate), "DESC")
Me.txtSortOrder = "DESC"
End If
End Sub
==========================================
I'm not sure if I should convert the date into something that can be sorted or just leave it as a date. I can't get it to work. Help please.
Thanks,
PC
help w/sorting two columns one with date checked out, other with date due back. some are overdue and there is not a date entered yet. need to sort by the longest overdue first and leave out those that have already been returned
View 3 Replies View RelatedHello,
I am having some trouble sorting my date and times. I have them both working great individually with RunCommand Sort Ascending after Update. The problem is, the dates will get jumbled up after the time is sorted. Is there a way to connect the two fields where the dates will always stay in order? I have attached a pic of this DB if it helps any. Thanks so much for any help.
Fritz
Hey all,
I have what is probably a simple question.
I want to sort my date field in a query by the most recent date.
When I use ascending or descending it looks at the Month/day/year to sort it.
I need it to look at year/day/month.
Any help would be awesome.
I created a query which at first was simply to run a query which asks you for a drivers name, however we have decided that now we want to sort by the employee name as well as by a specified date range. I originaly had it set up to select the driver name with this in the criteria field in the Driver Name column:
[Enter Driver Name]
Then I tried to enter the following in to the criteria field in the date column:
Between [Enter Start Date: (Format MM-DD-YY)] And [Enter End Date: (Format MM-DD-YY]
However when I run the query it asks me for the date about 4 times, and then it goes to a blank report. I am sure I am not doing this properly somehow, anyone care to help out?
I have a cross tab query for which I want to display the dayes in mmm-yy format. I am grappling with the problem that this is essentially a string, so gets sorted alphanumerically.
Having read around related forums I have tried basing the XTab on a select query, which is sorted by the conventialal date, but also has another field that gives a date using Format([DateAdded],"mmm-yy").
Trouble is, Access only lets me bring across 1 column heading, so I must sort on the column I display, and this doesn't solve the problem.
The coulmn headings are not be fixed (i.e. "Jan";"Feb" etc) as the months are financial year months (April - March).
I can get this too work by using yyyy/mm, but my board of directors are not happy with this format.
Any ideas. I would be open to re-querying my Xtab from another query of from a report if that worked.
Cheers
Mike
I have a query where I ORDER BY ClientId, MatterId and Transdate. The result of the query is correct. All of the transdates are in ASC order within the Matter.
When I run the report for a MatterId the relative transactions are not sorted on transdate
Can there be something in the report that could be effecting the order of the transactions.
I have a report that has a field (4) whose data is in the form of NO.1.1.2, NO.2.1.1, NO.1.12.3, etc. It is not allowing me to have the report display with those in order, sorting this field is not offered. My report is being grouped by Fields 1, 2, and 3, no sorting. How can the data then displayed in Field 4 be in order of those numbers. Sorry I don't know more about Access and I'm having to do this without support. Thank you
View 3 Replies View RelatedI have a table that i would like to sort the records in one field Ascending.
I would like for the field to be updated after the record is entered so that the records in the field are all ways in a alphanumerical order.
I have put [SSB-CW].Square in the Order BY properties of the table. The Table is called SSB-CW and the field to sort is called Square. As i enter the records they stay in the order entered. When the table has been closed and then re-opened the records are sorted.
Do i need to design a form and set the lost focus property with the SQL [SSB-CW].Square.
I have a text field in a table that contains an alphanumeric code. i.e.
DEL998
DEL999
DEL1000
DEL1001
SUN998
SUN999
SUN1000
SUN1001
SUN1002
etc.
I want to run a query to find the highest number for a particular alpha code. In the example for DEL I would want the query to return DEL1001.
I have created a select query that asks for the alpha code, selects all codes starting with that code, sorts them in decending order and only displays the first record.
The problem is that because the field is a text field the numeric is not sorted like a number. So in the DEL case the query returns DEL999.
Any ideas on how I can get a result of DEL1001??
Thanks for your help
I have a query that pulls info from a table. One of my fields is frequency and in that field i have weekly, annually, daily, etc.
How can I make the query sort starting at the daily one and move out to annually?
I have this form for data entry. I want to sort a field in a certain way which is neither ascending nor descending. The items in this field should be ordered as say, C, A, D, B. What I want is a custom order. How can we do it?
The field I am referring to is not on this form, but in the underlying table.
I am thinking of a VB code to achieve this purpose, where I can define the order. But not sure how to do it.
I have a table that stores data adn one of the fiels values is direction x or y so we have
|field 1|field 2|field 3|direction|field 4|
For example
Direction x and y will be different records in the parent table
And I want to arrange my data on a form as so
| direction x | direction y |
|field1|field2|field3|field1|field2|field3|
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
I have created a lookup in a field in a table:
select id, bike from tblbikes.
Column count 2
Width 0,3
When I try to sort the table by bike:
I receive the following error: Type mismatch in expression.
Is there some way to sort a field with a lookup.
I have a database of purchase orders
Many of the purchase orders have a revision No against them, eg:-
Purchase Order No / Line No / Revision No / Value
1001 / 001 / 00 / £50
1001 / 001 / 01 / £100
1001 / 001 / 02 / £200
I am trying to find a way to write a query that will show me the actual value of the final revised value of each Purchase order line ie: the answer to above is
1001 / 001 / 02 / £200
Could anyone please help?
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
I have a table of about 250 items that are sorted based on two fields. I created a query of the table and hide one of the fields because it is only there for sorting purposes to group the data.
For example, I have a "Type" field. In the type field, I differentiate the items with different categories, such as "Materials", "Safety", etc. My next field is "subtype" which assists the "Type" field being sorted properly for my needs. In the "Materials" category, all of the items are either "Fencing", "Plastic", "Wire", or "Miscellaneous". I am sorting the "type" and "subtype" fields in ascending order so that all the "materials" are grouped together, and all of the "fencing, "plastic", etc are grouped together within the materials field.
I am creating a printable report and I do not want the "subtype" field to be on the report, but I need the "subtype" field's sort to be affecting my data so it is grouped properly. I'm having difficulty, is there a way to hide fields in reports?
I am building an Access database for a client. It is an employee staffing database. With that being said they would like the ability to automatically populate the "T2PPCD" date field based on what is entered into the "Report Date" field. (Same table)
The date is the Monday after 180 days from the report date.
I already know how to get it to auto fill 180 days from the "Report Date" but I'm not sure how to tell it to give me the Monday after 180 days.
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I've inherited a database into which the user (not me) is entering essentially the same Date data twice: field 1 contains 6/19/2014 and for field 2 they enter June-14 (the month and year from field 1).
I suspect there should be a way to derive and then reformat the display of field 2 based on the contents of field 1, such that the user only has to enter the first. Is this true? If so, how?
(I know there really is no need for the 2nd field, but existing reports and queries are built around it -- so for right now, I'm just trying to eliminate the duplicate data entry)...