Date and time query

[casteverole]

Hi everyone,
I am brand new to access , so please be gentle.

My problem is that I need to produce a query to cover two dates within a timed range.

EG: from 01/01/2015 17:00pm TO 02/01/2015 07:00am

The table field contains both date and time.
I have no idea of how to do this, so if possible can you supply an idiots guide to follow.

I work for a security department in a local university and as we work 24hrs we need to make a report from one date through to the next morning.

any suggestions?

 

[Rx_]

Date and Time can have multiple formats, that is what can make the solution complex for a beginner.
If there happens to be a Date / Time data in a field, remember that the real data is actually being formatted to appear in a specific format. Or, it could be a string (text) that must be converted to a date-time format.

One trick would be to copy the same Date/time to another column.
In that column change the data type to Long Integer. This will indicate if your Date time is a numeric number being formatted. If your dates are numbers, then proceed. If they are just text (as string) then they must first be converted to a date/time data format.

If it is a number it may look like this: 42269.5097
The 42269 actually represents the number of DAYS sicne 1900
The .5097 indicated a half day plus a little more (e.g 12:15 after Noon)
42269.2500 would indicate 6AM while 42269.7500 would indicate 6 PM
This is the real data all date times are stored in. The Format can display them in many different ways (e.g. military, short date, long date, EU, US, ...)

The nice thing about numbers is how easy it is to subtract the smaller number from the larger one and find the difference in number of Days and the fractional amount.
42269.5000 - 42268..25 = 1.25 (days) or 32 hours

With this knowledge, google on Access time. You will probably find several tutorials and a lot of helpful tips.

If a Basic Query is all you need, be sure to enclose the values in the #
e.g. > #1/1/2014#
That way Access understands it is a date.

 

[MarkK]

To me this depends how you want to use that query, since to be useful you'll need to run it against different days all the time. So how do you feed in the day you want to query for?

I would try to construct criteria in the query design grid that prompt for a single date, something like . . .

   Field: YourDateField
   Table: YourTable
    Sort:
    Show: Yes
Criteria: >= DateValue([prmDate]) + TimeSerial(17, 0, 0) AND <= DateValue([prmDate]) + 1 + TimeSerial(7, 0, 0)

See what's going on in the criteria part there? We select dates >= our minimum date/time, and <= the max. So when you run this query, it will prmpt you for [prmDate], and it will then calculate the criteria for you, and select matching records.

Hope this helps,

 

[casteverole]

Hi Mark,
Thanks for the solution, I copied your answer into the query, and it does what it says on the tin.....Excellent.

I would never had achieved this, but where can I find info on how to learn this stuff.
I have never heard of a timed serial?

I had been trying... between[start date] And [end date].....but only ended up with only a single date of information.


I am so glad I joined,
Top stuff on the first post

 

[MarkK]

but where can I find info on how to learn this stuff.

Well, one useful resource are the forums here
But more seriously, check out the Object Browser. Open a code window and hit F2 or go to Main Menu->View->Object Browser. This thing might be confusing at first, but its essentially a view of your toolbox. It shows all the objects and methods that your project contains. So to find out what date/time functions are available . . .
1) Select "VBA" from the dropdown that contains "<All Libraries>"
2) Click "DateTime" in the Classes list.
3) Note the list on the right now shows all the members of the VBA.DataTime module, which you can right-click, and get help on. See TimeSerial() in there? And much more.

Cheers,
Mark

 

[casteverole]

Hi Mark,

Thanks again for pointing me in the right direction.
I have a lot of reading to do