[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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!
0
cjones_mcse
Asked:
cjones_mcse
  • 6
  • 3
  • 2
  • +1
3 Solutions
 
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 SARICAIT ManagerCommented:

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


Melih SARICA
0
 
Melih SARICAIT ManagerCommented:

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

this Help u n param will show difference in minutes

Melih sARICa
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now