How to change lowercase letters?

[dz2k7]

Now,
I have a big table with sales data downloaded from DB2.
We have branches codes of 1 digit only.
Because of too many branches we have branch codes in upercase and lowercase. So Q ans q are different branches.
I'd like to substitute all lowercase codes like q with something like q_ to make them searchable.
How I can substitute all lowercase letters in one column of the large table in one shot?

Or may be you know any solution how to make Access queries sensitive to the case.

 

[Mark-BES]

Don't hold me to this but I think you need to look at making an "Update Query"

 

[dz2k7]

I'd like to but how to make this Update query Case sensitive?

 

[Brianwarnock]

My skills are rusty but I think you are going to need to use the Chr function, eg if you put Chr(65) in the criteria only a will be returned not A which is Chr(97), I think, b is Chr(66) etc, not sure how you find them all.

Hope this helps

Brian

 

[Moniker]

Use the ASCII character codes to filter for only lower case characters. A is 65, but a is 97. Lower case z is 122. (Search 'Character Set' in Access Help for a complete table of codes.)

Assuming your branch codes are always in the format of [character][number] like A1 or a1, not 1A or 1a, then it becomes very easy. The update is just setting the field to this:

UPDATE BranchCode 
SET BranchCode=Left([BranchCode],1) & "_" & Right([BranchCode],1) 
WHERE Asc(Left([BranchCode],1))>=97 And Asc(Left([BranchCode],1) <=122;

The above will skip any branch code that doesn't start with the lower case letters a through z.

 

[Brianwarnock]

Sorry got that wrong just tested and even that doesn't work.

Brian

edit: Seeing Monikers reply perhaps it only works in VBA, I note that I got my numbers the wrong way round, I did say I was rusty.

edit2 forget that VBA nonsense I've sussed my error got the format all wrong, good job Moniker was on the ball.

 

[boblarson]

I did say I was rusty.

Need some WD-40?

 

[dz2k7]

Yahooooo!!!

It works

I just put

UPDATE wx AS sloc SET sloc.SLOC = sloc & "_"
WHERE (((Asc([sloc]))>=97 And (Asc([sloc]))<=122))
WITH OWNERACCESS OPTION;

The table name is WX
The fild name is SLOC

Thanks guys!

 

[Moniker]

There's no need to do that in VBA. In a query, you'd add the table you want to update, put the field that contains the branchcode in the grid, change the query type to "Update", and then "Update To:" would be this:

Left([BranchCode],1) & "_" & Right([BranchCode],1)

And the criteria would be this:

Asc(Left([BranchCode],1))>=97 And Asc(Left([BranchCode],1) <=122

Just replace "BranchCode" with whatever your field name is.

 

[Brianwarnock]

Need some WD-40?

LOL I need more than that, I should retire gracefully but he seemed to be getting nowhere, and hey I was on the right track.

Brian

 

[dz2k7]

UPDATE WX SET WX.SLOC = WX.SLOC & "_"
WHERE Asc(WX.SLOC)>=97 And Asc(WX.SLOC)<=122
WITH OWNERACCESS OPTION;

This is the right one

 

[Moniker]

Ta da! Glad it worked.