2 big problems

[daveUK]

Hi Guys

I'm designing a database for a local uiniversity reprographics department and have 2 problems with one of the tables. The table in question is the Staff table, which has a primary key (Autonumber) and the staff name. There are approx. 1000 entries in the table. I've imported the names from an Excel spreadsheet that I managed to get from the Finance Dept.

Problem 1 - The names are a mixture of upper and lowercase. Is there any way to change them all to either upper or propercase?

Problem 2 - (This is the big one) Both the firstname and surname have been input in one cell in the spreadsheet, which meant that they are now in one field. Is there anyway to 'split' this either in Excel, and I'll re-import the data, or in Access?

Thanks for your help

Dave

 

[daveUK]

Managed to get problem No.2 sorted by using Data - Text to Columns within Excel and the importing the info into an Access table.

Still haven't got Problem No. 1 sorted though

 

[Mile-O]

For problem 1, use the StrConv() function.

 

[daveUK]

Hi Mike

Thanks for the reply. I've been trying to use the StrConv() function in an update query, but it doesn't seem to work. In the 'update to' query field I have StrConv(Surname, 3), so that all of the entries in the field 'Surname' will be converted. Unforntunately, everytime I move from the 'update to' field Access inserts speech marks around the 'Surname', so it appears as StrConv("Surname",3). All this does is change every entry to 'Surname'.

Any ideas? I'm using Access2K with Win XP Pro if that makes any difference.

Thanks
Dave

 

[dcx693]

If Surname is the name of your field enclose it in brackets like this:
StrConv([Surname],3)

 

[daveUK]

Thanks guys

 

[IgorB]

Problem 2:

Use combination of built-in functions InStr and Len.