A module to update records with variable Table Name and Field Name (1 Viewer)

[accessfever]

Hi,
I have 12 queries to update an Access table's one field based on 4 criteria. So basically, it is one month one query. Is it a way that I can change the 12 queries' codes to become just one module?

Below is the first and second query's SQL code:

1st query:
UPDATE [COST ACTUAL LOCAL CURR], COST_CURMTH_ SET [COST ACTUAL LOCAL CURR].TOTAL = 0, [COST ACTUAL LOCAL CURR].Oct = 0
WHERE (((COST_CURMTH_.CurMth_Day_Diff)<>0) AND (([COST ACTUAL LOCAL CURR].ACCOUNT) Like "7276001" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like "7926031" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like "7926100") AND (([COST ACTUAL LOCAL CURR].CurMonth)="10"));
2nd query:
UPDATE [COST ACTUAL LOCAL CURR], COST_CURMTH_ SET [COST ACTUAL LOCAL CURR].TOTAL = [Oct], [COST ACTUAL LOCAL CURR].NOV = 0
WHERE (((COST_CURMTH_.CurMth_Day_Diff)<>0) AND (([COST ACTUAL LOCAL CURR].ACCOUNT) Like "7276001" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like "7926031" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like "7926100") AND (([COST ACTUAL LOCAL CURR].CurMonth)="11"));

 

[Ranman256]

your db is designed wrong if you have fields labeled by month.
you should have had a date in a field called DATE, not fields: JAN, FEB, etc

Public Sub UpdateAllMonths()
Dim sSql As String, sMo as string
Dim i As Integer

DoCmd.SetWarnings false
For m = 1 To 12
sMo = format( m & "/1/15", "mmm")

sSql = "UPDATE [COST ACTUAL LOCAL CURR], COST_CURMTH_ SET [COST ACTUAL LOCAL CURR].TOTAL = 0, [COST ACTUAL LOCAL CURR]." & sMo & "= 0"
sSql = sSql = " WHERE (((COST_CURMTH_.CurMth_Day_Diff)<>0) AND (([COST ACTUAL LOCAL CURR].ACCOUNT) Like '7276001' Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like '7926031' Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like '7926100')"
sSql = sSql = " AND (([COST ACTUAL LOCAL CURR].CurMonth)=" & m & "))"

DoCmd.RunSQL sSql

Next
DoCmd.SetWarnings true
End Sub

 

[Gasman]

Accessfever has hard coded field names in the table?

The first query updates Oct field. The second updates Nov field?

I presume the others are the same.

I think his structure is all wrong for the table?, which is why he is having this problem?

Accessfever, I think you should have a record for each month with a field for month number, then you would easily be able to update the fields depending on what month you run it.?

Not a column for each month?

Public Sub UpdateAllMonths()
Dim sSql As String
Dim i As Integer

DoCmd.SetWarnings false
For m = 1 To 12
sSql = "UPDATE [COST ACTUAL LOCAL CURR], COST_CURMTH_ SET [COST ACTUAL LOCAL CURR].TOTAL = 0, [COST ACTUAL LOCAL CURR].Oct = 0"
sSql = sSql = " WHERE (((COST_CURMTH_.CurMth_Day_Diff)<>0) AND (([COST ACTUAL LOCAL CURR].ACCOUNT) Like '7276001' Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like '7926031' Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like '7926100')"
sSql = sSql = " AND (([COST ACTUAL LOCAL CURR].CurMonth)=" & m & "))"

DoCmd.RunSQL sSql

Next
DoCmd.SetWarnings true
End Sub

 

[accessfever]

The Access table has 12 month columns: Oct, Nov, Dec., ect. and a "Report Date" column along with other text columns.

What the queries are doing: If Current Report Date = 11 (Nov) and other criteria are met, then update the Nov column to $0 and leave other month columns unchange. If Current Report Date = 10 (Oct) and other criteria are met, then update the Oct column to $0 and leave other month columns no change.

The 12 queries above are straight forward to update one column based on the criteria (But actually I have some other update queries are much more complicated than this one. So I want to try this simple one first).

