Solved

need criteria to pull data from today and yesterday in microsoft access

Posted on 2011-03-16
3
533 Views
Last Modified: 2012-05-11
Hi Experts,

I have a combo box with a rowsource of

SELECT [Scrap Ticket].ID, [Scrap Ticket].[Date Shift Run:], [Scrap Ticket].GLPart, [Scrap Ticket].[Material Type:], [Scrap Ticket].[Tube Description:]
FROM [Scrap Ticket]
WHERE ((([Scrap Ticket].Operator)=[Forms]![Switchboard]![txtUserName]))
GROUP BY [Scrap Ticket].ID, [Scrap Ticket].[Date Shift Run:], [Scrap Ticket].GLPart, [Scrap Ticket].[Material Type:], [Scrap Ticket].[Tube Description:]
HAVING ((([Scrap Ticket].[Date Shift Run:])>=Date() And ([Scrap Ticket].[Date Shift Run:])=Date()-1))
ORDER BY [Scrap Ticket].[Date Shift Run:] DESC;

which i thought would pick up all entries for this user between yesterday and today so that the third shift will still be able to access their data entered on that shift even after the date changes at midnight.

Is something wrong with my criteria.....

Date() AND Date()-1

Thanks
Laura
0
Comment
Question by:linbayzak
3 Comments
 
LVL 5

Accepted Solution

by:
DoveTails earned 500 total points
ID: 35149873
May want to try using only the greater than or equal to date minus one:

HAVING [Scrap Ticket].[Date Shift Run:]>=Date()-1)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35149877
And ([Scrap Ticket].[Date Shift Run:])=Date()-1))

These rules are impossible to meet.  If the date must be equal to yesterday it cannot also be greater than or equl to today.

Unless you have records going into the future then  all you need is..
HAVING [Scrap Ticket].[Date Shift Run:]>=Date()-1
0
 

Author Closing Comment

by:linbayzak
ID: 35150649
perfect, thanks :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now