Office & Productivity Software forum

Resolved Question

Help with between dates and times needed

by le456 / September 5, 2012 9:50 PM PDT

I have a query that I want to show only records with a datestamp between yesterday at 3pm and today at 3pm

I dont want to use a set date as I want to run this query automatically daily and email out the results.

My field is called MyDateVar and is formatted dd/mm/yyyy hh:mm:ss
(I'm using access 2003 - vintage i know!)

This is my sql so far - i'm not sure how to word the where clause:
SELECT tblMovLog.Field1 AS MyDateVAR, tblMovLog.Field2 AS [User], tblMovLog.Field3 AS Reg, tblMovLog.Field4 AS Logistics, tblMovLog.Field5 AS Dealer, tblMovLog.Field6 AS SaleType
FROM tblMovLog
ORDER BY tblMovLog.Field1 DESC;

Thank you for any help

le456 has chosen the best answer to their question. View answer
Answer This Ask For Clarification
Discussion is locked
You are posting a reply to: Help with between dates and times needed
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Help with between dates and times needed
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.

All Answers

Best Answer chosen by le456

Collapse -
Re: selection
by Kees_B Forum moderator / September 6, 2012 8:03 AM PDT

That's a (rather) simple selection. Just a programmers trick.
WHERE Int(DateAdd("h",9, [tblMovLog].[Field1]))=Int(Now())

But it's (very) bad practice to name your fields Field1, Field2, ...
Fields should have a descriptive name, that tells what they mean. Otherwise the application is unmaintainable, even by the author himself.
Makes me think you're not a programmer.


Collapse -
by le456 / September 6, 2012 5:34 PM PDT
In reply to: Re: selection

Thank you for your help Happy I'll test the query later on today.

You're correct I'm not a programmer, i'm an "analyst", I inherited the access database we use for reporting when the author left so I'm still trying to figure a few things out; he was no good with naming things correctly!

Collapse -
I once inherited ...
by Kees_B Forum moderator / September 6, 2012 5:42 PM PDT
In reply to: Thanks

an Access application with a lot of quite difficult calculations in nested queries.

One of tables used had a field with a name ending on %. So it seemed a percentage. But it was just a number. That made the difficult calculations nearly incomprehensible.

And the managements changed the rules every year. So I had to change the calculations every year.


Collapse -
That sounds worse than my database..
by le456 / September 6, 2012 8:25 PM PDT
In reply to: I once inherited ...

I'm glad i don't have a huge number of nested queries but i do have a few. I'm about to start a mini project to create the database from scratch in Access 2010 as it just keeps corrupting on me all the time and the author over complicated queries and forms making it really difficult to fix issues and so many redundant queries etc it's just a nightmare.

Can you explain how that little programmer trick works? It could be really useful for me going forward Happy

Collapse -
Re: trick
by Kees_B Forum moderator / September 6, 2012 8:38 PM PDT

First it add's 9 hours to the date. So the 24 hours from yesterday 3 PM to today 3 PM become the 24 hours today. Then we take the int to get rid of the time and keep only the date. That's the left side of the equation.
On the rights we have int(now). That's today.
And the boolean expression checks if the 'adjusted' date happens to be today.

So it's a way to say: select records that occurred at a time when it would have been today if it had been 9 hours later.


Collapse -
You are a genius!
by le456 / September 6, 2012 8:46 PM PDT
In reply to: Re: trick
Happy thank you for your help with this, it's much appreciated

Popular Forums
Computer Help 49,613 discussions
Computer Newbies 10,349 discussions
Laptops 19,436 discussions
Security 30,426 discussions
TVs & Home Theaters 20,308 discussions
Windows 10 360 discussions
Phones 15,802 discussions
Windows 7 7,351 discussions
Networking & Wireless 14,641 discussions


Having Wi-Fi troubles?

From the garage to the basement, we blanketed every square inch of the CNET Smart Home with fast, reliable Wi-Fi.