Hi -
I'm using MS-Access 97 and I need to be able to write a query to extract distinct dates from date/time fields. Just the date, not the time. I have many records with identical dates but different times.
Background: I am giving users a picklist that is supposed to allow users to select a date from the list and then get a report of all records for that date, regardless of time. So, I need to show only ONE "date" per date on the picklist no matter how many records have that date. Currently, I'm able to display each date in the picklist without the time, but each date in the picklist may appear multiple times if there are multiple records for that date. Example:
What I get now:
3/8/2004
3/8/2004
3/9/2004
3/10/2004
3/10/2004
3/10/2004
What I want to see:
3/8/2004
3/9/2004
3/10/2004
Thanks for any help!!
Yes I guess you would. I should read my own SQL
Select Distinct Format([YourDateField],"mm/dd/yyyy") From [YourTable];
SELECT DISTINCT Format([YourDateField],"mm/dd/yyyy") as MyDate
FROM [YourTable;
or
SELECT DISTINCT cdate(datevalue([yourdatefield]) as MyDate FROM [yourtable];
That should help. I forgot when using a formula, you have to tell access what to call the field hence the "as MyDate" verbiage. You can of course call the field anything you want to.
No luck with this one, either. Get same result as previous, where it seems not to be able to "see" the column name in the query. Pennsylvania Geological Survey: PaGWIS::
Each button on the forms initiates an Access query that reads the data from the when we want to extract only a single owner for each well or spring.
http://www.dcnr.state.pa.us/topogeo/groundwater/pagwis/help.aspxHOME |
Unfortunately, when I try this, then try to list the query results, I get a ColdFusion error message that says that my date/time column was "not present in the query".
If I say "SELECT DISTINCT MyDateTimeField" (my original query), I get results, but when it is written "SELECT DISTINCT DateValue([MyDateTimeField])", ODBC seems not to be able to see the field when doing the query. Maybe it thinks "DateValue" is the column? I'm pretty new at all this---it seems like this should be a fairly simple thing to do, but it's giving me fits!
i don't know cold fusion.
maybe it doesn't like the "Date" data type.
try changing it to a String value
Select Distinct CStr(DateValue([YourDateField])) From [YourTable];
I wonder if it's taking it out of a date format try:
Select Distinct Format([YourDateField],"mm/dd/yyyy") From [YourTable];
or
Select Distinct cdate(datevalue([yourdatefield]) from [yourtable];
let us know
Actually, the list will be on an html form as a regular html picklist and generated by ColdFusion accessing the db via ODBC. So, I need to be able to get just the "date" part of the date/time field.
If your using a listbox, open the form in design view, select your listbox, right click and click properties. then select the Data Tab. In the Row Source field there should be an SQL statement like
Select [YourTable].[YourDateField] From [YourTable]
Change this to
Select Distinct [YourTable].[YourDateField] From [YourTable]
This should sort it
Select Distinct DateValue([YourDateField]) From [YourTable];
When I tried the first way, I got ODBC Error Code 07001 "Wrong Number of Parameters - Expected 1"
When I tried the second way, I got ODBC Error Code 37000 "Syntax Error or Access Violation - Wrong Number of Arguments Used":confused:
 I Am a Sinner – What About You?
 Global Sourcing and Supplier Online by Dylan
|
Access 97 - Query To Extract Only The Date From Date/Time Field