Improve company productivity with a Business Account.Sign Up

x
?
Solved

Where clause for today in a query

Posted on 2014-04-23
5
Medium Priority
?
241 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 2000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
The best software application must always have an error handling tool
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

579 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