Hey all, I looked throught a couple of threads for sorting, but could not find exactly what I need.
Basically, I have a list of dates:
10-Aug-07
20-Oct-07
13-Nov-07
etc...
and when I try and sort these dates from earliest to latest, it only reads the number and not the month, like:
10-Aug-07
13-Nov-07
20-Oct-07
etc...
How would I make it sort by date and month?
Thanks,
RR
A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.
However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?
Hello,
Quick question,
I am bringing in two fields into a table which looks like this:
Field 1 Field 2
A 2006-07-25
A 2006-08-21
A 2006-09-21
A 2006-10-21
This is how it's sorted and do not forget that field 2 is a text field.
Now i made two different queries pulling in both Field 1 and Field 2, however in one query i'm pulling in First of Field 2 and in the second query Last of Field 2. For some reason, i'm getting 2006-10-21 as a first and 2006-07-25 as last.
Can anyone explain that?
Thank you!
Hey Im a newb waiting on my books but i need to get this done
Im trying to Pull four fields from a table called Hottopic based on their control number and then sort those by date I know io have got to have the query line all messed up I receive the following error
Microsoft JET Database Engineerror '80040e14'
Syntax error (missing operator) in query expression '<Date>'. Hot Topic1.asp, line 14
The Code Im using is as follows:
<HTML>
<HEAD>
</HEAD>
<Body>
<%@Language = "VbScript"%>
<!--#include file="adovbs.inc"-->
<%
dim objConn,objRec
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open "\lou1-web01
obohelpshpsmateshpsmate v3asphot.mdb"
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "SELECT [Control Number], [Date_] FROM Hottopic WHERE [Control Number] = '10029' Order By <Date>",objConn,adOpenKeyset,adLockOptimistic, adCmdText
%>
<TABLE BORDER=0 WIDTH=600>
<TR><TD COLSPAN=4 ALIGN=CENTER><FONT SIZE="+1"><B>Hot Topics SOG</B></FONT></TD></TR>
<%
While NOT objRec.EOF
Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("Date_") & "</TD>"
Response.Write "<TD>" & objRec("Control Number") & "</TD>"
Response.Write "<TD>" & objRec("Company Name") & "</TD>"
Response.Write "<TD>" & objRec("Comments") & "</TD></TR>"
objRec.MoveNext
Wend
%>
</TABLE>
Like I said Im pretty new at this so any help is greatly appreciated
I have a Table that is used to collect data from client’s phone calls. There is a field for the Client’s name and another for the date the client called (There are a whole lot more fields, but for simplicities sake let’s focus on these two). I have sorted the table on date and indexed the date field. I need to add a “RECORD NO.” field in order to get some queries to run correctly. This field will be autonumber. I would like to have the RECORD NO. field to run in conjunction with the date, that is RECORD. NO. 1 would be the earliest date in the table and the last RECORD NO. would be the most recent date that a client called in. I have verified that the table is sorted by date, but when I add the RECORD NO. field the table reverts to sorting alphabetically by client’s name. The client’s name field is not indexed. I can’t figure out why it is doing this. Any ideas or suggestions as to how to correct this?
Thanks.
I have two tables put into one query to generate a report. One table is Projects and the other is Project Additions. Both tables have the same fields and I want to be able to sort using date criteria. It will sort by the Project table dates but not by the Project Number Additions table. I have tried to put criteria in both field but then it will not work. Is there a better way to do this? The criteria I have put in is under the Project Number Table is between>=[Enter Start Date] and <=[Enter End Date].
View 1 Replies View Relatedhow can i short my record by date. my ata using format dd/mm/yyyy. i've been try using deferent metho but not working.
1. how to show just certain date? ex. just data date 10/08/2007
2. how to show just certain range date? ex. just data beetwen 30/10/2006 - 10/08/2007
thanks
In a report I need to sort by the month. the table field is "Appointment_Date" this is the code I am trying to use in the query behind the report . Appointment_SortMonth: Format([Appointment_Date],"mmmm" & " " & "yy")
but it doesnt sort. also the SORTING & GROUPING MENU does not work either. what do I try Now ???. your assistance will be appreciated.
Jabez
I'm trying to sort dates by the latest date when the query returns multiple IDs with different results. Ex.
ID1 1/1/2006
1/8/2006
ID2 1/2/2006
1/9/2006
In this example I would want ID1 with the date of 1/8/2006 and ID2 with the date of 1/9/2006 since they are the latest date. I will have many IDs that I need to run a query on that will all return the latest date. TIA
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?
I have a calculated date field in a query...if I try and sort by this field I get a data type mismatch.
[CONTREFF] is a date field in a table, [TERM] is a number field in a table. I am trying to calculate the year the contract expires in the "EndTerm" field. The calculation works fine, but I can't sort it.
EndTerm: DateSerial(Year([CONTREFF])+[TERM],Month([CONTREFF]),Day([CONTREFF]))
Please Help!!! Thank you ...
I have a form that allows the user to specify, among other things, date ranges for data to be displayed in a subform (in a form, not datasheet).
In the subform, the user can click any column heading to sort the records by record number, employee name, department, etc. The Click event calls a function:
Private Sub lblReviewDate_Click()
Call SortForm(Me, "ReviewDate")
End Sub
Here's the function module code:
Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
'Sort form column headings OrderBy to the string. Reverse if already set.
If Len(sOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.OrderBy = sOrderBy) Then
sOrderBy = sOrderBy & " DESC"
End If
frm.OrderBy = sOrderBy
frm.OrderByOn = True
SortForm = True
End If
End Function
This works great - except on date fields, which are set to the medium date format as DD-Mmm-YY. I end up with a sort list that looks like this:
21-Apr-05
24-Jun-05
29-Jun-05
11-Jul-05
05-Apr-05
07-Jun-05
A sort in the reverse direction is equally messed up. Can someone advise what to do about this?
Many thanks
Christine
Hello All,
I currently have a form where I would like the form to display the oldest account first, so the overall objective is the employee will action the oldest account first and then go onto the next one etc.
Can anyone please tell me how to do this?
Thank You.
Mos
I created my first RDB input form and it works fine. In the subform new records are created at the bottom row. The subform has a date field. The main and sub forms are based on tables.
When I open the input form I would like to see the subform records sorted by the date field but I don't know where to look for help on that. Can somebody point me in the right direction?
I am including my form in design layout just in case it will help.
[URL=http://pipsisiwah.home.bresnan.net/images/access_screen01.jpg[/URL]
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.
How can I get Access to sort in ascending order correctly on the formatted date?
Thanks
Hi
I have created a query which sorts store information by potential opening dates...however, some of the stores are so new there are no potential opening dates as yet.
I would like the stores with blank opening dates to appear at the bottom but when sorting by ascending (which is what I need) these blank dates appear at the top... is there any way around this?
thanks
I have a combobox (cbo_FileNames) that is unbound and gets fed on activate from a folder
Code:
Set SourceFolder = FSO.GetFolder("c:GundogManagerBackUp")
My problem is that the file names are as follows:
GundogManagerBackUp 02_12_13.xls
GundogManagerBackUp 03_12_13.xls
GundogManagerBackUp 07_08_13.xls
GundogManagerBackUp 16_11_13.xls
GundogManagerBackUp 17_12_13.xls
As you can see it consists of the name and a date of the backup. When it populates the combobox I want it to go in date order I newest to oldest. At the moment its going of the first number.
Is it possible to sort a natural table drop down by date from within a query? What im doing:
1. making a query that has certain results displayed
2. within the query you can select one of the field boxes and it has a list of all the items in that table.
3. is it possible to sort this natural table listing from within the query. I have attached a screenshot. Same thing happens with the client ID listing from within the query. Want to know if it is possible or not to sort those. I cant see how.
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?
I created a pop-up that appears when i open my report so that I can automatically sort through the report by date. However, the report will only allow me to choose one date and i need to be able to choose multiple dates. This pop-up is connected to a list box which has the multi select turned on. How can choose multiple dates through my pop-up?
View 1 Replies View Relatedi made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
Hi all,
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
Thanks in advance for any responses.
D
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies View RelatedI have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
data looks like this:
8/22/13 4:23 PM
1/29/14 12:21 PM
1/28/14 3:27 PM
8/26/13 4:27 PM
[code]....
this is what I have come up with to extract the time component of data set so that I can then later, sort it by the time in a query.
JustTime: TimeValue([YourField])
JustTime: ("hh:mm",([DateTaken])) and or ("hh:mm",[DateTaken])
I get either invalid operator or invalid syntax errors trying both of these.
I was just wondering if it was possible to run a query by a specific value first, then the remainder of the following values in ascending order.
For example, the data would appear in the table as
Smith
Bloggs
Jones
Taylor
What I would like to do is have all the Taylors at the top, followed by Bloggs,Jones then Taylor
I have looked at the OrderBy function, but believe that you can only sort Ascending and Descending
Checked search, cant find anything
Many Thanks
Andy
Hi,
I try to do something but I don't know how.
I have 1 table with 3 rows (title, year, genre) and 1 Form.
Is it possible to create combobox or something with 3 criterium, when I selected
criterium to re-sort columns in Forms (by title, by year, by genre)?
thank you