Using a query to change data in a field

[im4gltr]

I'm in a real crunch, and if you have time, you may want to look at my intro-I'm dumb, know very little about Access (any version-I have 2010) and I really need to replace and ampersand-&-in like a million records (I need not input the data) because I now know that Access doesn't it love it when it's used as text in a text field.....But I can't find out anywhere how to do it.....I've run the query to pull the records that have & in them, but I can't seem to write the criteria correctly to replace it with "and". Can anyone please help me----?????----Please....I'm begging.:banghead::banghead::banghead:
P.S. Just so you know, I'm not even in the realm of writing SQL....I'm using the drop down windows...

 

[John Big Booty]

OK so it sounds like you have created a select query. The next step is to create an expression that looks like what I have below;

and will return a result looking like;

Once you are happy that it is doing what you want, convert the select query to an Update Query, that should look something like;

When you run that query nothing will "appear" to happen, but when you check your table it should look like;

 

[im4gltr]

THANK YOU SO MUCH!!!!! I haven't tried it yet, but I will. I don't understand why there seems to be so many steps. I also never dreamed I needed to do anything except enter correct information in the "Criteria" area of the dropdown window. So confused. I don't like it when I don't understand the logic behind something. It just seems to me you should be able to just enter the correct commands in the "Criteria" area and be done with it.......That just proves how really sad I am when it comes to this process. Thanks again!!!!!

 

[John Big Booty]

You could certainly do it in less steps, but it always pays to step through these things, so as to test the logic you are using and make sure it is returning the result you want. As it is easy to create a query that does something that you hadn't expected, that you then have to try and recover from.

 

[im4gltr]

Ok, I understand and you're right-I need to learn it (crawling before running). I'll be working on it today. THANKS BUNCHES!!!! I am so grateful.

 

[im4gltr]

Hi.....I tried it I thought and then when I looked at the drop down window yours look different from mine....No wonder it didn't work for me. I must be on the wrong ribbon or something. And since I got this new computer, I can even capture a screen shot (or either I'm using the wrong keys-who knows). I'm about to get frustrated because when I saw your attachments, it made perfect sense and now nothing does.....I'm getting ready to try again. THANKS bunches.....I just can't figure what I'm doing wrong (probably everything:banghead::banghead::banghead...

 

[John Big Booty]

If you have a new computer, chances are it will have the Snipping Tool utility

 

[im4gltr]

Ok, got a screen shot. I don't know what I'm doing wrong. Now I can't get it in this reply.

 

[John Big Booty]

Did you start by creating a select query that collected the data you want?

 

[im4gltr]

I thought I did. But maybe that is the problem. I posted a reply late last night and I attached a screen shot, but I don't see it in the thread. I'm attaching it again. Thanks.

 

[im4gltr]

Maybe my problem is that I have already queried my database and selected all of the records containing an "&" in the "Title" field. So I was trying to just change the "&" in those records residing in my query from the "&" to "and". Is that the problem. Should I be performing a select query on the original table and not the query? Am I really dumb or what? And....I'm pretty sure I tried that too, but maybe that's where I didn't really use a select query.....I'm back to the drawing board. THANKS for everything.....I hope my next post says everything is great!!!!!
Stupid in Chesapeake

 

[im4gltr]

If I haven't confused you enough, you really will be now....
I queried the original database table and pulled all of the "&" records and built a new table with those records only......In the original not all records had a "&" in the Title field;ie., Mr. & Mrs., so I now have a table with just those millions of records. That is the table I was trying to perform the Select query on, but the dropdown window does not allow me to input the expressions you sent me....I made a screen shot of that and submitted it to you as an attachment. I am also attaching it now.....Wow-I know I don't explain it well, but hopefully this will.

Screenshot 4 is where I tried to actually type "record" in the field name so it would look like yours. If you notice my dropdown menus don't even look like yours with regard to size even. I hope you can please help me.....(And, oh, by the way, "Select" on the ribbon is highlighted.-I think)

 

[John Big Booty]

None of those queries has any criteria also what you put in your query has to reflect the reality of your DB not my sample.

 

[im4gltr]

Understood. Right. No wonder you're confused.It has taken me all this time to realize your first two screen shots are for the query (I think) that I had already done and I missed completely that the second set of shots are the "update" function. I couldn't get off the "select" function long enough to realize I had already accomplished the "select" query. I am SOOOOOOOOOOOOO incredibly sorry.
Ok, I think I've started correctly and I've attached screenshots so you can see what I get when I click 'run'.

THANKS for being so patient. I don't know what I'd do if you wouldn't help me. I've read the entire online help and I can't understand what's wrong.

Thanks again.

 

[John Big Booty]

In the Replace() function you need to change [Record] to [TITLE] :banghead:

 

[im4gltr]

Ok. Sorry. I'll try to find a forum for dumb beginners.

 

[John Big Booty]

Ok. Sorry. I'll try to find a forum for dumb beginners.

I think that's our mission statement

 

[im4gltr]

Thanks! But I feel so bad when I make this kind of a dumb mistake and it causes someone (Y O U) to bang your head. And then it's really bad when you realize just how stupid you are...I just want to learn really bad and I really do try to find the answers in books and online helps, but I guess I can't even formulate the question so that I can find it that way. Just so you know, I do realize how stupid I am, but I'm just a grateful. Thanks again!
I wish I had the time and the money to take formal hands on training, but right now, it just can't happen.
H A P P Y V A L E N T I N E' S D A Y!!!!!!!!!!!!!!!!!!!!!!!!!

 

[John Big Booty]

If you hang around here enough you will pick up plenty

 

[im4gltr]

I would like to do just that. I've learned a ton already! I ran the query and I got the correct results. Yea! Congratulations to you! Now I'm working on copying it to use against the other tables and I think I got the answer from this forum. I hope it works. THANKS FOR ALL YOU'VE DONE.......