Access ADP - Date function today


I have a query in my Access ADP project where I want to check a series of dates who are in the future (including if they match today). I can not use now() or getdate() as they will look at the time as well.
If found a T-SQL function, to be able to check on the date only.. but it still does not work

When I use : WHERE     dbo.UserId.Date_removed >= GetDate() the query returns all future dates, but not the one who match today.


SELECT     dbo.UserId.Date_removed FROM dbo.UserId INNER JOIN
                      dbo.Department ON dbo.UserId.Department = dbo.Department.dep_id
WHERE     dbo.UserId.Date_removed >=
(SELECT     DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The query should definitely return what you need (future dates including today). I just wonder why are you doing the join here. Cause if you have a foreign key in UserId on Department column, then you should just check if Department is not null I guess:

SELECT     Date_removed
FROM        dbo.UserId
WHERE     Date_removed >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND          Department IS NOT NULL

In Access the date() function should return todays date.
Gustav BrockCIOCommented:
How about:

WHERE     dbo.UserId.Date_removed >= INT(GetDate())


WHERE     DATEDIFF(dd, dbo.UserId.Date_removed, GetDate()) >= 0

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

WHERE  ((CONVERT(VARCHAR(6), dbo.UserId.Date_removed, 112) >=  ((CONVERT(VARCHAR(6), GetDate(), 112)
In SQL Server you use the example below to format the date to YYYYMMDD which is universally accepted as a valid date and is the equivalent of using DATE() in access rather than NOW()

dbo.UserId.Date_removed >=CONVERT(VARCHAR(8), GetDate(), 112)

Cheers, Andrew

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITBeneluxAuthor Commented:
I tested all proposed solutions.. yours worked ... thx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.