How can I continue a query based on date and time past a date change?

This is in the same Access project that my last question was from. I'm using a function in Access to count completed product from a table based on date, Product ID, and time. The problem is that our last production quarter on second shift is from 11:00 PM on one date to 1:00 AM on the next. How can I make the count continue from the first day into the next, or can I? There is a date field and a time field in the table instead of a single DateTime field. I can't change that as the database is part of a program written by a contracted software company for our production line.  I'm not sure how to manage the criteria on the date field either since the date is flipping from 11:59 PM to 12:00 AM. Am I going to have to do two seperate queries? One for 11:00 PM to 11:59 PM and one for 12:00 AM to 1:00 AM on the next day? If so, how can I show the total for these two in a single subform?

I'm teaching myself Access, SQL, and VB so I need all the help I can get. Thanks!
LVL 10
cjones_mcseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cjones_mcseAuthor Commented:
I'm increasing the points on this question. I've been working on this and stuck at this point for way too long. I think I could get it to work using some really complex and redundant coding, but I'd like to get it done right the first time or two.
0
Melih SARICAOwnerCommented:

combine date and time.. set it as a datetime field and thendo what ever u want..


Melih SARICA
0
Melih SARICAOwnerCommented:

datediff("n",startdate+starttime,enddate+endtime)

this Help u n param will show difference in minutes

Melih sARICa
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Gustav BrockCIOCommented:
You can use a selection criteria where you combine the two fields and calcute hours from midnight:

  PARAMETERS ProductNo Long, DateSelect DateTime;
  SELECT
    *
  FROM
    tblYourTable
  WHERE
    ProductID=ProductNo
    AND
    DateDiff("h", DateSelected, DateProduction+TimeProduction) Between 1 And 24;

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cjones_mcseAuthor Commented:
I can use the DateDiff function in the SQL statement for the Access query?
0
cjones_mcseAuthor Commented:
Let me give a little more detail as well...the output for this application is live data...as it comes off the line. So for example, the second shift 4th quarter begins at 11:00 P.M. on November 15th and end at 1:00 AM on November 16th. How can I write a SQL statement that pulls data from the table between the two times AND dates so that when a supervisor runs the application at 12:01 AM on November 16th, he gets accurate data. Here is the automatically generated SQL statement that currently pulls the data for 4th quarter. I only have it pulling from 11:00PM to 11:59 PM since I can't figure out how to handle the date switch.

SELECT     COUNT(*) AS fldOTLeft, dbo.tbl_Front_VisualInspect.fldDateInspected, dbo.tbl_Front_VisualInspect.fldTimeInspected
FROM         dbo.tbl_Front_VisualInspect INNER JOIN
                      dbo.tbl_Front_BackData ON dbo.tbl_Front_VisualInspect.fldBackBarcode = dbo.tbl_Front_BackData.fldBackBarcode
WHERE     (dbo.tbl_Front_BackData.fldOverallSeatPF = 1)
GROUP BY dbo.tbl_Front_VisualInspect.fldDateInspected, dbo.tbl_Front_VisualInspect.fldTimeInspected, dbo.tbl_Front_VisualInspect.fldBackBarcode
HAVING      (dbo.tbl_Front_VisualInspect.fldTimeInspected BETWEEN CONVERT(DATETIME, '1899-12-30 15:00:01', 102) AND CONVERT(DATETIME,
                      '1899-12-30 16:30:00', 102)) AND (dbo.tbl_Front_VisualInspect.fldBackBarcode LIKE 'E0%')


Let me reiterate that I can't change the Date and Time fields to one field because of programming issues with our data collection system. I can of course combine the two in an alias, but then I'm not sure where to go from there. Very much a newbie here.
I'm increasing the points to 250. Need to get this going ASAP.
0
cjones_mcseAuthor Commented:
Sorry...I just noticed that wasn't really a lot more detail.
0
pique_techCommented:
I'd suggest something like this:  Create another table called ShiftData that has the following fields:
Shift    Quarter    StartTime      EndTime
1         1            9:00:00 am   10:59:59 am
1         2            11:00:00 am   12:59:59 pm
1         3            1:00 pm        2:59:59 pm
1         4            3:00 pm        4:59:59 pm
2         1            5:00:00 pm    6:59:59 pm
2         2            7:00:00 pm    8:59:59 pm
2         3            9:00 pm         10:59:59 pm
2         4            11:00 pm       1:59:59 am
etc

Are you doing this in SQL as your query above suggests, or in Access?  That influences what comes next.
0
cjones_mcseAuthor Commented:
The DB is SQL, and I'm accessing the data and programming the forms in an Access project.
0
Gustav BrockCIOCommented:
> I can use the DateDiff function in the SQL statement for the Access query?

Certainly.

> How can I write a SQL statement that pulls data from the table between the two times AND dates
> so that when a supervisor runs the application at 12:01 AM on November 16th ..

Run the SQL I wrote with DateSelect as Nov. 15.

It shifts the "window" for a date from between midnight and midnight to between 01:00 AM and 01:00 AM.

/gustav
0
cjones_mcseAuthor Commented:
Sorry, nobody's provided an adequate response for my needs and I had to get this finished. I had to use some roundabout coding and two separate queries for 4th quarter, but I think I got it going. I guess I'll split the points up among everyone that tried to help.

Gustav, I think you had the right idea, but I just couldn't follow what you were telling me through to a conclusion. I'm giving you 100 since you gave the most detail about the query and I'm giving 75 each to the others.
0
pique_techCommented:
Sorry I didn't respond sooner, I've had a couple really busy days in a row.  

I returned to the idea I started above, and discovered it would be pretty complex to implement anyway.  So in the absence of any pressing need, I'll let it go for now.  Besides, I think there's promise in cactus_data's suggestion and it's certainly easier to deal with.
0
Gustav BrockCIOCommented:
I don't know what you have done but as I wrote, the query will get you the entries between 1 AM at the selected date to 1 AM the following day.
If you need data for one shift only, you can either create four queries where you adjust

   DateDiff("h", DateSelected, DateProduction+TimeProduction) Between 1 And 24;

to the interval you need. For the fourth shift it would be:

   DateDiff("h", DateSelected, DateProduction+TimeProduction) Between 19 And 24;

or add a parameter for the shift no.:

  PARAMETERS
    ProductNo Long,
    DateSelect DateTime,
    ShiftNo Long;
  SELECT
    *
  FROM
    tblYourTable
  WHERE
    ProductID=ProductNo
    AND
    DateDiff("h", DateSelected, DateProduction+TimeProduction) Between
      ((6*[ShiftNo])-5)
      And
      (6*[ShiftNo]);

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.