Populating listbox with dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a .ASP for a database that I cannot change.
I am trying to query the database to fill 2 listboxes with the date of the
record. (i.e. mm/dd/yyyy) The date field is of the date/time type. There
are a few records with the same date, but different times. My goal is to
just list one instance of the date in the listboxes in chronological order.
This is the query I have so far,
"SELECT DISTINCT Left(TimeStamp,InStr(TimeStamp,' ')) AS DateOfTest FROM
TestTable ORDER BY DatePart('yyyy',TimeStamp)"
The resulting error is ...
ORDER BY clause (DatePart('yyyy',TimeStamp)) conflicts with DISTINCT.
Any help is immediately appreciated.
 
Rather than Left(TimeStamp,InStr(TimeStamp,' ')) to retrieve the date part
only, try DateValue([TimeStamp])

I don't understand why you'd want to sort by year only: there's no guarantee
of what sequence the dates would actually appear in within a given year.

SELECT DISTINCT DateValue([TimeStamp]) AS DateOfTest
FROM TestTable ORDER BY DateValue([TimeStamp])

is probably what you want
 
Thanks Douglas,

It worked like a charm.

Ken


Douglas J. Steele said:
Rather than Left(TimeStamp,InStr(TimeStamp,' ')) to retrieve the date part
only, try DateValue([TimeStamp])

I don't understand why you'd want to sort by year only: there's no guarantee
of what sequence the dates would actually appear in within a given year.

SELECT DISTINCT DateValue([TimeStamp]) AS DateOfTest
FROM TestTable ORDER BY DateValue([TimeStamp])

is probably what you want

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Kenz21 said:
I am creating a .ASP for a database that I cannot change.
I am trying to query the database to fill 2 listboxes with the date of the
record. (i.e. mm/dd/yyyy) The date field is of the date/time type. There
are a few records with the same date, but different times. My goal is to
just list one instance of the date in the listboxes in chronological
order.
This is the query I have so far,
"SELECT DISTINCT Left(TimeStamp,InStr(TimeStamp,' ')) AS DateOfTest FROM
TestTable ORDER BY DatePart('yyyy',TimeStamp)"
The resulting error is ...
ORDER BY clause (DatePart('yyyy',TimeStamp)) conflicts with DISTINCT.
Any help is immediately appreciated.
Click to expand...
Click to expand...
 
Back
Top