jpaquette90
New member
- Local time
- Today, 07:05
- Joined
- Nov 30, 2016
- Messages
- 2
Hello all, hoping someone can assist with my issue. This has been frustrating me to no end.
To give you some background, I use this database to house commission data. When we are ready to pay commissions for the period, I have a query that exports the data in the format and structure that we need. My issue is that the sales reporting lists the branches by a 4, 3, or 2 digit number. (ex. 2043, 305, 87). However, when we prepare the commission pay statements, in order for our payroll system to accept the branch, the office needs to be 6 digits long. The methodology is always the same, which is good. It always starts with 1 and then adds however many 0s it takes in order to bring the office number to 6 digits. (ex. 2043 becomes 102043, 305 becomes 100305, and 87 becomes 100087).
In order to accomplish that without having to perform an extra step after the file is exported. I have a query that adds leading 1 and 0(s) to the branch numbers. This is the formula I use.
IIf(Len([Revenue_Office]="4"),"10" & [Revenue_Office],IIf(Len([Revenue_Office]="3"),"100" & [Revenue_Office],IIf(Len([Revenue_Office]="2"),"1000" & [Revenue_Office],[Revenue_Office])))
This is supposed to look for the offices with 4 digits and add 10 to the front of it, the offices with 3 digits and add 100 to the front...etc...
When I run the query, all this formula does is add 10 to the front of every branch, regardless of how many digits the branch is. I am not sure what I am doing wrong or if this is the correct formula to use to go about this.
I would greatly appreciate any assistance. Thank you!
-JP
To give you some background, I use this database to house commission data. When we are ready to pay commissions for the period, I have a query that exports the data in the format and structure that we need. My issue is that the sales reporting lists the branches by a 4, 3, or 2 digit number. (ex. 2043, 305, 87). However, when we prepare the commission pay statements, in order for our payroll system to accept the branch, the office needs to be 6 digits long. The methodology is always the same, which is good. It always starts with 1 and then adds however many 0s it takes in order to bring the office number to 6 digits. (ex. 2043 becomes 102043, 305 becomes 100305, and 87 becomes 100087).
In order to accomplish that without having to perform an extra step after the file is exported. I have a query that adds leading 1 and 0(s) to the branch numbers. This is the formula I use.
IIf(Len([Revenue_Office]="4"),"10" & [Revenue_Office],IIf(Len([Revenue_Office]="3"),"100" & [Revenue_Office],IIf(Len([Revenue_Office]="2"),"1000" & [Revenue_Office],[Revenue_Office])))
This is supposed to look for the offices with 4 digits and add 10 to the front of it, the offices with 3 digits and add 100 to the front...etc...
When I run the query, all this formula does is add 10 to the front of every branch, regardless of how many digits the branch is. I am not sure what I am doing wrong or if this is the correct formula to use to go about this.
I would greatly appreciate any assistance. Thank you!
-JP
