SELECT a specific DATE RANGE in Query

[taimysho0]

Hello, I only want my query to select this date range. In the pic attached, The first column is the ID column. The value for the ID should be the same for the whole picture. Ignore the "count of date" column. What im interested in is selecting the proper values in the "DATE" column.

The way it works is there should be 24 hour intervals for every day of the month, per ID.

However, I want the query to check STARTING from 1 AM all the way through the first hour of the next day. So in this picture, instead of selecting 24 hours of 5/1, i want it to only select 23 hours of 5/1 and one row of the first hour of the following day, so in this case it would be 5/2 (the row that is highlighted). It should ignore the first 5/1 at the top of the picture.

The reason is because in this example, "5/2/2012" is actually representing the midnight hour which is 00:00 but out machine converts it to the following day instead.

so again, query should spit out


5/1/2012 1:00:00 AM through 5/2/2012 and stop before 5/2/2012 1:00:00 AM


THANKS SO MUCH

[June7]

Maybe:

SELECT * FROM tablename WHERE [Date] > [input date criteria] AND [Date] < DateAdd("h", 24, [input date criteria]);

[taimysho0]

hey june, thanks for the input. which part is the "input date criteria"? and also for your "DateAdd", is that a variable i need to add in or that is an actual function? thanks!

[taimysho0]

My criteria for the date that gets picked up is anything from the 1st day of the current month to the current day (today). However, when i run the query with your setup, im getting an error where it cant find my "criteria" which is:

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date(),Day(Date())

[June7]

DateAdd is an intrinsic Access/VBA function.

I don't think the BETWEEN AND will get results you want because it is inclusive for the begin/end dates. I thought you wanted '5/1/2012 1:00:00 AM through 5/2/2012 and stop before 5/2/2012 1:00:00 AM'?

Missing closing parens:

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),Day(Date()))

But why DateSerial for the current date?

Between DateSerial(Year(Date()),Month(Date()),1) And Date()

[taimysho0]

hey june, i used the date serial because it will return may 1 through today (may 30), and from those results, i need to query if anything is under 24 rows from 1am to the first hour of the next day. so if i use your formula, what should i put for the "input date criteria"? is it still ok to use my date serial function? would that work for the "input date criteria" in :

SELECT * FROM tablename WHERE [Date] > [input date criteria] AND [Date] < DateAdd("h", 24, [input date criteria]);

Thanks! let me know if im being confusing.

[June7]

You want the whole month of data, not just a specific date?

Are you always going to run report at end of day on the last day of month? If you use Date() as criteria what happens if you run report on the 1st but really want the previous month of data? I had thought you would input a date criteria in a textbox and refer to that as input.

[taimysho0]

hey june, sorry if i was being confusing. In the pic above, all values under the "DATE" column will only display dates of the current month, which is May. What the query should output is 24 rows of each hour of the day starting from the 1st day of the current month all the way to the current day of the month (so for today, it should show up to may 31st) for ID 123456. Lets say for example the next ID you see when scrolled down is 123457, it should display the same number of rows (24) for every hour of the day starting again from the 1st of the month to the current day of the month (31st).


what i want is to filter any IDs from the given sample data of the current month that DOES NOT have 24 rows per day, or if its missing the whole day or days entirely.

The way i HAVE to count this is starting from 1am as the first hour of the day all the way to the first hour of the following day (which should be midnight as highlighted in the pic "5/21/2012") as long as the number of rows from this 1am to midnight of the following day equals a count of 24 rows, the query should NOT pick up that particular ID unless it is less than 24 rows for a different day.


and to asnswer your question, there is no form, report, or textboxes involved, just purely querying a massive table that has data for every hour of the day for every day of the month of every year for the past several years for thousands of different IDs.

This query will need to be ran on a daily basis to see what IDs do not have 24 rows of hourly intervals for the day prior or any day of the current month (this would be considered a gap, which is what this query is looking for; any gaps of missing interval hour of any day of the current month. Once the month goes to the next month, we do not care about the previous month, only the current month)


I hope this is more clear, sorry for any misunderstanding, thanks again June!!

[taimysho0]

