CODEWITHSUNDEEP
sqldatems-access-2010
Eska
Eska

Reputation: 201

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

Upvotes: 1

Views: 145

Answers (2)

Gustav
Gustav

Reputation: 55831

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#)

Upvotes: 3

Utsav
Utsav

Reputation: 8093

  1. You are putting date as d.m.yyyy but in code you are using d-m-yyyy. 

    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' );

  2. If you want, you can join both conditions together.

    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' )
      )

or (
    Format(AwaZam.dataZgloszenia, "d.m.yyyy") = '04.09.2015' 
    AND 
    ( Format(AwaZam.godzinaZakonczenia, 'hh:mm') BETWEEN '00:00' AND '06:00' )
    )

Upvotes: 0

Related Questions