Count within 24h but not one day (1 Viewer)

TheYoungOne · Dec 12, 2018

Hey guys,
I'm a newbie when it comes to access, so please keep your answers simple

I'm facing an issue with a query. I'd like to count how many releases have been made for one day, so within 24 hours. BUT, the new 'day' starts at 5 pm, going until 4:59 pm. :banghead:

I appreciate your help

All the best,
TheYoungOne

Minty · Dec 12, 2018

You can use criteria in a query with a Between construct. So to get everything from yesterday (based on your day explanation) you could use

Code:

Between  DateAdd("n",1019,(Date()-1)) AND  DateAdd("n",1019,(Date()))

Edit : Oh and welcome to AWF !

TheYoungOne · Dec 12, 2018

So far I've got this

Code:

SELECT Format([dbo_ORDER_HISTORY]![OTH_GenDate],"dd/mm/yyyy") AS Datum, Format([dbo_ORDER_HISTORY]![OTH_GenDate],"Short Time") AS [Time], dbo_ROUTE_GROUP_ASSIGNMENT.ROUG_RefNr AS [Route Group], dbo_ROUTE.CARR_SCAC AS Route
FROM ((dbo_ORDER_HISTORY INNER JOIN dbo_ROUTE_GROUP_ASSIGNMENT ON dbo_ORDER_HISTORY.OTH_ROU_RefNr = dbo_ROUTE_GROUP_ASSIGNMENT.ROU_RefNr) INNER JOIN NewWH_ROUTE_GROUP ON dbo_ROUTE_GROUP_ASSIGNMENT.ROUG_RefNr = NewWH_ROUTE_GROUP.ROUG_RefNr) INNER JOIN dbo_ROUTE ON dbo_ROUTE_GROUP_ASSIGNMENT.ROU_RefNr = dbo_ROUTE.ROU_RefNr
WHERE (((dbo_ORDER_HISTORY.OTH_Type)="REL") AND ((dbo_ORDER_HISTORY.OTH_User) Not Like "<unknown>"))
GROUP BY Format([dbo_ORDER_HISTORY]![OTH_GenDate],"dd/mm/yyyy"), Format([dbo_ORDER_HISTORY]![OTH_GenDate],"Short Time"), dbo_ROUTE_GROUP_ASSIGNMENT.ROUG_RefNr, dbo_ROUTE.CARR_SCAC;

What I need now is to Count the Route Group with the criteria I mentioned before

(one day starts 5 pm the day before and Ends at 5pm on this day)

isladogs · Dec 12, 2018

Posting to trigger email notifications

For info, your last two (duplicate) posts were moderated.
This sometimes happens for new users.
I've approved one & deleted the other.

If further posts don't appear, just use the' report a post' button below the bottom left of the post window. This will alert mods to follow up.

Please resist the temptation to send repeatedly. Thanks

Minty · Dec 12, 2018

On the end of your WHERE clause add

Code:

AND [dbo_ORDER_HISTORY]![OTH_GenDate] Between  DateAdd("n",1020,(Date()-1)) AND  DateAdd("n",1020,(Date()))

Note that this has the possibility of pulling in anything that occurred at Exactly 17:00 on both days. If that is a possibility we can change it use seconds as the date add portion of the calculation.

TheYoungOne · Dec 12, 2018

isladogs said:
Posting to trigger email notifications

For info, your last two (duplicate) posts were moderated.
This sometimes happens for new users.
I've approved one & deleted the other.

If further posts don't appear, just use the' report a post' button below the bottom left of the post window. This will alert mods to follow up.

Please resist the temptation to send repeatedly. Thanks

good to know

I thought thought I didn't post it in the first place

isladogs · Dec 12, 2018

No problem! Its an automatic 'feature' in the forum software related to blocking possible spam. Sometimes it gives false positives

The issue seems to go away once you've reached 10 posts or so

TheYoungOne · Dec 12, 2018

Minty said:
On the end of your WHERE clause add

Code:

AND [dbo_ORDER_HISTORY]![OTH_GenDate] Between DateAdd("n",1020,(Date()-1)) AND DateAdd("n",1020,(Date()))

What does the 1020 stand for? I added it, and the query ran but there where no results

I'll keep trying tho

Minty · Dec 12, 2018

The 1020 (minutes) adds 17 hours to the start date of yesterday. Which takes you to
11/12/2018 17:00:00 (UK time format - Yesterday 5:00PM)

The second part does the same for today
12/12/2018 17:00:00
So your query should return anything process "today" by your definition if you want it to work for "Yesterday" by your definition then change it to

Code:

Between  DateAdd("n",1020,(Date()-2)) AND  DateAdd("n",1020,(Date()-1))

I used minutes because in your original question you specified 16:59, and as explained you might have a need to have no overlap, hence possibly using seconds instead for more accuracy.

Count within 24h but not one day (1 Viewer)

TheYoungOne

New member

Minty

AWF VIP

TheYoungOne

New member

isladogs

MVP / VIP

Minty

AWF VIP

TheYoungOne

New member

isladogs

MVP / VIP

TheYoungOne

New member

Minty

AWF VIP

Similar threads

Users who are viewing this thread