Adding minutes to DatePart with DateAdd

[chizzy42]

HI, Hope all is well. I have a query that is fine if I was to use the start and stop times on the hour, I'm trying to learn a bit more and try some changes so I've been trying to alter it to try and stop and start at separate times e.g. instead of between 7am to 8am trying 7:30am and 8:30am. The problem I have is trying to add 30 mins to the time . I've attached a trial database to show what I was trying.

When I use the code

SELECT DatePart("h",TimeValue(Format([field1],"00\:00\:00")))  AS HourVal

The Hour turns out as expected. When I try to use DateAdd("n",30, xxx) with the above I get a date and time in the field with 30 mins added but no hour and I cant see where the date is coming from if I specify minutes with "n" DateAdd, 06/01/1900 00:30:00. What I expected was the hour o go from 7 to 7:30

Any help as always is appreciated

thanks

 

[MajP]

Try,
DateAdd("n",30,CDate(Format([field1],"00\:00\:00")))

Copy Of casetrial Copy Of casetrial

Field1	Test
70538​	7:35:38 AM​
70543​	7:35:43 AM​
74243​	8:12:43 AM​
74438​	8:14:38 AM​
74821​	8:18:21 AM​
75044​	8:20:44 AM​
75132​	8:21:32 AM​
75214​	8:22:14 AM​
75347​	8:23:47 AM​
75425​	8:24:25 AM​
75623​	8:26:23 AM​
75718​	8:27:18 AM​
83623​	9:06:23 AM​
84623​	9:16:23 AM​
105623​	11:26:23 AM​

 

[GPGeorge]

Out of curiousity, where do the supposed date values come from?

When I open the accdb, this is what I see.

Field1 values are numbers, not dates, and coercing them to dates returns:

 

[GPGeorge]

Oh, I see! These are actually strings of digits representing times?

 

[chizzy42]

Hi GPGeorge, I was just replying there and you beat me. They're entry times brought in from a text file .

MajP, Thanks for the reply. That adds the 30 mins but I'm tying to group the the first column by the time specified, as in the screenshot the hours between 7 and 8 yields 10 then between 8 and 9 there were 10 passes. The code is ok with full hours , but I was trying to find the passes between 7:30 and 8:30 for example.

 

[MajP]

This would be radically simpler if you run a update query to your table and add a real time field
Timer1 Timer1

Field1	Fld1AsTime
70538​	7:05:38 AM​
70543​	7:05:43 AM​
74243​	7:42:43 AM​
74438​	7:44:38 AM​
74821​	7:48:21 AM​
75044​	7:50:44 AM​
75132​	7:51:32 AM​
75214​	7:52:14 AM​
75347​	7:53:47 AM​
75425​	7:54:25 AM​
75623​	7:56:23 AM​
75718​	7:57:18 AM​
83623​	8:36:23 AM​
84623​	8:46:23 AM​
105623​	10:56:23 AM​

