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 Complete Forum Thread with Replies
Related Forum Messages:
Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria
Hello buddies :D, do you have any idea how to make this work? To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM. This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View Replies !
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 A Date Field
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 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 !
Ignore Time In Date And Time Field
Hi, Wonder if someone can help please. I'm quite new to Access so please bear with me. I have a data field in my database consisting of both a date and time. I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding. The problem is that because the field contains a time it doesn't return any records when I run the query. Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above. Many thanks Chris
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 !
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 !
How Make This Happen,time+date=new Date
Hi, I have a report that have a datebox(Short date) and a timebox(Short time) and also a box that contains numbers like 36:59 and so on. That number is hours and minutes, I would like to take the date, time and add my 36:59 and get a new date from that and put that in another box. The date and time is used as planned date and planned time for some work to happen. 36:59+planned time+planned date=should be planned finishing date and time. Does anyone know what I have to do, to make this happen ???
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 !
Reformat Date Time To DATE
I am working with an Access 2000 front-end linked to an Oracle back-end. One of the fields is formatted as "7/9/2006 3:41:01 PM". In my query I am using the SQL "CDate(Format([R6T_QVLOG].[QVL_COMPLETE_DT],"Short Date")) AS NewDate" to format to a useable date format. But, it is returning a string, not a true date. Is it possible with SQL to convert this date/time to a date format?? Are there other options?? Thank you!
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 !
Using Date, Time
I have a table called : tblTemp contain the field: EndTime (type: ShortTime) A combo box: cboTime How can i assign value for cboTime using the field: EndTime from tblTemp I tried: dim db as Database Dim rec as DAO.recordset set db = currentDB() Set rec = db.OpenRecordSet("tblTemp") 'Go to appopriate record cboTime = rec![EndTime] But i recieved the message: You can't assign a value for this object ...
View Replies !
Time And Date
Okay i know you can make a form post the date and time with the =NOW() command, is there a way to make it so it shows the current date and time? so you can watch the day go by in seconds if i wanted to?
View Replies !
Date Time Help
This is very tricky I hope that someone can help please. I have a table that I imported to access, it has four columns, start date start time finish date finish time. I need to generate a report to show items that took more than 30 hrs, ontop of this items that fall within friday have a different senario. Someone gotta help.
View Replies !
Date/Time
I have a field [Date] in one of my tables, and the field is set to Date/Time datatype....format is "mm/dd/yyyy"......but when I type in "03/29/2005", I get an error saying that "the value you entered is not valid for the field".....
View Replies !
Date/Time
I have a field in a table set to Date/Time. Is it possible to set the properties so that it always defaults to "PM"? In other words, if I enter "5:30" and then TAB, I always want it to be entered as "5:30 PM". Right now it appears to default to "AM". For this particular application, 90% of the entries are made in the PM. Thanks.
View Replies !
More Date/Time Fun
I've got a field in a query that is pulling the Date/Time from a table in the db. I've added another field in my query ... Tran_Datetime: Format([dbo_t_PMCS_History]![Tran_Datetime],"mm/dd/yyyy") to format the Date/Time into text so I can use the Date()-1 command. Problem is ... the Date/time in the database is like 1/1/2006. There is no leading 0 on the month or day. Now when I use the format function with "mm/dd/yyyy" the date() command will only work when the Date/Time is has two digits in the mm/dd fields. Hope this makes sense :confused:
View Replies !
Date/time US <> EU
Hello my sql/access friends I have a table with a date/time field named “teLezenOp” and I use the following line in VBA: SELECT * FROM tblBericht WHERE (teLezenOp<=#" & MediumDate(Now, Now) & "# or teLezenOp is Null ) ORDER BY datum (I use the european notation dd-mm-yyyy) Here is what goes wrong: yesterday 28-2-2006 there was no problem But today 1-3-2006 I get an error: Syntax error in querry-expression: telezenOp<=#1-mrt-2006# or telezenOp is Null If I look in my acces DB telezenOp = 20-2-2006 so there is a difference in notation? 20-2-2006<= 1-mrt-2006 I don't think this is the problem because yesterday it worked with the same code. now I've just read the following in one of the threads on the forum: "In VBA code and query SQL statements, the date between the # signs must be put in US format. So #4/1/2005# means April 1, 2005." I guess this is the reason of my problem: I compare a EU notation with a #EU#(so that is incorrectly read as a US notation) is there an easy way to switch between EU and US notations in VBA/sql statements? I guess there is but I can't find it... tnx for any (hopfully rediculously simple) tips/tricks/sollutions djemmers
View Replies !
Date And Time ?
I have a fiield that has date and time (00/00/000 00:00). I want to take the date portion and combined it with another time value. How can this be done? Thanks
View Replies !
Date And Time
I have a button on a form where the user can simply click and the date will be entered into a field. I would like to make it so the date and time can be entered, but I am stuck. Under the event procedure of the button I have check_req_date = Date The field is set to General Date so the time can be included, but how do I get it in there? Thanks, Adam
View Replies !
Date And Time
Is there anyway I can default the current date and current time into a fields of a form - sorry if this has been asked before
View Replies !
Date Time Help
Hello everyone, Ihave been here many time for coding help and now i am hoping someone can hlep me. I am using access xp to create a form for my boss that tracks hours worked for each employee. my problem is that i am using the =(DateDiff("h",[Time_In],[Time_Out])-[Lunch_Break]) function in access and it gives me the correct time for some days an incorrect time for others, example a user clocks in at 8:30 am and leaves at 5:30 pm gives me 8 hours worked on the other hand a user clocks in at 8:30 am and leaves at 6:00 pm and it still gives me 8 hours worked. the table lunchbreak is a dropdown menu for the users and it has increments of 0.5. if any one can help i would greatly appreciate it thanks. if you need more information on my tables timein, timeout = date time lunchbreak, dailyhours= number dailyhours displays results on form
View Replies !
Date/Time
I have a 'timestamp' field and do not want to display the time. I only want to display the date. How can I do this? thanks
View Replies !
Date/Time
In my db, i have tblCenterName.Appointment with the format mm/dd/yy hh:nn and the input mask as: 99/99/00 99:00;;_ I want the user to only be able to type in an hour between 8 a.m and 4 p.m. and either 00 or 30 for the mins. Any other time is not acceptable. What kind of expression in the validationRule box can i add to limit the user to what they can type in. Or would that be the correct place to do it? I want to keep the date and time in the same field so I can set index as no duplicates so as not to schedule an Appointment at the same date and time.
View Replies !
|
TRACKER
