Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    24

    Unanswered: query date and time

    I need some help to set up a query for dates.
    In my forms (in the date field) I use =Now() so that the date is auto filed. This returns a value back to my table as 11/28/20137:30:27 PM.
    To pull data from specific time periods I have my query set up using this criteria Beginning[start date] and [end date].

    However it appears that criteria doesn't recognize my dates because of the times periods that is added to the date. When i just have a date such as 11/28/2013 my criteria Beginning [start date] and [end date] does pull the information but it won't pull the data if the time is added.

    Is there a way to make Beginning[start date] and [end date] recognize a date that also includes the time of the day?

    I apologize up front if Ive not been clear on this but if you ask Ill do my best to clear things up.

    Thanks,
    Don

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    Beginning [start date] and [end date]
    Beginning being what?
    do you mean:
    Code:
    between [start date] and [end date]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    24
    Between means to display the data between the two dates, start date and end date. I have this in a query. When I run the query a prompt box pops up asking for a "start date". I enter a start date hit enter and then another prompt box pops up asking for an "end date". I enter another date, hit enter and data is displayed. The data that is displayed is between the start date and the end date.

    The data I'm collecting is gathered using a form. In the forms "Default Value" field, I've inserted =now() which fills in the date but also records the time of day as well. When I run the query I mentioned above the "Between" code doesn't return anything because the time of day is part of the date.

    In the reports I plan to run it's important that I can report on "time periods". Possibly by month or at times maybe just for certain days. That's why I'm needing something like the "Between" code to pull that data.

    I'm open to other ideas of how to accomplish this but this is the only method I know of.

    I hope this explanation is helpful in understanding what I'm doing and trying to accomplish.

    Thanks again,
    Don...........

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so 'start date' and 'end date' are now parameters you enter at the time the query runs.
    I still dont' understand what beginning is.. its not a valid SQL or VBA function that im aware of.

    can we see the actual sql?
    providing you have specified start date and finish date correctly then you will get reprots

    correctly in this instance means start date is less then end date, both dates are valid

    I tend not to let users specify parameters at runtime, because you have no way of validating the values are correct, especially with dates. So I tend to collect parameters on a form called from the report switchboard/menu, which in turn generates the report.
    that means I can trust (a little, never ever wholly trust) user input.

    I suspect that this problem will be easier solved if you attach a copy of your db, stripped of all sensitive data, stripped of all none essential forms, reports and so on, compressed and then zipped and attached to a post
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    24
    Ok, Ive tried to do what youve asked, stripped down and zipped.

    I collect data using a form. I have 10 forms (for 10 different product lines) for this dept. Ive included one form in this example.

    In the end the main thing Im trying to do is create a report that will display a percentage of all 10 products checked (based on Lotsize and Totalchecked).

    However I may want to report on just one product in that same manner. Or I may need to report on PassFail (as you helped me with before).

    Once the data is collected Ill be able to report on it in many different ways but the main reporting will be date ranges of the percentage of all 10 products checked (based on Lotsize and Totalchecked.

    I hope this gives you a better understanding of what Im trying to do.

    Don..
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2012
    Posts
    24
    You are right about "between". I took it out of my query and the date range still works the same. I must have misunderstood when I was given some help.

Posting Permissions

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