seperate data in one field

[cliff7376]

I have a table in a database where one field should equal 5 tables. for example in my field i have data like this 221-4950-907~2002/10/01 0:0:0^142#N/A the first part of this field until the ~ is my part number. the second part until the space is my required date. the other part after the ^ is my customer ID and the part after the # is my sales order number. I need to seperate these things into seperate fields so i can get extra information associated with them through more query's. What do i do?

 

[David R]

Not as bad as you fear Cliff.
Look up the InStr() function in Access help (I think it's more useful from the Access VBA help window for some reason).
InStr() will give you the position of each of those special symbols. Then you can use Left(), Right(), and Mid() to separate out the field pieces into separate fields.

Hopefully this is a one-time switch to fix existing data. If you're going to be importing data that will always be in this format, look into using an Import Spec (check the archives for help on this).

HTH,
David R

 

[cliff7376]

I have tried the instr function in my query to get the lenght of my string until that certain char "~" by using InStr(1,[PEG_Usekey],"~"), it isn't showing up anything in my select query. I know that is becouse the code is returning a number of what position the "~" occurs, not the data. If I could get one more push as to how i can put all the texti am trying to sepereate from this field out on a query i would really appreciate it. I don't know why our MFG system put all our planning data into one field but it is really irratating me.

 

[David R]

No problem. Something like this should work in a query (you can then use the query as the basis for your work if the data will be added _often_, or run an update query to change the data if it comes in once a day or whatever...

Expr1: InStr([Val],"~")
PartNo: Left([Val],[Expr1]-1)
Expr2: InStr([Val]," ")
ReqDate: Mid([Val],[Expr1]+1,[Expr2]-[Expr1]-1)
Expr3: InStr([Val],"^")
ReqTime: Mid([Val],[Expr2]+1,[Expr3]-[Expr2]-1)
Expr4: InStr([Val],"#")
CustID: Mid([Val],[Expr3]+1,[Expr4]-[Expr3]-1)
SalesNo: Mid([Val],[Expr4]+1,99)
(You can also use InStrRev() and Right() for the last one)

HTH,
David R

 

[cliff7376]

I am sorry i am so stupid. Where do i put this code. I am now putting it in the criteria field that i am trying to pick apart. I know it wont work that way but how do i Do it. I tried making another field in the query but it keeps asking me what table it belongs to and stuff. I am so confused.

 

[David R]

Not stupid at all, I suppose it looks like code but I just wanted to make it more readable.

These should be new fields in your query. Base your query on your table with the super-string data in it (221-4950-907~2002/10/01 0:0:0^142#N/A). I called that field [Val] because I didn't know what it was in your table. Rename as appropriate.
Then create the other bits, Expr1, PartNo, etc in BLANK fields of your Query Design. The part before the : will become a field name in your query when it's run. That's why I named the important ones PartNo, ReqDate, etc. Rename as appropriate.

Run your query and see if the result is what you want. Then use that query, not your original table, as the basis for any forms, reports, etc where you need that string broken into its proper pieces.

The other option is to create an Import Spec if you're getting this data in a comma-delimited file (.csv) or something similar. Then you can design the size of the fields and cut out the markers, as long as the fields are always the same size. They may not be.

Good luck,
David R

 

[cliff7376]

Thank you so much you saved my life. I am going to tell you the stupid thing i was doing. I was putting the name in the field and putting the instr() in the criteria not in the field section. I never did this type of thing before. Thanks again so much. You have been very patient.