Date Formatting for Report Groupings...

[tyleri]

I have a dilemna.

I am running pass-through queries off of a mainframe for reporting purposes in Access.

I need to make a report, and group this report by Date. The only problem is--the date field on the mainframe is formatted like this:

05/21/2001 11:54:08 PM

So - when I group it, each date has its own group header because the times are all different.

How can I make it so that it omits the time stamp next to the date in the field?

I know this will probably get complicated, but I would appreciate any help anyone has to offer!
Thanks,

Tyler

 

[LarryDeLaruelle]

Tyler:

If the report is based on a query, open the query in design view and select the column with the date/time. Right click and select properties. Set the format to Short Date.

This will eliminate the time stamp portion.
Larry De Laruelle
larry1de@yahoo.com

 

[tyleri]

Hey - thanks that worked - but now I still have a problem.

now it seperates by Date - but if the client signed on 2 times or 3 times during a single day, it will show their name 2 or 3 times under the heading. This is all fine and dandy, but I just typed "DISTINCT" in the SQL view and it wouldn't actually pick distinct rows - even though they look exactly the same for the duplicates in all fields of each record...

example:

date name login_id
---- ---- --------
5/12 John johny1
5/12 John johny1

that is what it shows, and even though it says "SELECT DISTINCT" it doesn't select distinct, and I am sure it is because of the date format, it probably still recognizes the time stamp even though it is not displaying the time stamp.

 

[tlbroadbent]

It is a very common mistake to confuse the display format with the storage format. Changing the format to short date in the Query Designer only changed the way the final result appears.

There are several ways to strip off the time. The easiest is to use the INT function. Internally, date/time fields have a whole number portion (date) and decimal portion (time). By using the INT function, you can strip off the time portion.

Select INT(DateTime) As DateOnly From table Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[JoeMiller]

Rather than changing the display (which is what you did by using short date) try pulling the date by using a left function to strip all the stuff after the date ends. So if your date always is in MM/DD/YY HH:MM:SS XM format then you need the first 8 characters from the field. In your query on your date field, pull this:

Expr1: Left(MyDateField,8)

HTH Joe Miller
joe.miller@flotech.net