473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

ACCESS String Converstions

5
Hi guys, first post so please bare with me. Still learning and hope to learn lots from you guys. I've never done queries in VB on any of the MS packages ... but queries and things I know quite well.

Question...i have a database with Customer Names.
eg.
[CUSTOMER_NAME]
CP PRINSLOO

i want to properCase the field to read
CP Prinsloo

by using the STRCONV([CUSTOMER_NAME],3) ... this does exactly what i want but it unfortunately changes the second Initial of the customer to lower case!

Cp Prinsloo

its easy enough if the customer only has one initial, but what can i do to keep the second character of the customer's initials in CAPS?

please help?
Nov 19 '07 #1
10 2219
FishVal
2,653 Expert 2GB
Hi, Bricks.

You may use Split() function to get an array of substrings, then perform conversion on one substring (second or last ?) and then merge them back into string using Join() function.
Nov 19 '07 #2
Bricks
5
FishVal

thanks for the reply but please forgive my ignorance, like i said...new at this and learning...
went looking for those functions that you mentioned and found nothing ??
did a search on it and could not find it...is it called Split \ Join Function?
Nov 19 '07 #3
QVeen72
1,445 Expert 1GB
Hi Bricks,

This is the Query you are Looking For :

Expand|Select|Wrap|Line Numbers
  1. SELECT   UCASE(left(CNAME,INSTR(CNAME,' ')-1)) & ' ' &  STRCONV(MID(CNAME, INSTR(CNAME,' ') +1),3) FROM  MyTable
  2.  
In above Query, Replace CName with your FieldName Customer_Name

Regards
Veena
Nov 19 '07 #4
FishVal
2,653 Expert 2GB
To get a help on some function, open VBA window (Alt-F11) type elsewhere the name of function and press F1.

Here is example of code applying conversion to proper case on last substring in string passed as argument.
Expand|Select|Wrap|Line Numbers
  1. Public Function LastSubStringToProperCase(strInput As String) As String
  2.  
  3.     Dim lngLastIndex As Long
  4.  
  5.     lngLastIndex = UBound(Split(strInput, " "))
  6.     ReDim strSubStr(LBound(Split(strInput, " ")) To _
  7.                     lngLastIndex) As String
  8.  
  9.     strSubStr = Split(strInput, " ")
  10.     strSubStr(lngLastIndex) = StrConv(strSubStr(lngLastIndex), vbProperCase)
  11.     LastSubStringToProperCase = Join(strSubStr, " ")
  12.  
  13. End Function
  14.  
Nov 19 '07 #5
Bricks
5
hi thanks a mill guys, i'll go try it out and will let you know if i got it right.

i would like to try and learn mor about VB too coz i have no idea what Dim and those things mean too...but i'll get there, one step at a time...

thanks again
cheers
Nov 19 '07 #6
missinglinq
3,532 Expert 2GB
No one has asked yet, probably because we all know how quickly working with proper names can turn into a mare's nest, but how are your Customer_Names formatted? Are they always either C PRINSLOO or CP PRINSLOO? Or can it be CHARLES PRINSLOO or CHARLES P PRINSLOO as well?

Welcome to TheScripts!

Linq ;0)>
Nov 19 '07 #7
Bricks
5
yes it can be quite a mess and a headache too!
where the names are formatted to show C PRINSLOO then i am happy with the convertion and all is fine...but we have things looking like this in some cases

source1 (all in one field [CUST_NAME])
FRANKS I & FRANKS YH
KING AR & TJ
DICK MB & SAUNDERS GP
BREBNOR KN

source2 (two fields [INITIAL] [LAST_NAME])
INITIAL LAST_NAME
EJ CASEY
BJ WILLEMSE


we have different data sources in the bank and everyone does their own thing!!
Where source2 is concerned, i can handle that...but where we get data from source1 and teh like...PROBLEM!

but i need to make it standard somehow to have it put on letters.
i've tried the code that was supplied above, but i'm not having much joy, no fault to the code...i don't know how to use VB coz i've done all my things in the Design Queries till now. i'll hve to see if i can go one some sort of course for VB
Nov 20 '07 #8
QVeen72
1,445 Expert 1GB
Hi,

Did you try my Query...?
First use Select Query and check, f you find OK, you can Update whole column in one Go..



REgards
Veena
Nov 20 '07 #9
missinglinq
3,532 Expert 2GB
Source2 type data, as has been shown here, can be addressed a number of ways, but Source1 data is another story entirely! The possible permutaions are essentially limitless, and trying to parse them , in my opinion as well as the opinions of far greater masters of Access than myself, is simply a no go. A number of years ago, I was doing some temp work in dtat entry for one of the largest banks in the world. They had recently acquired another bank and were in the process migrating the customer records to their own system. After having a team spend 6 months and $200,000 trying to automate the reformatting of names and addresses, they finally gave up and instead hired teams of temps to manually re-enter all the data.

Linq ;0)>
Nov 20 '07 #10
FishVal
2,653 Expert 2GB
yes it can be quite a mess and a headache too!
where the names are formatted to show C PRINSLOO then i am happy with the convertion and all is fine...but we have things looking like this in some cases

source1 (all in one field [CUST_NAME])
FRANKS I & FRANKS YH
KING AR & TJ
DICK MB & SAUNDERS GP
BREBNOR KN

source2 (two fields [INITIAL] [LAST_NAME])
INITIAL LAST_NAME
EJ CASEY
BJ WILLEMSE


we have different data sources in the bank and everyone does their own thing!!
Where source2 is concerned, i can handle that...but where we get data from source1 and teh like...PROBLEM!

but i need to make it standard somehow to have it put on letters.
i've tried the code that was supplied above, but i'm not having much joy, no fault to the code...i don't know how to use VB coz i've done all my things in the Design Queries till now. i'll hve to see if i can go one some sort of course for VB
Hi, Bricks.

Does the following logic fit?
Substrings with:
  • length less or equal to 2
  • having non-alphabetical symbols
are not converted to proper case.

The rest are converted to proper case.

Regards,
Fish
Nov 20 '07 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Nicola | last post by:
Hi Everyone, I am new to programming and would like to know how to open an access Report from within vb 6. I am trying to write a program to organise cross stitch threads. I have found out how...
4
by: Fabian von Romberg | last post by:
Hi, I have installed Sql Reporting Services on 2 machines, one is WIN 2000 PRO and the other one is WIN 2000 ADV. SERVER. When I try to access a report using the webbrowser, I get the following...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
1
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this...
7
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I...
3
by: Jorge Cecílio | last post by:
Hi! I would like to export some MS-Access reports output to pdf. However, the only possibility offered by Access (afaik) for me to export formatted output is snp (snapshot) (I use MS-Office...
1
by: Microsoft News | last post by:
I have a web server sitting on computer 1, (QA1). Out on the network on my main server I have a folder with a zip file in it. All I want is for the web services that are on the web server to pick...
2
by: Dean Slindee | last post by:
Anybody written code in VB.NET to: 1) show a print preview window of reports already written and stored in an Access 2002 database; or 2) execute the print of a report stored in an Access 2002...
1
by: gm | last post by:
Hi; I have written a database that tracks all the installation we have ever done. I have a small heating company. I have recently started keeping a directory of digital photographs of the...
1
by: cpajoe2001 | last post by:
I am having an issue and after searching around online for a day and half now and finding others with the same problem but yet no solution to my issue I am looking for help. What i have is ServerA...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.