Also, just to explain why i have the "between DateSerial(.." statement is because that is what i used to query the "DATE" column from the massive table. That is how i look at only dates for all IDs that fall within the current month (May). And from here, i need to query what is missing 24 hour intervals of any day for the month of May.

The tricky part is how to count from 1am of a given day to midnight of the following day and making sure the number of hour intervals between that range = 24.

[June7]

Wasn't that question of counting records to account for 24 hours addressed in your other post? https://www.accessforums.net/program...ows-25215.html

I thought you wanted something else in this thread. Duplicating questions in multiple threads is highly frowned upon.

[taimysho0]

yes you are correct, i did post about that, but I AM asking a different question. The way that person answered the question does not fit my actual needs, its close but not what i need. i need it to start from 1AM to midnight of the next day, and not from midnight of the current day to 11:00 PM of the same day (which is what the person in the other thread was giving the code for). I just wanted to explain the process of what i was doing above, it is the same process as in the other thread, but i need a different range, thats the difference. Let me know if that makes sense..?? Thanks

[taimysho0]

heres a pic to better understand:

[taimysho0]

you started off by providing this snippet:

SELECT * FROM tablename WHERE [Date] > [input date criteria] AND [Date] < DateAdd("h", 24, [input date criteria]);

which could solve what i need, but im just not sure where to put this, and what to put into [input date criteria], however my query is not pulling from any textboxes..

[taimysho0]

here is my SQL code if you think it might help:

This is for the first Query, which the Second Query is based off of:

SELECT tblMonthly_Link.[AMR AIS #], dbo_GAS_USAGE.GAS_READ_DATE, dbo_GAS_USAGE.GAS_UNCORR, Format([gas_read_date],"mm/dd/yyyy") AS [Month]
FROM tblMonthly_Link INNER JOIN dbo_GAS_USAGE ON tblMonthly_Link.[AMR AIS #] = dbo_GAS_USAGE.GAS_CUS_AIS
GROUP BY tblMonthly_Link.[AMR AIS #], dbo_GAS_USAGE.GAS_READ_DATE, dbo_GAS_USAGE.GAS_UNCORR, Format([gas_read_date],"mm/dd/yyyy")
HAVING (((tblMonthly_Link.[AMR AIS #]) Not Like "xx" And (tblMonthly_Link.[AMR AIS #]) Not Like "yy" And (tblMonthly_Link.[AMR AIS #]) Is Not Null) AND ((dbo_GAS_USAGE.GAS_UNCORR) Not Like "*null*") AND ((Month([GAS_READ_DATE]))=Month(Date())));




Query 2 (based off above)

SELECT qryMonthlyToGLDSLink.[AMR AIS #], Format([gas_read_date],"mm/dd/yyyy") AS [Month], Count(dbo_GAS_USAGE.[gas_read_date]) AS CountOfdtDate
FROM qryMonthlyToGLDSLink
GROUP BY qryMonthlyToGLDSLink.[AMR AIS #], Format([gas_read_date],"mm/dd/yyyy")
HAVING (((qryMonthlyToGLDSLink.[AMR AIS #]) Not Like "*xx*" And (qryMonthlyToGLDSLink.[AMR AIS #]) Not Like "*yy*") AND ((Count([dbo_GAS_USAGE].[gas_read_date]))<24))
ORDER BY qryMonthlyToGLDSLink.[AMR AIS #], Format([gas_read_date],"mm/dd/yyyy");

^ This second query is what i use as my master list to see what IDs have a row count of 23 or less.

[June7]

Query to group records requires a common value to capture the records into sets for an aggregate calculation to act on. It is easy to extract the common date part of each hour and group by that value but how is a group supposed to be defined that will include the 5/1/2012 dates (except for the first 5/1/2012 which is really 4/30/2012 midnight) and also include the 5/2/2012 which is really 5/1/2012 midnight? That is the conundrum you face and I don't yet see a solution. I get lost just trying to decode the posted sql statements. If you can provide a set of data for me to test with will make an attempt. I don't want to take time to build a table and enter data. A spreadsheet will suffice.

This is really a continution of the first thread and should have been additional posts to expand on the information of that thread.