[Joe West]

I am using a query to sort out some records, using specific fields. The query is then used as input to a report, which is really a form that I want to print, using certain records, selected by the query. All that is working fine. The challenge then is this:

Once I have selected the records and printed the reports for those specific records, I would like to update a field in each of those records, to set a yes/no field to yes. I use that field to select those records for the report. Lets say for instance that I am using the field called, "Was the letter mailed" which has a yes or no, true or false response. If the field is no, that record will be selected for the report. Then, once I have printed the letter, I want to turn that field value to a True value, so that it will not be printed again.

I thought the UPDATE SQL command would work, but I am having trouble getting it to work. I looked at the SQL statement for that query, and tacked the UPDATE command to the end. But it did not work, and gave me an error. E.G. Select fieldone, fieldtwo... from Customer
Where WasTheLetterPrinted = False
UPDATE Customer
SET WasTheLetterMailed = True;

The actual statement is much longer than that. The query works, and the report works great. But I would have to go back into the Table to set each of these fields manually to True, which I am trying to avoid. Can I use the UPDATE statement as part of the Query to do this? I tried to look this up in the ACCESS 2003 book that I have, and it is really confusing to me. Can anyone help with this? I will appreciate any assistance I can get. Thanks. :4-dontkno Joe

 

[Dragoen]

Hi Joe,

What you need to do is create an Update query that uses the exact same selection criteria as your select query that drives the report. You can't combine the update process within the select query. You could then create a macro to run the report and then immediately follow with running the update query to mark those records just printed.

hope that helps,

 

[Joe West]

Thanks Dragoen. I will try that. I kind of figured that out but had not tried it yet, but I was thinking along those lines. I guess that was good, yes! I will let you know if that works after I try it. Perhaps it may help others too. What I would really like to do is find an SQL program that I could use on the MS operating system. That would enhance my SQL education immensely. At 66 I don't have a lot of time to throw away, so I'm in a hurry, if you get my drift..ift..ift..
Thanks for posting that answer. I do appreciate it.
Joe