Flagging up overdue dates

[Ella1981]

Hi there, I am tearing my hair out on this one as I know it must be easy but I am new to Access so am struggling.

I have created a query which works out a due date for a specific task. From this, I want to be able to run a query/report, say once a week, which only shows overdue dates so I know that this task needs doing.

I have asked the question previously and someone kindly came back with the expression: Date() > DueDate. I have tried this and the query runs with no errors but I don't get any results?! I have manipulated the data (this is a test database at the moment) so there should be results but I am just getting blank fields.

Can anyone advise on this with as simplistic explanation as possible (I won't be offended lol )

Thanks

Ella

 

[plog]

A few things to check:

In your table, what data type is DueDate? It should be a Date/Time field.

Where are you putting the expression Date()>DueDate? Could you post the sql of your query?

Remove that criteria from your query and run it--what do you get? Is it all the records of the table? It should be, if not your problem is bigger than Date()>DueDate.

 

[Ella1981]

Hi,

DueDate is a Date/Time field so I assume that's ok.

When I run the query I get all records so again I am happy with that.

I am sorry but how do I get the SQL of my query?? I told you I was new!!!

Ella

 

[Ella1981]

Is this what you need?


SELECT [Contract Details].[Start Date], DateAdd("w",2,[Start Date]) AS [First Contact Due], DateSerial(Year([Start Date]),Month([Start Date])+7,Day([Start Date])) AS [Second Contact Due], [NVQ Details]![NVQ Actual End] AS [Final Contact Due], [Personal Details].[First Name], [Personal Details].Surname, [Personal Details].[Address Line 1], [Personal Details].[Address Line 2], [Personal Details].[Address Line 3], [Personal Details].County, [Personal Details].Postcode, [Personal Details].[Telephone Number], [Contract Details].[First Contact Made], [Contract Details].[Second Contact Made], [Contract Details].[Final Contact Made]
FROM ([Contract Details] INNER JOIN [NVQ Details] ON [Contract Details].ID = [NVQ Details].ID) INNER JOIN [Personal Details] ON [Contract Details].ID = [Personal Details].ID;

 

[plog]

Sounds good so far, except those INNER Joins might be messing things up. Is the Due Date field in the [Contract Details] table or one of the others?

If it is in [Contract Details] you need to add a WHERE clause just before the ending semi-colon. Like this:

...=[Personal Details].ID WHERE Date()>[Contract Details].[DueDate];

Be sure to change [DueDate] to the actual name of the field in [Contract Details] you want to use.

If its not in [Contract Details], then that could be the explanation for no results. What table is it in and what relationship does that table have with [Contract Details]?