Query to find records almost a year old from input date

[rrmando]

Hello all. I am a rookie with Access and am in need of some help. I am building a W-9 form tracking database. The fields are Vendor Number, Vendor Name, W-9 on File (yes/no), and W-9 Date.

I would like to create a query that locates all of the W-9s that are almost one year old, so that we can contact the vendors annually and ask them to submit a new W-9 form. I am thinking of a query that will let us know which records are 11 months (or more) older than the W-9 Date field.

The W-9 Date field is the date showing as 04/26/04 for example. I would like the query to show us 11 months or more from now which vendors need a new W-9 form on file. Thank you in advance.

 

[Norie]

Take a look at the DateDiff, DateAdd and Now functions.

Now will return the current date (and time)

DateDiff the difference between 2 dates in a period (day, month etc) specified by yourselh.

DateAdd will add n specified periods to a date.

You should be able to combine these to acheive what you want.

Perhaps SQL like this.

SELECT W9Table.Vendorname, W9Table.W9Date
FROM W9Table
WHERE (((W9Table.W9Date)<DateAdd("m",-11,Now())));

To create this in the QBE add your table, add the vendor name and W9 date fields.

In the criteria for the date put this:

<DateAdd("m",-11,Now())

 

[rrmando]

Thank you for the prompt reply Norie. You lost me at the end...did the message get cutoff? Can you please re-post? Thanks again.

 

[Norie]

It's because the less than operator is also used in the board's markup language.

I'll edit the post - did you understand the rest of it?

 

[rrmando]

You're the man Norie! Thank you so much for your help. If you were here I would hug you. Thanks again.

What is the SQL select, from, and where? Is that the SQL behind the query?

 

[Norie]

Yes it is.

If you want to see it goto View>SQL while in query design mode.

You shouldn't really need to use SQL, just use the QBE.

It's hard to explain queries without giving an example, that's why I posted the SQL.