Update Query from H*ll

[TravelerOn]

Hi all,

I posted a similar question in the VBA forum, but I've since simplified and refined to adjust this process.

I have a table that has a certain number of "trials" or records, with a field called "Priority" that designates (by sorting) how they should appear in a report. Hence, Trial #1341 has priority value #1, etc etc etc....I've created a select query that shows just the trial number and priority, and then an update query with the update to: [Priority]+1...so if a user makes a record (the table allows "temporary" duplicates, so that 1341 can be #1 and so can 1342) and needs to move all the other records to the next level of priority, they can do so.....HOWEVER, how can I exclude certain records from the update query? That is, the [Priority]+1 query will change all priorities to the next level, so that both trials marked #1 will change to #2, a #1 priority will cease to exist...and so on....I can make the adjustments behind the scenes, and so can the user on the form (go in and manually reenter 1341 back to priority #1)...but is there a better way? That is, if a user creates a new trial, assigns it #1, can I make a query automated to understand the [Priority]+1 applies to all records except that one?

 

[boblarson]

What you need to do is to create Criteria that is based on a different field. So you can specify which records get the update.

For example, if you had a date field which identified when each record was added, then you could include that field in the query grid and then under criteria place something like <#11/01/2001# to limit the records that get the update to all prior to November 1st.

Don't know what fields you have, but if you can find something which you can use then it will make it very easy to only update the ones you want.

Good luck!

BL
hth