Solved

Where clause for today in a query

Posted on 2014-04-23
5
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

752 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