Update Query

[cbende2]

Hello all,



I'm having trouble with an update query. Here is an example of my table

ID(PK)	PlanYearBegin(text field)	PlanYearEnd (text field)
1		12/31
2		12/31
3		1/31
4		3/31

I am trying to make a query that takes [PlanYearEnd], and updates [PlanYearBegin] to (M+1)/(D+1)

So for record 1, the [PlanYearBegin] would be 1/1, Record 2 would be 1/1, record 3 would be 2/1, and record 4 would be 4/1

I tried using the MID function but was unsuccessful.

Any help will be appreciated!

[CJ_London]

try using the mod function, to get your numbers, use the instr function to find the position of the / character, then use the left and mid functions to determine the numerical value for M and D.

[cbende2]

For the mod function, the examples I'm looking at don't give me a good description of how I'm supposed to be using it. Basically I'm not sure the reason I'm using it. I understand the second part of your suggestion, splitting it up from the "/"

[CJ_London]

if M=12 then

M mod 12 +1= 1

Assuming your month ends are always month ends, then the D doesn't matter - it will always be 1 for the beginning

actually, thinking about it you don't need the left, instr,mid functions - you can use the val function
planyearbegin=(val(planyearend) mod 12) + 1 & "/1"

[cbende2]

I'm being asked for parameters for [Expr1] and [Expr2]??

[cbende2]

if M=12 then

M mod 12 +1= 1

Assuming your month ends are always month ends, then the D doesn't matter - it will always be 1 for the beginning

actually, thinking about it you don't need the left, instr,mid functions - you can use the val function
planyearbegin=(val(planyearend) mod 12) + 1 & "/1"

How does this work for months that aren't 12?

[DrGUI]

Try the following

Update tablename set PlanYearBegin = CStr(Format(DateAdd("d",CDate([PlanYearEnd]),1),"mm/dd"))

[CJ_London]

How does this work for months that aren't 12?

if M=3 then M mod 12 +1= 4
if M=6 then M mod 12 +1= 7
if M=8 then M mod 12 +1= 9

mod provides the remainder after dividing (by 12 in this case) - so 144 mod 12 = 0, 100 mod 9=1 etc

[cbende2]

Try the following

Update tablename set PlanYearBegin = CStr(Format(DateAdd("d",CDate([PlanYearEnd]),1),"mm/dd"))

I like this, any way to get rid of leading zeros? for example, I'd like it to read 1/1 instead of 01/01

[DrGUI]

Update tablename set PlanYearBegin = CStr(Format(DateAdd("d",CDate([PlanYearEnd]),1),"m/d"))

[CJ_London]

I'm being asked for parameters for [Expr1] and [Expr2]??

1. remove your three expr columns, they are not required
2. copy and paste the code I provided -- (val([plan year end]) mod 12) + 1 & "/1" -- in place of your [Expr1].... in the update to row. I see you use spaces (bad idea) so I've corrected here

[CJ_London]

with regards DrGui's suggestion, it can be improved - you don't need the cstr function since format returns a string anyway and use "m/d" rather than "mm/dd" to lose the leading zeros.

[cbende2]

1. remove your three expr columns, they are not required
2. copy and paste the code I provided -- (val([plan year end]) mod 12) + 1 & "/1" -- in place of your [Expr1].... in the update to row. I see you use spaces (bad idea) so I've corrected here

This has made every record 1/1, it didnt update the non 12/31 records correctly.

DrGUI's suggestion has worked.

Thanks to both of you for the help!