Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2012
    Posts
    14

    Unanswered: Query parameter from 6am-6pm

    Please HELP!!!! How do you make a parameter query data from 6:00 AM to 6:00 AM daily? What I meant is How to put it in the parameter query to include date and time?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I don't mean to be querrelous, but you called your question "6 am-6 pm" and your text says 6 am-6 am. Which is it?

    Besides, we can't help you if we don't understand the situation. Please copy and paste the SQL statement so we can see what you see.

    Sam

  3. #3
    Join Date
    Apr 2012
    Posts
    14
    Sorry, this is my first time to post and I don't know how to change the question. I have a query that collect the data every day from 6AM to 6AM next day due to 1st shift works from 6am-6pm and 2nd shift works 6pm-6am which passed midnight. My criteria for query everyday is for ex: Between #6/5/2012 6:00 AM# and #6/6/2012 5:59 AM# and it works. But I'd like to make a parameter query which will ask the user to enter date and time instead of having user changes date and time in the criteria every day. Any help will be appreciated.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I partially thought so. Forms collect data to put them into tables. Queries are not the tool to collect data.

    Create a form for adding data only. The form's RecordSource should be the table where the data reside. Add a textbox to the form tied to the correct field in the table. The Default Value (set it in the textbox's Property Sheet) should be
    Code:
    =Time()
    which will give you the time the form opens. You can modify the time if it's wrong. When you exit the form, save the info, and your data will be safe in the table.

    I admit that I'm confused, though. You say "it works." Perhaps you're doing something else other that just adding data?

    Sam

  5. #5
    Join Date
    Apr 2012
    Posts
    14
    Yes, I use Format my Date/Time Field to be mm/dd/yyyy HH:NN am/pm then use Default Value =Now() in my Table so anytime user adds record, date and time automatically there on my Form and Table. When I run Query with criteria Between #6/5/2012 6:00 AM# and #6/6/2012 5:59 AM#, it will give me data in that time interval. But Dates have to be modify every day in query design. I don't want the user to get into query design. Is there a way to put it in criteria like [enter Date and Time]? I tried and Query returns with no record?? Thanks for the quick response

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Please copy and paste the query's SQL statement so we can see it. We can't help you without it.

    Also, do you open the query from a form, or does the user go into the database window and open the query himself?

    Sam

  7. #7
    Join Date
    Apr 2012
    Posts
    14
    Currently they going to the database query design to change the date everyday then run the report out of it. Sorry I have no idea about getting the SQL statement. How do I get it?

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In the database window, open the query in Design view. In the left hand corner, you will see an icon that looks like a blue triangle. Click on the down-arrow next to it, and you will see an icon that says "SQL." Select it, and open the SQL window to display the SQL. You can copy directly from there, and paste it into here.

    Sam

  9. #9
    Join Date
    Apr 2012
    Posts
    14
    Ah sorry it took so long. Thanks for showing me this. Here it is:

    SELECT PCBRepairT.PCBID, PCBRepairT.PCBInDate, PCBRepairT.PCBScan, Left([PCBScan],11) AS PCBPartNumber, Right([PCBScan],12) AS Scan1, Left([scan1],6) AS PCBSerialNumber, Right([scan1],6) AS Location1, Left([location1],2) AS PCBLocation, Right([location1],4) AS PCBDateCode, PCBRepairT.PCBName, PCBRepairT.SignInBy, PCBRepairT.PCBFailDesc, PCBRepairT.ErrorCodeID, PCBRepairT.PCBTech, PCBRepairT.TechRepairDate, PCBRepairT.PCBRwkDesc, PCBRepairT.IsFixed, PCBRepairT.PCBMOCKTestPass, PCBRepairT.PCBMOCKTestFail, PCBRepairT.PCBStatusID, PCBRepairT.PCBOutDate, PCBRepairT.PCBRemark
    FROM PCBRepairT
    WHERE (((PCBRepairT.PCBInDate) Between #6/5/2012 6:0:0 AM# And #6/6/2012 5:59:0 AM#) AND ((PCBRepairT.TechRepairDate) Between #6/5/2012 6:0:0 AM# And #6/6/2012 5:59:0 AM#) AND ((PCBRepairT.PCBOutDate) Between #6/5/2012 6:0:0 AM# And #6/6/2012 5:59:0 AM#));
    Last edited by mdjet; 06-06-12 at 15:34.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try changing the WHERE statementL

    Code:
    WHERE (((PCBRepairT.PCBInDate) Between #6/5/2012 6:0:0 AM# And #6/6/2012 5:59:0 AM#) AND ((PCBRepairT.TechRepairDate) Between #6/5/2012 6:0:0 AM# And #6/6/2012 5:59:0 AM#) AND ((PCBRepairT.PCBOutDate) Between #6/5/2012 6:0:0 AM# And #6/6/2012 5:59:0 AM#));
    to

    Code:
    WHERE (((PCBRepairT.PCBInDate) Between Date() - 1 + 0.2500 And Date() + 0.2499) AND ((PCBRepairT.TechRepairDate) Between Date() - 1 + 0.2500 And Date() + 0.2499) AND ((PCBRepairT.PCBOutDate) Between Date() - 1 + 0.2500 And Date() + 0.2499));
    See if that doesn't update every day automatically.

    Sam

  11. #11
    Join Date
    Apr 2012
    Posts
    14
    Thanks so much, I will try tonight

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    asking users to input aq valid date and time is asking for trouble
    if they can screw it up they will

    Sam Landy's suggestion is good, providing you always run the query and want thee data for the day up to 06:00 today

    if it were me I'd do this using a form, validate the data there, make certain its sane and then use those parameters in a query, form or report
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by healdem View Post
    Sam Landy's suggestion is good, providing you always run the query and want thee data for the day up to 06:00 today
    It's also only a good suggestion (if I may say so myself) if you run a 365/366-day-a-year operation (which may be what healdem is alluding to). If you have any days that have no valid entries - whether they are Saturday, Sunday, or holiday, or even if an employee is absent for a day - your logic goes up in smoke. Your needs are probably much more complex than a simple query.

    Sam

  14. #14
    Join Date
    Apr 2012
    Posts
    14
    hmm Instead of date()-1+.25, I tried Date()+0.75 didn't work . I guess I have to specify yesterday first then start at 6AM. Anyway, Iran a test at work and it gave me data from 6/6/12 12:49AM to 6/7/12 5:30 AM

  15. #15
    Join Date
    Apr 2012
    Posts
    14
    My bad the start time was typo. It works perfectly. Thank you so much!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •