Can this be done?

[writteninr3d]

Is it possible for a single form control to populate two fields in a table?



The idea is, you enter a full name in the form separated ideally by only a space, just like a person's name would normally be written, and this populates both the first name and last name fields in a table...

[pbaldy]

You would have to code it yourself. It would be problematic anyway, given two-part first and last names, like Mary Ann Jones or James van Alder.

[writteninr3d]

Right. This sounds much more advanced than the level I'm at!

It's my first assignment ever using MS Access, and they're expecting me to use SQL, which I haven't even started learning, to do something I have no idea how to do!

I know I'm asking a huge favour I can't return, but would you know how to code it? I'm not worried about the two-part names.

[pbaldy]

This wouldn't involve SQL (unless you were using the form with an insert query or something, but unbound forms are not typically used by beginners). You would use string functions like Left(), Right(), Mid() along with the InStr() function to find the position of the space.

[ETCallHome]

As mentioned before in previous replies... you will have a problem when the persons enters Mary Ann Smith, but you could play with the following idea:

Declare a field as variant and then split the input field into the declared field. The assign occurence 0 to Name and ocurrence 1 to surname, see below:

dim x as variant
x = split (Full Name, " ")
Name = x(0)
Surname = x(1)

Bear in mind, if Mary Ann Smith is entered, you will see the following:

Name = Mary
Surname = Ann

Play with the above idea and you never know what you may come up with. I too started as a novice a few months ago, I now I am writing full code using SQL, DAO Recordset (Editing, Updating), queries, etc.

Earl

[Zipster1967]

Three part names are not that difficult to handle Just use the variant as previously stated then check for the number of occurrences. If you have three occurrences then you have three names and can combine the first two into the first name field (If you allow spaces in that field) then use the third occurrence in the surname field. Pretty easy.

[pbaldy]

If you have three occurrences then you have three names and can combine the first two into the first name field (If you allow spaces in that field) then use the third occurrence in the surname field. Pretty easy.

What if it's a two part last name rather than first name?

[writteninr3d]

You would use string functions like Left(), Right(), Mid() along with the InStr() function to find the position of the space.

Was able to successfully do this using an update query. It also involved finding the string length using LEN() and subtracting the value from InStr from that.

In order to make that work for people with more than one first or last name (separated by a space as shown above), is there a way to make an if loop so that if it needs 3 fields in the table it does 3, or if it doesn't it just does two? Straight away it is apparent you need a function to find the number of spaces and identify their separate locations with variables, and I don't know how to do that...

Declare a field as variant and then split the input field into the declared field. The assign occurence 0 to Name and ocurrence 1 to surname, see below:

dim x as variant
x = split (Full Name, " ")
Name = x(0)
Surname = x(1)

I'm assuming this would be entered in SQL view? As even though Microsoft forum says SPLIT() is a function in Access 2007 I can't find it in 2010 when I try to use SPLIT() in my update query. Plus I wouldn't have a clue where in the code to throw your statement in anyway...my bad...

[pbaldy]

The Split() function would be used within VBA code. I don't know that it could be used directly in a query. For your simple 2-name scenario, you shouldn't have needed Len(). Mid() with InStr() should have worked for the last name (if the length isn't specified for Mid(), it will run to the end).

Trying to break apart more than 2 names is more complicated than it first appears. You have the issue of 2-part first names vs 2-part last names as I mentioned earlier, as well as people entering "Doctor Bill Smith" or "John Doe Jr.". I once saw a function created by somebody that was pretty comprehensive, and looked explicitly for things like Van or Del and knew they were part of last names. It looked for Doctor or Junior or Jr. and knew what to do with them. I'll see if I can find it.

[writteninr3d]

Trying to break apart more than 2 names is more complicated than it first appears. You have the issue of 2-part first names vs 2-part last names as I mentioned earlier, as well as people entering "Doctor Bill Smith" or "John Doe Jr.". I once saw a function created by somebody that was pretty comprehensive, and looked explicitly for things like Van or Del and knew they were part of last names. It looked for Doctor or Junior or Jr. and knew what to do with them. I'll see if I can find it.

That sounds really cool. I will be especially looking for the part of it that talks about splitting to three...

[pbaldy]

How sad is it that I remember 5 year old threads? Here's the thread I remembered, specifically post 5 by blindman that has the function:

http://www.dbforums.com/microsoft-ac...ft-access.html

[writteninr3d]

Call it sad or whatever you want, thats pretty freakin' awesome!

"Everybody wants to rule the world"...well...I think you get the point.