Solved

Where clause for today in a query

Posted on 2014-04-23
5
231 Views
Last Modified: 2014-04-23
My where clause that is suppose to bring up the records from the current day is not working. The where clause is:

WHERE (((DateValue(Nz([qryBookingDayswithYearIIIB].[DayRecorded],#1/1/1950#)))=DateValue(Now())) AND ((qryBookingDayswithYear.FirstDay)=[qryBookingDayswithYearIIIB].[DayRecorded]))

Open in new window


I am using a very similar where clause in another query that pulls up the records from the current week. This work perfectly. It is:

WHERE (((Format(Nz([qryBookingDayswithYearIIIB].[DayRecorded],#1/1/1950#),"wwyy"))=Format(Now(),"wwyy")) AND ((qryBookingDayswithYear.FirstDay)=[qryBookingDayswithYearIIIB].[DayRecorded]))

Open in new window


Is there a way I can change my first where clause for current day that would work properly. When a originally designed this query it was working properly... that was a month ago. Not sure why it is not working. Thanks!
0
Comment
Question by:cansevin
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40017585
The trick is what you said 'very similar'. Look at this section:

[DayRecorded],#1/1/1950#),"wwyy"))=Format(Now(),"wwyy"))

[DayRecorded],#1/1/1950#)))=DateValue(Now())

Make sure your date formats match or they will never return what you need.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40017596
Another thought. You can look at the data when the query was working. Maybe something changed in the way the data was entered.
0
 

Author Comment

by:cansevin
ID: 40017603
Jmiller, thanks for you help. At the risk of looking stupid... what do you think I should do to solve it? Is there I way I can make the first where look like the 2nd where an work for the current day?

Thanks for your help!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40017624
try this


WHERE (((Format(Nz([qryBookingDayswithYearIIIB].[DayRecorded],#1/1/1950#),"yyyymmdd"))=Format(Now(),"yyyymmdd")) AND ((qryBookingDayswithYear.FirstDay)=[qryBookingDayswithYearIIIB].[DayRecorded]))
0
 

Author Closing Comment

by:cansevin
ID: 40017775
Thanks!!!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question