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

[satwah]

Hello Gurus

I need your help in building a query which will be used in VBA code.
I have a table in which I have two Datetime fields and two other fields like this

Season Time_slot Season_St_Dt Season_Fi_Dt
Summer Day 26/03/2017 09:00:00 28/10/2017 00:00:00
Summer Night 26/03/2017 00:00:01 28/10/2017 08:59:59
Winter Day 29/10/2017 08:00:00 25/03/2018 23:00:00
Winter Night 29/10/2017 23:00:01 25/03/2018 07:59:59

When user input a date & time like 23/08/2017 10:00:00 am , I need to pick a single row based on input date and time slot it falls in.

I have tried multiple combinations of datevalue(Season_St_Dt) and also tried Timevalue function too.

It appears Datevalue or search on date only works fine to give me two rows but when time comes into picture then it goes in pear shape. I know it will be tricky to see when date / time changes after mid night as timing are different for day and night in summer vs winter.

Any help will be greatly appreciated.

 

[arnelgp]

Select [season time_slot] from your table where forms!forms!text box between season_st_date and season_fi_date;

 

[satwah]

Select [season time_slot] from your table where forms!forms!text box between season_st_date and season_fi_date;

Thanks Arnelgp - I have tried between clause already as a select query, it return the two rows as it is correct if only date is taken into account but it ignore time altogether hence I get two rows as output. Query I am using is

PARAMETERS [var_TimeSlot] DateTime;
SELECT T_DLAF.DLAF_Year, T_DLAF.DLAF_Season, T_DLAF.DLAF_Time_slot, T_DLAF.DLAF_Season_St_Dt, T_DLAF.DLAF_Season_Fi_Dt, T_DLAF.DLAF_Factor
FROM T_DLAF
WHERE [var_TimeSlot] Between [T_DLAF].[DLAF_Season_St_Dt] AND [T_DLAF].[DLAF_Season_fi_Dt]

 

[Minty]

You'll need to compare both the date and time separately, as each day between those times could be either day or night. To do that you will need to store the dates and times in each row separately.

 

[arnelgp]

PARAMETERS [var_TimeSlot] DateTime;
SELECT T_DLAF.DLAF_Year,
T_DLAF.DLAF_Season,
T_DLAF.DLAF_Time_Slot,
T_DLAF.DLAF_Season_St_Dt,
T_DLAF.DLAF_Season_Fi_Dt,
T_DLAF.DLAF_Factor
FROM T_DLAF WHERE CDbl([var_TimeSlot]) Between CDbl([T_DLAF.DLAF_Season_St_Dt])
And CDbl([T_DLAF.DLAF_Season_Fi_Dt])

 

[satwah]

Thanks Minty - I did try that also but when Time comes into picture, query doesn't work there.

 

[satwah]

Thanks Arnelgp - I have tried your query as mentioned above using CDbl function, but I still get the two row which indicate time is not taken into account.
any other thoughts on this or Am I doing something wrong here.

 

[arnelgp]

Chk the two rows are they overlapping or conflict date/time.

 

[arnelgp]

Time is taken into acct when you convert it to numeric.

 

[Minty]

Thanks Minty - I did try that also but when Time comes into picture, query doesn't work there.

So do you now have the table structure as below;

Season	Time_slot	Season_St_Dt	Season_Fi_Dt Season 	StartTime 		EndTime
Summer	Day		26/03/2017 		28/10/2017 	09:00:00		00:00:00
Summer	Night		26/03/2017 		28/10/2017 	08:59:59 		00:00:01
Winter	Day		29/10/2017 		25/03/2018 	08:00:00 		23:00:00
Winter	Night		29/10/2017 		25/03/2018 	23:00:01 		07:59:59

If not, it won't work.

 

[satwah]

I have check that as well.

If Parameter is 17/08/2017 11:11:11, this should return me one row with DLAF_Season as "Summer" and DLAF_Time_slot = "Day" But this is returning me as two rows for both Day & Night time slots as Date condition is true for both of them.
My Data in the time is like this.

Summer Day 26/03/2017 09:00:00 28/10/2017 00:00:00
Summer Night 26/03/2017 00:00:01 28/10/2017 08:59:59
Winter Day 29/10/2017 08:00:00 25/03/2018 23:00:00
Winter Night 29/10/2017 23:00:01 25/03/2018 07:59:59

 

[arnelgp]

i can only know that:
morning is from 0:00:00 up to 11:59am

afternoon 12:00 pm to 4:59pm

night 5:00pm to 11:59pm

 

[arnelgp]

try to analyze the query is returning the correct
data 2 rows. i can see it.

you need to break your record to many rows

summerday 26/03/2017 09 am 26/03/2017 04:59 pm
summernight 26/03/2017 05 pm 27/03/2017 08:59 am
summerday 27/03/2017 09 am 27/03/2017 04:59 pm
summernight 27/03/2017 05 pm 28/03/2017 08:59 am

 

[satwah]

Thanks But Requirement for timeslot ( day or night) is fixed for season ( Winter & summer) which is determined by date
So if User enter a date & time then I need to find it falls in which season and then check if time falls between "Day " or "Night"

Please point me how to achieve this.

 

[Minty]

If the time and dates are stored as separate fields then a simple query can be used, and you only need 4 rows.

SELECT Season, Time_Slot from YourTable
WHERE DateValue(forms!forms!textbox) between season_st_date AND season_fi_date AND TimeValue(forms!forms!textbox) Between StartTime And EndTime

 

[satwah]

Yes Minty , I had it earlier and then changed it. Now seeing your reply I changed it again exactly you have suggested above.

Please note that all field (Season_St_Dt, Season_Fi_Dt Season ,StartTime and EndTime) are DateTime type.

Query I have wrote is

PARAMETERS [var_TimeSlot] DateTime;
SELECT *
FROM CT_DLAF
WHERE
DateValue([var_TimeSlot]) Between [CT_DLAF].[DLAF_Season_St_Dt] And [CT_DLAF].[DLAF_Season_Fi_Dt]
AND
TimeValue([var_TimeSlot]) Between [CT_DLAF].[DLAF_Start_time] And [CT_DLAF].[DLAF_End_time

Parameter I am passing is 17/08/2017 10:11:29

Expecting output row as
Summer Day 26/03/2017 28/10/2017 09:00:00 00:00:00

Outcome of Query is Null i.e. No row Return

If I pass the Parameter as 01/11/2017 12:12:12 then getting two “Winter Season “ rows as output


Please suggest !

 

[satwah]

Exact Data is my table is

Summer Day 26/03/2017 28/10/2017 09:00:00 00:00:00
Summer Night 26/03/2017 28/10/2017 08:59:59 00:00:01
Winter Day 29/10/2017 25/03/2018 08:00:00 23:00:00
Winter Night 29/10/2017 25/03/2018 23:00:01 07:59:59

 

[arnelgp]

Hi minty, just analyze the data a date time can fall on two range date on her table.

 

[satwah]

try to analyze the query is returning the correct
data 2 rows. i can see it.

you need to break your record to many rows

Okay - I am going to break my data as you have suggested in Table and will revert back with my findings.

 

[Minty]

As Arnelgp says your data is incorrect the times in Summer day and Night are incorrect. They overlap.