Resolved Question

Help with between dates and times needed

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

Discussion is locked
le456 has chosen the best answer to their question. View answer
Reply to: Help with between dates and times needed
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: 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.

Best Answer

- Collapse -
Re: selection

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 -

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 ...

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..

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

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!
Happy thank you for your help with this, it's much appreciated


CNET Forums