Update table field

[BRANDON99]

Hi
I think this is a simple question but it is several years since I used Access. I have Access 2010. I have created a table into which I download my bank statements, named tblStatements. One field is “spent_at” another is “Group” I want to update the field” Group” depending on the contents of “spent_at” eg if “spent_at” contains edf in the text I want to update group to “Energy” and continues with all the other variation in “spent_at”. I do not want to do this in a form, I would rather updates are made in the table.
Thanks for your help, please can you fully explain as I really can not remember much about this!

 

[dhookom]

I would create a small lookup table that has a primary key of the imported Spent_At values with another field for you to enter the short description. Then just create a query that joins the tables.

Duane
Hook'D on Access
MS Access MVP

 

[BRANDON99]

Hi
Thanks for your reply, that seems like the easiest solution but I would really like to do this with VBA, I would like to get back into writting some code again

regards

 

[dhookom]

IMO, data belongs in tables and not in code. However, you can create and run a SQL statement in code like:

Dim strSQL as String
strSQL = "UPDATE YourTableNameHere SET Spent_At = 'Energy' WHERE Spent_At = 'edf'"
Currentdb.Execute strSQL, dbFailOnError

This seems like the wrong solution since you would either need to create and execute multiple SQL statements or create a small user-defined function that would accept any Spent_At and return its replacement.



Duane
Hook'D on Access
MS Access MVP

 

[BRANDON99]

Hi

Thanks again but I did not describe the downloaded data properly. EG the payment ref for EDF is "001007 Edf Pr Qe Clio Bnpp
*187801275289 23210 134 Edf Pr " but the last group of numbers change for each payment so what I need to do is just match the edf part of this. I do not know is this would be possible with a query using a lookup table ie just match the edf. The other payments follow a similar format.

thanks

 

[dhookom]

You can still create the table as I suggested and match on part of the downloaded field value. Is there other significant information that you should be providing?

Duane
Hook'D on Access
MS Access MVP

 

[BRANDON99]

Hi

Thanks for you reply. I had a look at it and I remembered how to do it. It's about 9 years since I last used access so I am a bit rusty.