I have a field in a form that contains the years that households have participated in my neighborhood garage sale (I'm a Realtor). So, that field contains for instance, 98,99,00 and now I want to append to the end of that field the year 01, through an update query, to several hundred records. I know how to run the update query and select the records to update, but what sort of code or format do I use in the update box so that the year that I am adding, 01, appends to the end, rather than replaces the years that are currently in the field. Also, if the field is empty (the first time the household had participated in the garage sale), I would want 01 to be entered in that field as well. Thanks. Hope someone can help.

First of all, let me say that the way you're storing the data is not a good way to do it. It violates the normalization rules of a relational database.

What you should do, is to have a separate Garage Sale table, which has the household key and the year participated.

So, in your main table, you would have a unique key that identifies that household along with other information. If that other information can have more than one value, then you should store it in another table and tie it via the primary key.

So, in your garage sale table, you would want to have at least two fields: HouseholdID and GarageSaleYear

That way, you can also run queries that bring the two tables together and select only those households that participated in a specified year.

It sounds like a bit of work, but I think you will find it to be better in the long run if you redesign it to fit the normalized pattern.

BL

You're right. It sounds like a lot of work. Too much for this Access rookie. I recently imported the data from a DOS based program, (Q&A) which I have been using for over 20 years. It is a great program but is not supported by Windows, so I reluctantly made the switch over. In Q&A I just did a "Mass Update" much like an Update Query, and had a little macro that went to the end of each field and put in a comma and the new year. I was hoping there was some simple code that I could do the same thing with Access. Apparently not. Thanks for the trouble of your response.

I agree with boblarson that normalizing your table structure is the way to go. I can't emphasize strongly enough the importance of this. It seems daunting at first particularily to people such as yourself who are not nor will ever be professional programmers. But in the long run, a normalized set of tables is much easier to work with.

To keep you going in the mean time, the appending of a string to the end of a string is simple in VBA also. Create the update query and in the UpdateTo line of the ParticipatedField put:

=[ParticipatedField] & ",01"

The square brackets are important. If you omit them, Access will assume that YourField is a text string and you will end up with "Participatedfield,01" as the value in the ParticipatedField.

Okay Pat, see if you've heard this one before: "I did exactly what you said but it didn't work." Well, I probably did something wrong, but after I did what you said, and then clicked on the Run Icon, a box pops up which says, Enter Parameter Value and then in smaller print below that it says Participated Field. I tried ignoring it (since I don't know what a Parameter Value is or why it wants it)and just clicking OK, but all that did was put ,01 in the fields that were blank, and it didn't do anything to the fields that already had some years in there. Then I went back and tried entering that whole code you gave me when it asked for Parameter Value, but that didn't work either. Where am I messing up?

Since I didn't know the name of YOUR field, I made one up. Use your own field name rather than "[ParticipatedField]".

Duh . . . Pat, there is one thing you must remember when responding to one of my questions, I am very very very dumb. I can just see you thinking to yourself now, I can't believe he took me literally and put in [ParticipatedField] . . . oh well, please excuse my ignorance. I have now done EXACTLY what you told me to do and it worked perfectly!! Yippee. Thank you so very very much.

It's one of those learning curve things...just be glad you're not on the Luge team, those curves are a killer. http://www.access-programmers.co.uk/ubb/biggrin.gif

Rereading your question made me spot something else: You'll need two update queries.
First one will have Is Not Null as a criteria for the [ParticipatedField], and use Pat's suggested format in the Update To: box. Run this one FIRST.
Then run another one, but change the criteria to Is Null, and the Update To: 01
Otherwise your new records will end up with ",01" as their value.

Hope that helps,
David R