Office & Productivity Software

Resolved Question

Help with between dates and times needed

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

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

Kees

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

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

Kees

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.

Kees

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

Leah
Popular Forums
icon
Computer Help 47,885 discussions
icon
Computer Newbies 10,322 discussions
icon
iPhones, iPods, & iPads 3,188 discussions
icon
Security 30,333 discussions
icon
TVs & Home Theaters 20,177 discussions
icon
HDTV Picture Setting 1,932 discussions
icon
Phones 15,713 discussions
icon
Windows 7 6,210 discussions
icon
Networking & Wireless 14,510 discussions

Tech Tip

Know how to save a wet phone?

It's not with a dryer and it's not with rice. CNET shows you the secret to saving your phone.