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.
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.
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?
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.
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've been struggling with this for a while, and even though I understand the theory, I can't get my code to work correctly.
I have 2 fields: CallbackFrequency & Last Contacted.
I want to use these fields to populate a third field (callBackOn) so that we can have a list of candidates that need to be called from a certain date.
CallbackFrequency is added from a combobox, so all values are either 1 Month, 3 Months, 6 Months, or 12 Months.
I have tried creating a calculated field directly in the candidates table, but apparently this is not possible.I've therefore tried to create a query using IIF statement that will calculate this value.To get to grips with the code, I'm only dealing with 1 callbackFrequency at the moment. So far I have:
I have not factored in the date manipulation yet, as the query does not display the LastContacted date of the record it finds, it only shows a blank cell,
I 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
i 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?
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??
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.
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
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].
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.
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?
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.
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.
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?
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
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.
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?