Select range instead of specific

[cactuspete13]

This is a couple questions in one. In the database I am building, users need to be able to select both specific time and date for an event, but there are also occasions when users will not be able to specify an exact time and date.



Current setup: Reporttbl with DateofIncident and TimeofIncident fields (Date/Time data type obviously, I have them seperate for a reason). Several queries pulling that information for use in forms and reports. In the query, I have several fields set up to show day of week, month, quarter, and those are used in a report to count how many times events happen on each (how many events on Monday, in Febraury, in 2nd QTR).

What I am trying to get to, if possible, is allow the users to in put a range of dates and times for incidents. For example, if the exact date is not known, they can enter that it happened between 1 and 4 February, or between 11pm 1 February and 3am 2 February. Then I would need to find a way to count the start of those range groups (11pm and 1 February in the last instance) for statistics purposes.

So my question is, is this possible and how would I set it up? Thank you

[Rod]

This is a couple of answers in one.

... is this possible ... ?

Yes!

... how would I set it up?

Ah, the difficult one second. There are of course many ways to achieve what you want. For now, rather than leap in and give one solution that may not be in-line with your expectations, let me discuss a few principles.

Date/time data types are held as a number. The integer part is a relative day number and the decimal part is the time of day. Hence it is possible for you to add your date to your time of day to end up with a combined date and time. You may also add the user entered date to the user entered time. It is now much easier to compare two numbers than four (with a condition).

It is possible to 'drive' your interrogation of the data through VBA but you need to be comfortable with VBA and associated techniques. This is the way I would approach the requirement: soliciting the start and end dates via a form or input boxes; performing rudimentary validation via VBA; creating an SQL string; deriving a result set based on the SQL; and reporting back to the user via a form or report. It sounds complicated but is the easiest way - for me, anyway.

The other way to do this is to write some faily 'incestuous' SQL so that all the user has to do is run the query. The SQL prompts the user for four values: start date, start time, end date and end time. I'm fairly sure this is feasible but would need to prototype it to make sure. There is little or no opportunity for validation with this method but it does avoid VBA which for some people is a blessing.

Either method would make use of the Nz function so it's worth reading up on that - if the user does not enter the time then you need to force a time of 0.0.

Hope this helps somewhat. Get back to me with your choice of VBA or SQL and I'll try to be more specific.

[Rod]

I got interested! Here's a proof of concept for the SQL approach. It's a little crude and there is perhaps room for refinement. I've tested it a little.

MSAFcactuspete13.zip

Now going for a beer - quit while I'm ahead!

[cactuspete13]

My apologies, I think I may be leading you down a different road. Let me clarify. With the current setup, a user can say that an event happened at exactly 4:45 PM on 15 February 2013 (Rod - I'm keeping time and date different to make it easier for the users). What I'm trying to do is set it up so that a user can say that it happened between 4:00pm and 8:00pm and that is was somewhere around 15-20 December 2011. Then use the start of a date time range as the value used for statistical purposes, so 4:00 pm and 15 December respectively.

[Rod]

To do what you ask is actually easier than my original misconception. Try Query2 in the modified db.

MSAFcactuspete13.zip

It's crude! There's probably opportunity to enhance the SQL such that some rudimentary validation (formatting) is done. For example entering 16:00 is a time of 4pm but entering 16 will not result in 4pm.

Anyway test it out and let me know your opinion.

[cactuspete13]

- Rod: You are actually thinking a few steps ahead of where I am now, where the information about the events are already in the system. I'm working on getting the information into the system. I was thinking more about it today and I have a work around that I was trying to avoid it. I will keep this for later if I need to incorporate a search engine. Thanks for the help