Query to change all data

[justinwright]

I already have the query to append data and such, but I was looking to write one that would iterate through *all* of the entires in a certain table and add the next month to the beginning, ie:

It's August now, so running it now would change:



<description>

to

September <description>

in the DESC column of the table

In September it would say October, etc.

Anyone have any ideas?

[weekend00]

Do you really "add" every month so you will have "OctoberSeptemberAugust.....", or you want replace last month with new month like update "SeptemberXXXXX" to "OctoberXXXXX" instead of "OctoberSeptemberXXXXX" ?

Why don't add a field just fill with the month name. this is much easier to accomplish. if you want them together in report or form, just join them together.

[justinwright]

Well, they have to be entered into the database in the same field.

This is going to sound really stupid, but the idea is this:

Stored list of stuff with generic description
Press button, copies to temporary table
Press button, iterates through and adds the next month in front of it and concatenates with generic description
Press button and adds to main database, completely clearing said temporary table

I came up with the temporary table idea to simplify matters, but any better open I'm open to. It ports from a database with a generic reason and adds the next month name to the front and moves it into the main database, ideally.

[weekend00]

update tablename set desc="September"+desc

or

update tablename set desc=Format(DateAdd("m",1,Now),"mmmm")+desc

[justinwright]

So like

Dim myDateaddQuery as String
 
Dim response
response = "MsgBox("Are you suer you want to add to List database?", vbYesNo)
If reponse = vbYes Then
 
myDateaddQuery = "UPDATE TempList set DESC=Format(DateAdd("m",1,Now),"mmmm")+DESC
 
CurrentDB.Execute myDateaddQuery, dbFailOnError
 
Else
End If

correct?

(to update the desc's in the temporary list)

[weekend00]

Yes, I think it is correct.

But don't ask me, just test it in you database to see if it's correct.

Please mark it as solved if it is what you need.

[justinwright]

As I'm typing it, in the code:

"UPDATE TempList set DESC=Format(DateAdd("M",1,Now),"MMMM")+DESC

at the end of the line it gives me the following error:

Compile error:
Expected: end of statement

(Might I also add that I changed m to M because it needs to be in all caps, also what would I add to add a space between Month and Desc?)

[weekend00]

what would I add to add a space between Month and Desc?
Just add a space behind MMMM

the whole line should be: (be attention to the red chars.)
myDateaddQuery = "UPDATE TempList set DESC=Format(DateAdd('m',1,Now),'MMMM ')+DESC"

[justinwright]

Okay, that was certainly a dumb error on my part, and thank you for pointing that out.

At the moment, it isn't editing DESC though. It's duplicating all of the entries o_o.

[weekend00]

(It's duplicating all of the entries o_o. )
I can't believe it. update query never adds record unless you have other codes behind to add record when updating.

[justinwright]

Okay you were right, I was just not thinking apparently.

The code I added after the one that you showed me that was correct is

CurrentDB.Execute myDateaddQuery, dbFailOnError

And it's saying:

Run-time error '3144':

Syntax error in UPDATE statement

Any ideas?

[weekend00]

I tried and it worked good to me.

Please check the quot marks.
you can check what is in myDateaddQuery just before you run
CurrentDB.Execute myDateaddQuery, dbFailOnError.

[justinwright]

Hmm.... would it be simpler if I just had an input to specify? Ie a text box that said "Month of <thing>" and just had the user enter it? How would it be different?

[justinwright]

As a follow up, I'm still getting the "Run time error '3144' Syntax error in UPDATE statement" error. This is my code in it's entirety, I can't figure out for the life of me what's going wrong with it :\

Private Sub Command20_Click()
Dim myAppendQuery As String
Dim myDateChangeQuery As String
 
Dim response 
response = MsgBox("Are you sure you want to add to List database?", vbYesNo)
If response = vbYes Then
 
myAppendQuery = "INSERT INTO TemporaryList ( SITE, PDATE, PAMOUNT, [DESC], DESC1, ACHGROUP, DateAdded, DeductAdd, Initial) "
myAppendQuery = myAppendQuery & " SELECT MonthlyRentalsList.SITE, MonthlyRentalsList.PDATE, MonthlyRentalsList.PAMOUNT, MonthlyRentalsList.DESC, MonthlyRentalsList.DESC1, MonthlyRentalsList.ACHGROUP, MonthlyRentalsList.DateAdded, MonthlyRentalsList.DeductAdd, MonthlyRentalsList.Initial FROM MonthlyRentalsList"
 
CurrentDb.Execute myAppendQuery, dbFailOnError
 
myDateChangeQuery = "UPDATE TemporaryList set DESC=Format(DateAdd('m',1,Now),'MMMM ')+DESC"
 
CurrentDb.Execute myDateChangeQuery, dbFailOnError
 
Else
End If
End Sub

The bolded line of code above is what's highlight when I hit debug. any help is much appreciated, I feel like I'm so close to having this T_T

[weekend00]

please put brackets around DESC :

myDateChangeQuery = "UPDATE TemporaryList set [DESC]=Format(DateAdd('m',1,Now),'MMMM ')+[DESC]"