searching date & time and picking right field (1 Viewer)

satwah

Registered User.
Local time
Today, 19:06
Joined
Aug 22, 2017
Messages
17
As Arnelgp says your data is incorrect the times in Summer day and Night are incorrect. They overlap.

Thanks Minty & Arnelgp.

I am bit slow in picking up this access, so please excuse me for that...

My Data or query has nothing to do with actual timeslot of the day i.e. to find out is it summer, day time or Winter ,evening time.

In my table or data I have fix date & fix time period ( hence Start date, Finish date , Start time and finish time is there). Once user enter any date & time I need to check two things only

1. Is the entered date between start date & finish date
2. Is the entered time between start time & finish time

If both are true then query will return me one row and I have another column in table called "energy factor" which has different value for different date & time range which I need to pick.

Please help me here. :banghead:
 

Minty

AWF VIP
Local time
Today, 19:06
Joined
Jul 26, 2013
Messages
10,355
I have corrected your data in your table to hopefully reflect what you actually wanted. Note that the EndTime has been changed to 23:59:59 as I suspect setting it to 00:00:00 was skewing the query.

See the attached database, which also has the query as described with a subtle change.
 

Attachments

  • Database1.accdb
    400 KB · Views: 51

satwah

Registered User.
Local time
Today, 19:06
Joined
Aug 22, 2017
Messages
17
I have corrected your data in your table to hopefully reflect what you actually wanted. Note that the EndTime has been changed to 23:59:59 as I suspect setting it to 00:00:00 was skewing the query.

See the attached database, which also has the query as described with a subtle change.

Thank you Minty for correcting my Data in table.
I ran your suggested query on Form with multiple scenarios.
It worked fine for all of them expect "Winter" "Night " times. for example 01/11/2017 02:00:00 am should give me one row but it brings back no row. I can't see anything wrong in query.
 

Minty

AWF VIP
Local time
Today, 19:06
Joined
Jul 26, 2013
Messages
10,355
That's because of the day boundary cross over with your times. 23:00 --> 07:59, makes perfect sense to us, but not an access time comparison, as those times are in different days.

You will probably need to write a custom function to take account of that.
 

satwah

Registered User.
Local time
Today, 19:06
Joined
Aug 22, 2017
Messages
17
That's because of the day boundary cross over with your times. 23:00 --> 07:59, makes perfect sense to us, but not an access time comparison, as those times are in different days.

You will probably need to write a custom function to take account of that.

Thanks - I go around it by splitting my row data in two rows.

thanks for help;
 

Users who are viewing this thread

Top Bottom