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 query that draws from two tables, and the field in question looks like this:
X: [TableData]![FieldA]*[TableNumbers]![A]+[TableData]![FieldB]*[TableNumbers]![B]
It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB.
Is there a work-around for this within the query?
The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.
I am trying to add a calculated date field in a query, I have 2 fields and 1 of them has a date and the other one i would like to to be 3 years from the date of the first field.
View 3 Replies View RelatedI am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
I have a database to track temporary decertification's. I have the expiration and max dates calculated out from the original dates at the top of each box. The temp expiration date is calculated by adding 267 days from the first date . When we enter an extension, the new expiration date is 30 days from the extension date. My question is, how can I make the expiration date update when a new extension is put in.
For ex.
Temp Decert Date: 05 Dec 2014
Temp Decert Extens 1:
Temp Decert Extens 2:
Temp Decert Extens 3:
Temp Experation Date: 31 Aug 2015
Max Temp Date: 04 Dec 2015

how can I make the expiration date update to go 30 days from what is in the extens field 1, 2, and 3 (respectively) instead of 267 days from the original date?

So I want it to look like this after updating a field

Temp Decert Date: 05 Dec 2014
Temp Decert Extens 1: 30 Aug 2015
Temp Decert Extens 2:
Temp Decert Extens 3:
Temp Experation Date: 29 Sep 2015
Max Temp Date: 04 Dec 2015
I've got a members table where my members pay an annual fee. The fee is paid 12 months on from when they originally registered. So, for example, 12 months from today, i would expect to pay my next annual fee.
Now, with different members registering at different times of the year, it isn't so straightforward. I would like my calculated field to indicate to me if a payment is required.Furthermore, I would like to include a '' date range '' so that the calculated field entitled ''Payment Required?'' shows yes for a fortnight. Here is my attempt at the expression which partially works:
PaymentRequired?: IIf((Format(Date(),"dd/mm")>= [RenewalDate] And Format(Date(),"dd/mm")<=[RenewalDateAdd14])Or ([Subscription Paid?]=0),"Yes","No")
i used the Format Date method. But i've got a case where someone is due to pay at the end of November...and today being teh 3rd of December.they should have a good few days yet to pay the fee (before teh aforementioned 14 days is up)
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname]
=Sum([Quarter1_A]) - Named "Quarter_Total"
=Sum([Quarter1_T]) - Named "Quarter_Target"
=Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
I 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.
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?
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
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
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 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.