Ad: Manage updates with the Download App
ie8 fix

Office & productivity software forum: Help with between dates and times needed

by: le456 September 6, 2012 4:50 AM PDT

Like this

0 people like this thread

Staff pick

Resolved question

Help with between dates and times needed

by le456 - 9/6/12 4:50 AM

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 his/her question.
Click here to view the answer that was selected.


le456 has chosen the best answer to his/her question!
Click here to view the answer that was selected.

Answer This
Ask for clarification
Report offensive post
Email to friend
Answers
Answer Best answer as chosen by user le456

Re: selection

by Kees_B Moderator - 9/6/12 3:03 PM

In Reply to: Help with between dates and times needed by le456

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


Was this reply helpful? (0)   (0)

Staff pick

Reply
Report offensive post
Email to friend

Thanks

by le456 - 9/7/12 12:34 AM

In Reply to: Re: selection by Kees_B Moderator

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!


Was this reply helpful? (0)   (0)

Staff pick

Reply
Report offensive post
Email to friend

I once inherited ...

by Kees_B Moderator - 9/7/12 12:42 AM

In Reply to: Thanks by le456

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


Was this reply helpful? (0)   (0)

Staff pick

Reply
Report offensive post
Email to friend

That sounds worse than my database..

by le456 - 9/7/12 3:25 AM

In Reply to: I once inherited ... by Kees_B Moderator

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


Was this reply helpful? (0)   (0)

Staff pick

Reply
Report offensive post
Email to friend

Re: trick

by Kees_B Moderator - 9/7/12 3:38 AM

In Reply to: That sounds worse than my database.. by le456

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


Was this reply helpful? (1)   (0)

Staff pick

Reply
Report offensive post
Email to friend

You are a genius!

by le456 - 9/7/12 3:46 AM

In Reply to: Re: trick by Kees_B Moderator

happy thank you for your help with this, it's much appreciated

Leah


Was this reply helpful? (0)   (0)

Staff pick

Reply
Report offensive post
Email to friend

Forum Icon Legend

  • UnreadUnread
  • ReadRead
  • Locked threadLocked thread
  •   
  •   
  •   
  •   
  •   
  •   
  •   
  • ModeratorModerator
  • CNET StaffCNET Staff
  • Samsung StaffSamsung Staff
  • Norton Authorized Support TeamNorton Authorized Support Team
  • AVG StaffAVG Staff
  • avast! Staffavast! Staff
  • Webroot Support TeamWebroot Support Team
  • Acer Customer Experience TeamAcer Customer Experience Team
  • Windows Outreach TeamWindows Outreach Team
  • DISH staffDISH staff
  • Dell StaffDell Staff
  • Intel StaffIntel Staff
  • QuestionQuestion
  • Resolved questionResolved question
  • General discussionGeneral discussion
  • TipTip
  • Alert or warningAlert or warning
  • PraisePraise
  • RantRant

You are e-mailing the following post: Post Subject

Your e-mail address is used only to let the recipient know who sent the e-mail and in case of transmission error. Neither your address nor the recipient's address will be used for any other purpose.

Sorry, there was a problem emailing this post. Please try again.

Submit Email Cancel

Thank you. Sent email to

Close

Thank you. Sent email to

Close

You are reporting the following post: Post Subject

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.

Offensive: Sexually explicit or offensive language

Spam: Advertisements or commercial links

Disruptive posting: Flaming or offending other users

Illegal activities: Promote cracked software, or other illegal content

Sorry, there was a problem submitting your post. Please try again.

Submit Report Cancel

Your message has been submitted and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.

Close

Your message has been submitted and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.

Close

You are posting a reply to: Post Subject

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to the CNET Forums policies for details. All submitted content is subject to CBS Interactive Site Terms of Use.

You are currently tracking this discussion. Click here to manage your tracked discussions.

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.

Sorry, there was a problem submitting your post. Please try again.

Sorry, there was a problem generating the preview. Please try again.

Duplicate posts are not allowed in the forums. Please edit your post and submit again.

Submit Reply Preview Cancel

Thank you, , your post has been submitted.

> Click here to view your post. > Manage your tracked discussions. > Track this discussion. Close

Thank you, , your post has been submitted and will appear on our site shortly.

Close

You are requesting a clarification of the question: Post Subject

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to the CNET Forums policies for details. All submitted content is subject to CBS Interactive Site Terms of Use.

You are currently tracking this discussion. Click here to manage your tracked discussions.

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.

Sorry, there was a problem submitting your post. Please try again.

Sorry, there was a problem generating the preview. Please try again.

Submit Clarification Request Preview Cancel

Thank you, , your post has been submitted.

> Click here to view your post. > Manage your tracked discussions. > Track this discussion. Close

Thank you, , your post has been submitted and will appear on our site shortly.

Close

You are posting an answer to the question: Post Subject

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to the CNET Forums policies for details. All submitted content is subject to CBS Interactive Site Terms of Use.

You are currently tracking this discussion. Click here to manage your tracked discussions.

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.

Sorry, there was a problem submitting your post. Please try again.

Sorry, there was a problem generating the preview. Please try again.

Submit Answer Preview Cancel

Thank you, , your post has been submitted.

> Click here to view your post. > Manage your tracked discussions. > Track this discussion. Close

Thank you, , your post has been submitted and will appear on our site shortly.

Close
close

Click here to be notified via e-mail when someone submits an answer.

Would you like to resolve this question? close

Based on your response, it looks like this question has been answered.



Sorry, there was a problem resolving this question. Please try again.

Resolve Leave unresolved