Dates and queries

[JQuinnC]

I need to show a subform on my mainform that will list items that are late.



I've made the query that correctly grabs the data I need, I just can't figure out how to make the criteria work the way I want. The relevant fields are:

sblReservationDate
sblNLPDocsReceived
sblNLPDocsSent

Basically, I want it to show me the DocsReceived query if we're more than 2 days past ReservationDate and DocsSent is null.

I have the criteria of DocsReceived as "sblReservationDate>=Date() + 2 And IsNull(sblNLPDocsSent)" (without quotes).

Am I doing this correctly?

[ranman256]

Date math is done with DateAdd & DateDiff
in your query,add a calculated field, DateDiff("d",[resDate],Date)
then filter it's criteria >1

[Micron]

I'm thinking the problem is that you're using a function in your criteria: IsNull(sblNLPDocsSent)
and that it should be sblNLPDocsSent= Null

[JQuinnC]

Ok, I think I have this worked out.

I set up two queries. One to determine when task is due and the other to determine if it's late.

To set when a task is due is simple. All the fields are set as dates, so due date is Due: [field] + x (where x is the number of days out I want a task due, which for me is based on the completely date of the last task).

To determine if a task is late, the code {<=Date()+2 And IsNull([sblTracking]![sblNLPDocsSent])=True} works.

So, if the date is past the due date, and no date has been entered on the next phase of the task, the query shows. Having the =true part of the IsNull function seems to have fixed the problem.