Yes I know this is the General baord, but nobody in query has helped.

[TYLER_DURDEN]

Ok, you all have helped me so much in the past, and I thank you for it. I've got a new one.

I have three dates in a querie. When you enter the first date (No Show Date), it automatically puts the third date (Due Date) at 30 days from then. The second date (Kept Date) is the date that hopefully the patient will come in for in between that 30 day time frame. What I need is for the query to notify me automatically of any individuals that do not meet the 30 day mark the third date. But I need the query to prevent notification of the 30th day if the individual had come in for the kept date, which is the second date. Hope this makes since, I have also added my databse which is only in the development stage.

I tried the criteria you listed above, but it wasn't what I was wanting. Hopefully the typo correction will make since.

Please help.

TD

 

[Fizzio]

Have you attached the correct Db? The one you have attached does not match your query problem!

 

[TYLER_DURDEN]

Oops, here it is

Here it is.

 

[Fizzio]

Try this.

You should not store the Due date in your main table as you can calculate it as you have done in the No Show query.

 

[TYLER_DURDEN]

Whoa!!! You rock!!!

Thank you so much!!!

 

[The_Doc_Man]

What I need is for the query to notify me automatically of any individuals that do not meet the 30 day mark the third date. But I need the query to prevent notification of the 30th day if the individual had come in for the kept date, which is the second date.

OK, I'm going to make up names for the fields. Call them Date1, Date2, Date3.

In your table, you need Date1 and Date2. Date3 only needs to exist in the query, and then only for display purposes.

Look up the DateAdd function in Help before you do this. It will help you understand what is being done.

In the query, you want the fields to include

- Date1 (from table)
- Date2 (from table)
- Date3: DateAdd( "d", 30, [Date1] )
- Overdue: (CDate(Nz( [Date2],"1-Jan-1900")) > [Date1]) AND (CDate(Nz( [Date2],"1-Jan-1900")) <= DateAdd("d",30,[Date1]))

I think I got the parentheses right. The colons, of course, identify the text just before them as query-field names. Which means that if you later open the query, those will be column names. And if you make a recordset out of the query, those will be the field names.

Date3 will be your 30-days-later date.

Overdue will be -1 (TRUE) or 0 (FALSE) to indicate whether they came in between Date1 and Date3. It presumes that Date2 is null if they have never come in before.

In a report, you can reference the query rather than the original table, so you could use IIf on the Overdue field, such as...

IIf( [MyFunkyQuery]![Overdue], "Overdue", " " )