Account Number Troubles

[jweeda]

Hi...im new to these boards and new to MS Access so Howdy ya'll.

Ok, I'll try to explain this as plain as possible.

Our billing system creates customer account numbers with seven digits (i.e. 0001500) The last two digits indicate how many times the account is deactivated and reactivated. So if someone moves out of the account above and someone new moves in, the new number would be 0001501.

Ok...here is my trouble. My mapping (GIS) system only uses the first five numbers (00015) because the number stays with the location forever. When I extract data from my map and pull it into Access, I can't get it to line up with the account numbers from billing. I need to remove the last two digits from the billing accounts and I don't want to do it manually because there are 8000 of them.

Any ideas?

Thanks in advance!

Jim

 

[FoFa]

numeric or string data?
Numeric: IntVar = AcctCode / 100

But since you have leading zeros also I am assuming it is string.
String: StringVar = LEFT(AcctCode,LEN(AcctCode-2))

You can use that in a query, report whatever.

 

[jweeda]

Well, it looks like we're getting somewhere! Only when I said I was new to Access, I really meant it! Where would I enter that string?

Thanks!!

Jim

 

[FoFa]

Depends on what you are trying to do. Are you trying to update a table/column with this information, join a table in a query, show it on a report, what?

 

[jweeda]

My apologies!

I imported this data into a table and I want to update the whole column in that table.

 

[FoFa]

To change the data, this is the basic SQL (Query)
UPDATE MyTable
SET MyCOl = LEFT(MyCol,LEN(MyCol)-2)

And that would remove the right two digits from MyCol for ALL the rows of data.
Of course you have substiute your table and column names, and if you do not want to do ALL the rows, you need some criteria.

 

[jweeda]

Ok...I tried that but it set everything to just 0.

*sigh...I wish I weren't a noob at this!

 

[jweeda]

Oh...would the fact that this is text have anything to do with that? I set the values to text instead numbers because of the leading zeroes!

 

[FoFa]

I was assuming text as LEFT is a string function.
So given your value is 000012300 in MyCol
You are trying to remove the last 2 zeros
LEN(MyCol) would return 9
LEFT returns the left number of characters specified by some value
so LEFT(MyCol,LEN(MyCol)-2) would appear like
LEFT(MyCol,9-2) OR LEFT(MyCol,7)
So it should return the left 7 characters, in this case 0000123
The update statement just applies that to the MyCol.

Can you paste the actual SQL you ran?

 

[jweeda]

I think Im missing something!

I go to the queries section, pick a new query, go to design mode and add my table. I go up to the query button on top and change it to an update query. Then I drage my ACCOUNT column down to the "table" below and where it says Update to:, I put this in,

SET ACCOUNT = LEFT(ACCOUNT,7(ACCOUNT)-2)

My account numbers are seven characters long.

I know Im missing something little cause now it says I have an invalid . or ! or parentheses.

 

[FoFa]

In the update too you should have:
LEFT([ACCOUNT],5)

If all of them are 7 and you want them to be 5

 

[jweeda]

LOL Told you I was a total n00b!

Thank you SO much for your help and your patience! I wish I could return the favor by helping someone else but I won't even begin to think that Im qualified! But kudos to this forum and to you Fofa! Thanks again!

Jim