Then a simple query
SELECT Timer1.Field3, Count(Timer1.Field3) AS CountOfField3
FROM Timer1
WHERE (((Timer1.Fld1AsTime) Between #12/30/1899 7:30:0# And #12/30/1899 8:30:0#))
GROUP BY Timer1.Field3;

qryPassFailsFrom0730to0830

Field3	CountOfField3
Fail	2​
pass	8​

 

[chizzy42]

HI Thanks again for the reply MajP, That is a better way of getting the data . I don't think I've explained too well what I was trying to do. In the original query I pull a variable called HourVal and this field from the query is used on a form that shows fails and passes for that hour, but displays them starting from the time on the hour 7, 8 ,9 etc I was trying to add a time (e.g 30 mins) that would mean the start time would be 7:30, 8:30,9:30. See the attached form for how it looks. So the grouped values (passes and fails) for that hour are linked to HourVal, hope this makes sense. Just trying to learn a bit more trying ideas out, I was just surprised that when I first tried to add 30 mins onto the hour that I ended up with a date format

 

[MajP]

Your queries are a bit complicated using a long of subqueries and vba functions. I would make a table of ranges
tblRange tblRange

RangeID	RangeName	RangeStart	RangeEnd
1​	07:00 to 07:15	70000​	71500​
2​	07:15 to 07:30	71500​	73000​
3​	07:30 to 07:45	73000​	74500​
4​	07:45 to 08:00	74500​	80000​
5​	08:00 to 08:30	80000​	83000​
6​	08:30 to 09:00	83000​	9000​

For whatever ranges you want. I do not have to even convert to time.
Then you can do a query to get your values in a range

SELECT tblRange.RangeName, Timer1.Field3 AS Pass_Fail
FROM tblRange, Timer1
WHERE (((Timer1.Field1)>=[RangeStart] And (Timer1.Field1)<[RangeEnd]))
ORDER BY Timer1.Field1;

qryPassFail qryPassFail

RangeName	Pass_Fail
07:00 to 07:15	pass
07:00 to 07:15	pass
07:30 to 07:45	pass
07:30 to 07:45	pass
07:45 to 08:00	pass
07:45 to 08:00	pass
07:45 to 08:00	pass
07:45 to 08:00	Fail
07:45 to 08:00	pass
07:45 to 08:00	Fail
07:45 to 08:00	pass
07:45 to 08:00	pass

Then you can make two more simple queries to seperate the passes and fails

SELECT qryPassFail.RangeName, Count(qryPassFail.Pass_Fail) AS Failures
FROM qryPassFail
WHERE (((qryPassFail.Pass_Fail)="Fail"))
GROUP BY qryPassFail.RangeName;

qryPasses qryPasses

RangeName	Passes
07:00 to 07:15	2​
07:30 to 07:45	2​
07:45 to 08:00	6​

You then can bring most of this together to get your answers

SELECT tblRange.RangeName, Nz([passes],0) AS Pass, Nz([failures],0) AS Fail, IIf(([fail]+[pass])=0,0,Round(([pass]/([fail]+[pass]))*100,2)) AS Yield
FROM (tblRange LEFT JOIN qryFails ON tblRange.RangeName = qryFails.RangeName) LEFT JOIN qryPasses ON tblRange.RangeName = qryPasses.RangeName;

qryNew qryNew

RangeName	Pass	Fail	Yield
07:00 to 07:15	2	0	100​
07:15 to 07:30	0	0	0​
07:30 to 07:45	2	0	100​
07:45 to 08:00	6	2	23.08​
08:00 to 08:30	0	0	0​
08:30 to 09:00	0	0	0​

These are all very simple queries that take a few seconds to build. These are efficient too. The nice thing is you can easily add and modify ranges and have unequal sized ranges in this method.
For the cumms I again would make two simple queries and again link by rangename or I should have did range id.

 

[arnelgp]

is this close (Query2 and Query3)

 

[chizzy42]

MajP, arnelP,Thanks to both of for the time and expertise here. MajP that was a great explanation of breaking down the queries and being able to use the range, answers what i was thinking about next if the hours monitored weren't in a uniform standard eg between 8 and 9 am then 9:45 am and 10:45am..i had thought it would take case selects to do that, using this gives the flexibility i was wondering how to achieve.
arnelgp thanks for the formatting examples , can look into these a bit deeper now to get a clearer understanding...one question please what does the $ sign do in the code in Query1 I tried a search and didn't see it used in examples.

CDate(Format$([field1],"00\:00\:00"))

Ive attached the updated queries if they are any use to someone

Thanks again

 

[arnelgp]

...one question please what does the $ sign do in the code in Query1

All function ending with $ means the return value is a String.

 

[arnelgp]

i'll attach my query6 also if it is useful to anyone.

 

[chizzy42]

Hi Arnelgp, with regard to the query2

SELECT TimeValue(DateAdd("n",[num]*30,"1/1/1899 7:00")) AS [Time]
FROM tblNumbers
WHERE (((DateAdd("n",[num]*30,"1/1/1899 7:00"))<"1/2/1899"));

I was trying to figure out without success how to filter out the full hours that have had the x mins added to them , that is instead of the query result giving 700, 730,800,830,900.930 it would just return 730,830,930 etc....just curious if you have the time please

thanks