Access ADP - Date function today

Posted on 2008-02-08
Medium Priority
Last Modified: 2013-12-05

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

Question by:ITBenelux
LVL 18

Expert Comment

ID: 20848673
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


Expert Comment

ID: 20848696
In Access the date() function should return todays date.
LVL 53

Expert Comment

by:Gustav Brock
ID: 20848764
How about:

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


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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 15

Expert Comment

ID: 20849027
WHERE  ((CONVERT(VARCHAR(6), dbo.UserId.Date_removed, 112) >=  ((CONVERT(VARCHAR(6), GetDate(), 112)
LVL 28

Accepted Solution

TextReport earned 1000 total points
ID: 20849122
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

Author Closing Comment

ID: 31429126
I tested all proposed solutions.. yours worked ... thx

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

599 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