Manipulating string 1

[SHAWTY721]

I have a database field that I need to be able to dissect into two different parts. The first two digits or letters and the last four digits or letters. This is needed so that I will be able to determine which group and sub group a particular record should belong to. I was wondering if there was a easy way to do this other than me doing this manually for over 900 records.

 

[Remou]

You can use the Left and Right functions.

 

[SHAWTY721]

Thanks, I totally forgot about those two functions. Ok is there a way to I guess in a way map to which account and company a certain ID is going to based on what I get from using the left and right functions on a string that is imported into the database.

 

[Remou]

If I understand your post, it depends on where and how you want to do this, for example:

[tt]=DlookUp("Group","GroupTable","GroupID='" & Left(TextID,2) & "'"[/tt]

May suit on a form.

Or you may want to split the field to create two new fields, in which case an Update query may suit. There are several other possibilities.

 

[SHAWTY721]

If I was to use an Update query would I just have to create two new fields based on the output that is generated by the left and right functions

 

[Remou]

Can you give me an example of what you want?

 

[BobRodes]

Shawty, I think you might need to step back a bit from detailed questions about the solution you have in mind and describe more clearly the actual problem you're trying to solve. What do you mean by account, for example, and by company? What do you mean by "mapping"? etc.

 

[SHAWTY721]

I have a table that contains a list of codes that reference certain account numbers. I need to be able to have the appropriate code appear on a form based on the account number that is selected by the user.

 

[Remou]

You can use an unbound control and DlookUp, as illustrated above. However, it cannot be a good idea to have a field where each part means something different: you should split the field. If it is a key field, use the two new fields as a combined key.

 

[SHAWTY721]

I am planning on splitting the field, the field will be inputted into the database as a single field and then I plan on separting it into the two different fields I need using the left and right functions. I guess where I am having the hang up is after I separate the field creating two new ones so that I can store the appropriate data in the correct locations in the tables.

 

[Remou]

Can you please post an example of what you wish to do?

 

[BobRodes]

Yeah, spell it out for us. What table(s) are you using, what fields are in them, how are they related, etc.

 

[lespaul]

so you have a temporary table with the WHOLE data string. you have a table that you need to put the separated information into. Do you need to update a specific record that is already there or do you need to insert a new record?

INSERT INTO SomeTable (LeftPart, RightPart) (SELECT Left(Somefield, 2), RIght(SomeField, 6) FROM TemporaryTable)

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[BobRodes]

<Do you need to update a specific record that is already there or do you need to insert a new record?

Not sure...