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
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?
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.
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 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.
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.
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?
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 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.
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 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.
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)?
