query criteria

[josh_hat]

I have a query made that calculates when my equipment needs retested. I want to make the criteria so that only the equipment in the next 90 days shows up.

I have tried a couple of different ways but they are not working.

When I try =Date()+90 I get a mismatch warning.

If this doesn't make sense let me know. Thanks.

 

[dcx693]

People will also scold you for double posting .

 

[josh_hat]

This was a different question. I got the DateADD funciton to work. I didn't have the m in " ", yes I know I am a retard.

Now that I have the DateADD funtion working I can't get it to should just the ones due in the next 90 days.

Sorry for the double posting. I really appreciate your help.

 

[dcx693]

Sorry, sounded like the same question. You are not a retard!

If you're trying to set query criteria, try this:
Between Date() And DateAdd("m",3,Date())

 

[josh_hat]

When I type in...

Between Date() And DateADD("m",3,Date())

I get....


Data type mismatch in criteria expression

I'm I doing something wrong?

 

[dcx693]

The field that you are applying this criteria to, it is a date field, right?

 

[josh_hat]

Yes it is the field that I did the first DateADD function with. This field gives me a calculated date for the next date the equipment needs certified.

 

[dcx693]

Very strange. Works fine on my system. Maybe you should post that question to see if anyone has an idea! Sorry, I don't know what's wrong.

 

[realnine]

There is a built in function to calculate difference of two dates. Under the date field right click on criteria. Select build. You will find the functions under built in. Select date/time then datdiff.

Your final expression should look like this

DateDiff("y",[Your table name]![Date],Date())>90
The "y" selects the day of the year.
The next parameter is the name of your table and the field name that stores your date.
The last parameter is the todays date function.
The > 90 selects those records where the difference inthe two dates is freater than 90

HTH
Old Real

 

[realnine]

Josh_hat
I don't think I answered you question is direct way.

In your querry select the next empty column and right click on the top row, select build and type in this code

DateAdd("d",90,[Your Table]![Date])

"d" calculates days
90 is the number of days added
the last parameter is the name of your table and the date field you wish to add 90 days.

When you get back to you querry you will see Expr1(or higher interger). Be sure to check the box to show the dates.

HTH
Old Real

 

[josh_hat]

realnine,

I tried both ways that you suggested. Neither one is giving me is giving me just the customers that will be due within the next 90 days. If you have any other suggestions I would appericate it. Thanks.

 

[josh_hat]

What I have right now is a query set up that has a field that contains the last date a specific piece of equipment was last serviced, it is named "Last Cert". I also have field that has the frequency (in months) that piece of equipment needs serviced, it is named "Ferq". The other field is named "Due" and that contains a calculation that uses the "Last Cert" field and the "Freq" field to figure my next due date.

What I want to do is be able to make a report or a query that lists just the "Due" dates that are within the next 90 days.

I have tried all of the suggestions in the Criteria box and I can't get them to work. Is it because I have this set up wrong? I also tried to create a new query to do this and I have had no luck.

Sorry to keep dragging this out but I know it is just something little on my end that I am doing wrong.

Thanks.

 

[Rich]

Apply a filter to the form that's displaying date due or use an Iif statement to restrict DateDue

 

[josh_hat]

Rich,

When I apply a filter with the Iif statement what to I put in for the false value?

 

[Rich]

Null, then put Is Not Null in the criteria section for the field

 

[josh_hat]

Does this look like what I need?

Iif(Date() <> [Due2]![Due]+90,Is Not Null,Null)




Then put "Is Not Null" for the Criteria field?

 

[Rich]

No, it's more complicated than that, let's try the easy way for now, create a second query from your first, add Due to the grid and set the criteria for the field to <=Date()+90

 

[realnine]

josh_hat,
Because I will be doing some invoice overdue programing soon, I have followed this string closely. I made a table named duecert with one field a date field named duedate. I populated it with several dates ranging over 8 months to include some null entries.

I opened a querry on that table to list all duedates that are within 90 days from today. Of course it also listed those which are due in a few days and those that are overdue as of today.

Here is the criteria for the duedate field
DateAdd("d",90,[duecert]![duedate])<= Date()+"90"
It works and does what I think you want.
I am using Aesss 2002 but I belive these functions have been around for earlier versions.

This leads me to believe there is a problem with the calculated field you mentioned. Try this little exercise I referenced and maybe that will help pinpoint where your problem is.

HTH
RealNine

 

[Crilen007]

How can you get the 90 days thing in VB?

rstSQL = "Select * from qryInServLease where [LeaseDateEnd] < " & DateAdd("d", 90, Date)

Thats what I have right now..

 

[Crilen007]

Ah, got it.

Missed the "#"

Thanks