Sorting A Date Field

Sorting By Date
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.

View Replies !

---

Sorting By Date
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

View Replies !

---

Sorting By Date
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

View Replies !

---

Sorting By Date
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 Replies !

---

Sorting By Date And Time
Hello,

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

View Replies !

---

Sorting By The Most Current Date.
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.

View Replies !

---

Sorting Query By Date & Employee Name
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?

View Replies !

---

XTab Colun Heading Date Sorting
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

View Replies !

---

Reports And Field Sorting
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 Replies !

---

Sorting An Alphanumeric Text Field
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

View Replies !

---

Purchase Orders And Lines - How To Make The Sorting Of 1 Field Dependent Upon Another
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?

View Replies !

---

Sorting A Table, Apply The Sorting To A Form
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)

View Replies !

---

Print Date Range On A Report Based On A Non-date Field
Is there a way to show the earliest and latest dates of a report generated by a non-date field?

E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)

View Replies !

---

Query Date From DateTime - General Date Format Field.
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).

I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.

Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.

How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?

View Replies !

---

Comparing A Date/Time Field To System Date
Hi,

I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.

Any help would be greatly appreciated!!!!
Thanks
Mikeco555

View Replies !

---

Creating Date Field For Reminder Date
I want to create a field for displaying a 90 day period date before the time of renewal.

This date will be displayed to remind me to send out a notice to a client and will be based on the actual renewal date field.

Can anyone please provide some insight into creating such a field.

Also, I will be creating a similar data field that will be based on 30 Days after the 90 day notice that I send out.

Thank you in advance...

Steve.

View Replies !

---

Date/Time Field Date Extraction
How can I create a field in a query, extracting the date portion of a date/time field? The format is mm/dd/yyyy hh:mm:ss. I need just the mm/dd/yyyy so I can prompt the user for a date range, without them having to key in the date mm/dd/yyyy 00:00:00 to mm/dd/yyyy 23:59:59. Please Help

View Replies !

---

Automatically Populate A Date Field Based On Value Entered In Another Field
I need to create a New Form control for this situation:

If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.

For example:

If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.

Any help is greatly appreciated.

View Replies !

---