I will try the codes in a new modules and see how it works. Thanks!

 

[Gasman]

Don't go using the loop 1 to 12 then.
Calculate the month number in question and then use the rest of Ranman256's code.

Nice workaround the columns problem.

 

[Cronk]

sMo = format( m & "/1/15", "mmm")
Does that work?

I would have used
sMo =format(dateserial(2015,m,1),"mmm")

Incidentally, lines starting with
sSql = sSql = ...

should be
sSql = sSql & ...

 

[accessfever]

I put the SQL codes in a module but it didn't fly. May be I should better to use recordset to code in the module: the Access Table's name, the criteria ( 12 If-else-then?) then to update the corresponding month column?

 

[Gasman]

I put the SQL codes in a module but it didn't fly. May be I should better to use recordset to code in the module: the Access Table's name, the criteria ( 12 If-else-then?) then to update the corresponding month column?

Either debug.print or msgbox the sql string to see what is created.
Cronk has pointed out an error when attempting to concatenate the where clause.

 

[Galaxiom]

..... COST ACTUAL LOCAL CURR].ACCOUNT) Like '7276001'

One does not use Like for an exact comparison. Use equals.

DoCmd.SetWarnings False

The problem with this technique is that any problems with the query will be silently ignored. Much better to use:

CurrentDb.Execute sSql dbFailOnError

This will avoid the warnings about data being changed but will raise an error if the query is unable to complete its tasks.

 

[Galaxiom]

And yes, the data structure is wrong. You will be continually faced with clumsy workarounds and waste a lot of time as you continue to develop it. Better to bite the bullet now and restructure it.

 

[accessfever]

Thanks for the comments. The data by 12 month columns is downloaded from the SAP. Probably I didn't explain it clearly that the "Report date" column is actually the SAP date to run the data I added when an Excel macro is executed to format the SAP data like a table to feed into the Access Database. For example, if I enter 12/23/2015 then the Excel macro will populate the same date to the new "Report Date" column.

My original query tries to zero out the current month column if the Report date is not end of the month (Use another Access Table to check the day difference between the Report date and the last day of the month : COST_CURMTH_.CurMth_Day_Diff)<>0) AND the account numbers are 7926031 or 7926100 or 7276001. If the Report date is EOM (the last of the month) then don't do anything to keep the balance in current month column.

I changed the code to look for just one account number and added double quotes around the account number, the module works to zero out the single account's balance in Dec column.

However, when I changed the code to look for more account numbers then the module zeroes out all accounts' balance in Dec column instead of those preferred account numbers.... Not sure what's wrong with it...

The sSql line with account numbers are:
sSql = sSql = " WHERE (([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7276001"" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926031"" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926100"");"

 

[accessfever]

I think I figured out why it was not working. I should have one criteria one line to make it work:

Was:
sSql = sSql & " WHERE (([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7276001"" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926031"" Or ([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926100"");"

Changed to:

sSql = sSql & " WHERE (([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7276001"" )"
sSql = sSql & " OR (([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926031"")"
sSql = sSql & " OR (([COST ACTUAL LOCAL CURR].ACCOUNT) Like ""7926100"");"

Thanks everyone's comments!!

 

[accessfever]

Another question though. The module is working now. Then how can I include it in one of the steps in Macro... I am asking this since there are some queries have to be run first before the module. Then there are some queries have to be run after the actions in the module. I would like to have one macro include all steps instead of click 1st macro then run the module lastly click the 2nd macro to complete the whole process. Is it possible? Or I should convert the 1st macro and 2nd macro inside the module....?

 

[Galaxiom]

Guess you didn't understand my post (#9).

Like is for comparing similar strings.

eg WHERE whatever Like "123*"

Without the wildcard character (*) the Like becomes the same as using equals (=) except it is more expensive to run.

Using Like operator where it should be Equals will also give the impression that you are a complete novice to anyone who sees your work.

 

[accessfever]

It is my habit always use "Like" instead of "=" when it should be "=". Thanks for pointing that, I will change to "=" to have less execution time.