Creation of two fields from parts of another

[diakis]

In a field “NAME” I have “Adam Smith”.
From this field I want in a QBE window (not in VB) to make two new fields “FIRST NAME” “Adam” and “LAST NAME” “Smith”.
In Excel there are the functions FIND and SEARCH with which I take the number of the gap “ ” (5) between Adam and Smith and with the functions LEN, RIGHTS and LEFTS I have a result.
What could I do in Access 2003?

 

[Banana]

First, you shouldn't have a field named "Name" because Name is reserved for other uses by Access. This will certainly create problem with queries. Look up the list of reserved words and remember what they are.

Secondly, you can look up about concentrating query. That will answer your question. You wouldn't want to store both firstname and lastname into single field as well; as this will make search difficult. Look up on data normalization.

 

[Matt Greatorex]

The 'Split' function might be useful to you.

Failing that, you can use 'Instr' to find where the space character is, then use the 'Left' and 'Right' functions to construct the two strings yourself.

 

[diakis]

To Banana: the “NAME” was for example but I hadn’t thought that this would be a problem.
Thank you for the interest.
To Matt Greatorex: The use of “InStr” gave me a solution. I didn’t find the “Split”.
You helped me. Thank you.