split content of the field

[dbertanjoli]

I have a field in access database called result (coming from webform) and the content is something like: 'not much; 2' or 'frequently; 5'. How can I split numeric and text value into two separate columns in query?
I will greatly appreciate any help.

Debbie

 

[georgedwilkinson]

left(incomingstring,instr(incomingstring,';') - 1)

right(incomingstring,len(incomingstring) - instr(incomingstring,';') - 1)

is the general syntax. Since this is off the top of my head, you need to integrate/test/troubleshoot it.

 

[dbertanjoli]

many thanks for your reply. It works perfectly. The only problem is if original field is blank I get error message in new field (the rest is populated correctly). Is there a solution for this?

Many thanks,
Debbie

 

[boblarson]

Use the NZ function to capture and handle nulls.

 

[dbertanjoli]

I tried but it dosn't work. This is what I am doing:
Results: Left(Nz([QuestionAnswer],0),InStr(Nz([QuestionAnswer],0),';')-1)
Points: Right(Nz([QuestionAnswer],0),Len(Nz([QuestionAnswer],0))-InStr(Nz([QuestionAnswer],0),';')-1)

Any suggestions
Many thanks,
Debbie

 

[boblarson]

How about:
Results: Left(Nz([QuestionAnswer],""),InStr(Nz([QuestionAnswer],""),';')-1)
Points: Right(Nz([QuestionAnswer],""),Len(Nz([QuestionAnswer],""))-InStr(Nz([QuestionAnswer],""),';')-1)

 

[dbertanjoli]

Still doesn't work. In each emty field I get #Error

 

[georgedwilkinson]

How about:
Results: Left(Nz([QuestionAnswer],""),InStr(Nz([QuestionAnswer],";"),';')-1)

Might work. Again, off the top of my head, based on Bob's post. Just added the ";" defaults if the string was null. Should make the string length 0. Still thinking about the other. The same tactic won't work because you want the string length to evaluate to 0 if the string is null.

 

[dbertanjoli]

Still same error message. PLEASE HELP>
Deb

 

[boblarson]

Can you post the db in question

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Mike375]

You might find it easier to go with IIF

IIF(Fieldabc] Is Null,Null,your right() expression)

 

[dbertanjoli]

Thank you for your reply. I am attaching a database. The data is coming from the web based form. The query that I am having problem with is called 'User Query'. It is a web based medical survey and I am just trying to attach certain points to each answer. I wanted to create a separate table with points but couldn't manage doing it properly so, instead I added points to each answer in the value field and know I would like to split them. I know I made it complicated but didn't know better way.

Many thanks,
Deb

 

[boblarson]

Okay - here you go:

QAnswer: IIf(IsNull([QuestionAnswer]) Or [QuestionAnswer]="","",Left([QuestionAnswer],InStr(1,[QuestionAnswer],";")-1))

Points: IIf(IsNull([QuestionAnswer]) Or [QuestionAnswer]="","",Mid([QuestionAnswer],InStr(1,[QuestionAnswer],";")+1))

Also, your first record has a comma instead of semi-colon as the delimiter which makes the #Error come up. Fix that and make sure that a semi-colon is used and you are fine.

However, I don't know how the data wound up this way but it really should be stored in separate fields and/OR just the answer id used from the answer table.

 

[dbertanjoli]

Works perfectly, THANK YOU VERY MUCH.
Deb

 

[boblarson]