Eska Eska - 6 months ago 31
SQL Question

MS Access, Query that selects * from db from 6AM to 6AM next day

I'm doing tons of approach to get this to work.
I need to generate reports based on table

dbAwarieZamkniete
.

I need to grab
ALL
from
dbAwarieZamkniete
, so when i select the
short date
from the text box in access form, based on that i want to run
query
that will grab data.

Let's say i need the records from 10.05.2016, that means i need records from 10.05.2016 06:00 till 11.05.2016 06:00.

My approaches were different, I was tried
union
for example, i won't paste all of them here, its just an example:

SELECT *
FROM dbAwarieZamkniete AS AwaZam
WHERE Format(AwaZam.dataZgloszenia, "d-m-yyyy") = '03.09.2015'
AND ( Format(AwaZam.godzinaZakonczenia, 'hh:mm') BETWEEN '06:00' AND '23:59' )

UNION ALL

SELECT *
FROM dbAwarieZamkniete AS AwaZam
WHERE Format(AwaZam.dataZgloszenia, "d-m-yyyy") = '04.09.2015'
AND (( Format(AwaZam.godzinaZakonczenia, 'hh:mm') BETWEEN '00:00' AND '06:00' );


dataZgloszenia contains the date (as a d.m.yyyy) - short date

godzinaZgloszenia contains the time (as h:m) - short time

Answer

Always handle dates as dates, not text, no exceptions.

So:

SELECT *
FROM   dbAwarieZamkniete AS AwaZam
WHERE  AwaZam.dataZgloszenia + AwaZam.godzinaZakonczenia
       Between 
           DateAdd("h", 6, #2016/05/10 00:00:00#)
           And
           DateAdd("h", 6 + 24, #2016/05/10 23:59:59#)

Using a date from a textbox:

SELECT *
FROM   dbAwarieZamkniete AS AwaZam
WHERE  AwaZam.dataZgloszenia + AwaZam.godzinaZakonczenia
       Between 
           DateAdd("h", 6, DateValue([Forms]![YourForm]![YourTextbox]))
           And
           DateAdd("h", 6 + 24, DateValue([Forms]![YourForm]![YourTextbox]) + #23:59:59#)