Date/Time issue

[patriot76]

Hello

My last question was answered, how to extract a date/time range. What I need now is to take it one step further. I need to extract the earliest time logged for any given day.

Example: 01/17/2005 0625 am
01/18/2005 0615 am
01/18/2005 0622 am
01/19/2005 0614 am
01/19/2005 0623 am

Query results I need would be:01/17/2005 0600 am
01/18/2005 0615 am
01/19/2005 0614 am

 

[sportsguy]

Uncertain as to what you are doing, but this will get you started

You need to split the time into two fields, one date, one time, and you can do this with the format function. This query should get you started, from tblTest, field Logon using your times:


SELECT Format([tblTest].[Logon],"mm/dd/yyyy") AS ThisDate, Min(Format([tblTest].[Logon],"hh:nn:ss")) AS ThisTime
FROM tblTest
GROUP BY Format([tblTest].[Logon],"mm/dd/yyyy");

ThisDate ThisTime
01/17/2005 06:00:00
01/18/2005 06:15:00
01/19/2005 06:14:00

 

[sportsguy]

Or you could do it this way

Just split the date out, and use some sort of criteria to select a particular day, and then use the Min function,

SELECT Min(Test.Logon) AS EarliestLogon
FROM Test
GROUP BY Format([Logon],"mm/dd/yyyy")
HAVING (((Format([Logon],"mm/dd/yyyy")) Like [Which Date] & "*"));


EarliestLogon
1/17/2005 6:00:00 AM
1/18/2005 6:15:00 AM
1/19/2005 6:14:00 AM

sportsguy

 

[patriot76]

date/time

I've tried both ways, not sure what Im doing wrong.

My table is: tblstocker

fields are: username, date/time, qty replenish

Does your statement get typed into the field heading of a query grid or does it go into the criteria area of the Date/time field?

Thank you

 

[sportsguy]

Query is not a spreadsheet

I've tried both ways, not sure what Im doing wrong.

My table is: tblstocker

fields are: username, date/time, qty replenish

Does your statement get typed into the field heading of a query grid or does it go into the criteria area of the Date/time field?

Thank you
=========================================

Ok, i used a Query to get to the result. you are in a table. .

sounds as though you are looking at the query as a spreadsheet. . .
a query is not a spreadsheet . . .

copy my SQL code, open a query, blank, do not add any tables, switch to SQL, click paste, return to design view, and fix up the fields and tables so that the query runs properly.

sportsguy

 

[patriot76]

date/time

I run the below query, but I only get the individual day listed in one column and the other column where the earliest time suppose to be is blank (no return data)

Help


SELECT Min([tblstocker].[date]/[time]) AS EarliestLogon, Format([date/time],"mm/dd/yyyy") AS Expr1
FROM tblstocker
GROUP BY Format([date/time],"mm/dd/yyyy")
HAVING (((Format([date/time],"mm/dd/yyyy")) Like [Which time] & "*"));

 

[Jon K]

SELECT DateValue([date/time]) + Min(TimeValue([date/time])) AS EarliestLogon
FROM tblStocker
GROUP BY DateValue([date/time]);


It's better not to use special characters in field names.
.