Access SQL Question

Factual Questions
1

Ok, I don’t really like doing this, but I have a question and can’t find the answer on the net anyplace.

I’m in the process of converting a bunch of Excel spreadsheets into database’s in Access. Somone sent me one yesterday, and they have one colum for both first name and last name. Like this:

Smith, John

Now my question is, I seem to remember coming across a referance someplace that hinted at a way to separate this into two fields in Access using an SQL query. I can put two field together with a Union Statement, but don’t know how to separate them.

Has anyone done this?..Or know of a great resource on the net that I can use?..While I know the company I work for has all the license agreements, getting any help from Microsoft for this level of support is difficult…(I’ve tried before. They are great for simple questions, but more complex stuff is beyond what you can find out).

Any help on this would be appriciated…if it’s too much of a pain to post the whole thing here, e-mail me and I’ll work out another way. Thanks all…

2

For the last name try: <pre>lName:left({Name}, instr({Name}, “,”) - 1)</pre>
First name: <pre>fName:trim(mid({Name}, instr({Name}, “,”) + 1))</pre>

As always, replace the curly brackets with square ones.

I haven’t actually tested this, but you get the idea and it should work. Also, note that it’ll only work if every names is formatted as described.

3

some fine tuning for Supernerd ideas:

If you have a space after the comma, then for the First Name, use “+2” instead of “+1”
If you have any entries without a comma, both ideas will gets errors. Rather than fix the formulas, it might be simpler to add commas to the few records which don’t have them.

4

The trim() will get rid of any excess leading spaces, so +1 should work fine. And, yes - if there’s a record with no comma the query will barf. You can get around that with an iif but it’s probably simpler just to add the comma …

5

Nerd, you are the man…thank you very much…What do you use as a Referance Guide?..I have a few, and they’re ok, but it seems like I ahve to hunt and peck to find truely advanced stuff. I don’t need another book telling me how to make a stupid form, I need one that helps out with SQL, or advance Macro’s…thanks again for your help…I’ll owe ya a beer sometime

6

Actually, I mostly use SQL in Visual Basic and my reference guides are pretty propeller head. But, I’ve found that the Access query designer is a cheap & quick way to write SQL - I let it work out the syntax and then cut and paste the SQL into my code.

Good luck. I’ll have the beer tonight and think about you.

7

You can do lots of cool things with strings inside of an Access query.

You can do lots of concatenating of strings and cutting out pieces and such.

With Access you can do many different string manipulations within the same string and it does not save it until you are done.

Example let say you have a field that has the phone number in the format of (999) 999-9999 and you want it with all the numbers strung together (i.e. 9999999999) you can also do the reverse.

You can do the following:

right(left([phone],4),3) & right(left([phone],8),3)& right([phone],4)

the & is a concatenation symbol and the right takes the rightmost number of characters and left takes the leftmost so combining them you can take the rightmost number of the leftmost number.

You can get really complex if you want to.

You can also do a lot with Access basic.

I learned it all from playing around with Access and the help system. I have never found a book that really shows a lot about this type of stuff.

Jeffery