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.
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
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
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 3 Replies View RelatedI have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY.
- If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY
- If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)
Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
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 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 just started to use access and i know how to insert the current date into a field using date() but i am not sure will this change the date everytime i open the form ? i want to create a form for invoices that shows the date the invoice was created and doesnt change if i re open for editing,
View 1 Replies View RelatedHello,
I was wondering how to check a date in a table vs current date and
then put a yes/no in a colum if the date in the table is past current date.
In the form I know it should be wrote in the Upon Open Event
But I am unsure of the code.
Thank you for your time,
Derek L
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 want the query to return the results of all records that are for the next day.
Sort of like =Date() +1
Please help
There is a Date Submitted in my form that I would like it automatically poplulated with the current date. How do I do that?
View 1 Replies View RelatedHow do you automatically put in the current date in a table?
View 3 Replies View Relateddear all
what is the difference between current date and system date?
for current date : =now()
for system date : =date()
am i right as above?
many many thx
Hello,
I have about 1500 records in a table. We use a form to search through the records to find info on certain accounts. We now want to only search through accounts that are current, so I made a query to see only current accounts. I switched the search form from going off data right from the table to the new current date query. Now when you load the form it says "running query" and the application stops responding. The query seems to load up quick in raw format.. how come the application freezes when searching using the query instead??
thanks,
Matt
Hi there, I am trying to put a query on my form which selects the most recent date of a members attendance from an attendance table and displays it in a text box, the code I am using is:
Private Sub Text41_BeforeUpdate(Cancel As Integer)
SELECT MAX(Attendance.AttendDate) as "LastAttended"
FROM Attendance
WHERE Attendance.membershipNo = Forms!Customer.sbfAttendance.Form.membershipNo
End Sub
Customer is my main form and Attendance is my subform. I think the mistake is that I dont have a field called last attended (you are not supposed to store data like this are you) and I need it to display in my text box (text41) on the main form
Thanks in advance,
Jon
When I print queries (and maybe forms) I would like to have the date printed on the sheet so I know when I printed it out. How should I do this??
Thanks
Hey guys,
I'm trying to pull data that has a due date within 30 days from the current date. I'm trying to use
WHERE x <= Now() + 30
but I'm getting incorrect records. Can someone please help me out? Thanks.