run update query in autoexec

[Sally*]

Hi, I have an existing query that uses two tables INVOICES AND CUSTOMERS ('acno' in CUSTOMERS and 'account' in INVOICES as the link) to give me a list of customers who have not ordered in the last 18 months

I use:
Expr1: Max([INVOICES]![INVOICE DATE])<=DateAdd("m",-18,Date()) with -1 as the criteria and
Archive with 0 as the criteria.

I then go through the CUSTOMERS table to manually tick the archive field.

Firstly, I would like to have an update query that will update the archive field from 0 to -1, but I am getting an error message "Operation must use an undateable query"

Once this is solved, I would like to incorporate this update query into an autoexec macro so it runs on 1st of each month with datepart("d",date()=1)

Having trawled the internet, it looks like SQL would be the best way but would need to know how I would set it out(self taught in Access so little knowledge in SQL) .

Any help in any of these stages would be gratefully appreciated.

 

[Mr. B]

but I am getting an error message "Operation must use an undateable query

To try to solve this issue, keep both tables in the query and linked as you require them to be. Use the fields in the query you need for criteria but do not try to display them. Remove any fields that you do not want to update. In other words, get the query down to just updating the one field you need to be updated.

When you have resolved the updating issue then you can figure out a way to have it executed. It will not be a problem to have the query run, but you may need to have some place that you store the most recent date on which the update query was run. Then, part of the process will be to check that date to determine if the update query has been run in the current month, if not then the first time the database is opened when that date is not in the current month, the update query would be run.

Just my initial thoughs. Hope this helps.

 

[Sally*]

Hi, thanks for the advise. The original query only has two items, Archive and the MAX one. I have unticked the MAX option so it is no longer displayed, just leaving archive field.

I then open a new update query using the above query and select just the Archive item which I try to update to -1, but I am still getting the same error message "operation must use an updateable query"

I hope this makes sense

 

[Mr. B]

Sorry, but I did not pickup on the "Max" part of your query. That is the part that is causing you to have a non-updatable query. You have to remove that from the query.

 

[Sally*]

Is there another way of getting around the MAX? I need to update all customers who have not ordered anything in the past 18 months.

 

[Mr. B]

I would think that you would not need the Max at all.

Try using the following criteria for your Invoice Date field.
>=DateAdd("m",-18,Date()

That should return all record where the invoice date is greater than or equal to the current date - 18 months.

 

[Sally*]

Would this not list ALL invoices that are older than 18 months, including customers that are still ordering. I need to find all those customers who's last invoice was 18 months or older.