Trying to update one column 1

[vba317]

I don't know why but I am running into a block with writing a update query in vba. I have a table called dbo_rpt_FYInfo. What I am trying to do is update 12 cells in the table. So I wrote a simple for next loop and copied and pasted the sql code into the module. My block is how to update the cell with the numbers 1-12. Any help would be appreciated.

Tom

For intX = 1 To 12

UPDATE dbo_rpt_FYInfo SET dbo_rpt_FYInfo.fyord = intX
WHERE (((dbo_rpt_FYInfo.uci)="APT") AND ((dbo_rpt_FYInfo.rptpd)>372));

Next intX

 

[PHV]

This is unclear what you want to do.
In the example given dbo_rpt_FYInfo.fyord will only have value 12 for all the rows selected.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

update 12 cells in the table" - do you mean 12 records in the table?
What's your PK in the dbo_rpt_FYInfo table?

Does this part:
[tt]
WHERE uci="APT" AND rptpd>372
[/tt]
apecify just one unique record?
Probably not, so what you are doing is updating fyord field 12 times and ending up with 12 in it.

Have fun.

---- Andy

 

[vba317]

Sorry, I was running reports till midnight last night. What I need is if rptpd =373 and fyord =1 then fyord gets updated to 2. If rptpd =374 and fyord =1 then fyord gets updated to 2. If rptpd =375 and fyord =1 then fyord gets updated to 3. All the way up to rptpd=384 and fyord =1 then fyord gets updated to 12.

Tom

 

[PHV]

Something like this ?
strSQL = "UPDATE dbo_rpt_FYInfo SET fyord=rptpd-372" _
& " WHERE uci='APT' AND fyord=1 AND rptpd Between 373 And 384"
DoCmd.RunSQL strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

That's either a typo or your logic requirement:
[tt]
if rptpd =37[red]3[/red] and fyord =1 then fyord gets updated to [red]2[/red]
If rptpd =37[red]4[/red] and fyord =1 then fyord gets updated to [red]2[/red]
[/tt]

Have fun.

---- Andy

 

[vba317]

it is a typo if rptpd=374 and fyord =1 then fyord gets updated to 3.
if rptpd=375 and fyord =1 then fyord gets updated to 4.


Tom