Convert text to number 1

[lmn]

What is the protocol for converting text to a number. For survey purposes I have 1,2,3,4,5,N/A - so if it's a # - I convert it from text to the number and if it's N/A I make it null (for averaging) - but I'm not sure how to make the text format a number.....

Anyone know this offhand??

Lisa

 

[judgehopkins]

Where do your users input these numbers?

Text boxes? Check boxes?

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[lmn]

They enter them into the survey.

The field (score) is currently formatted as text so I can accomodate N/A scores and subsequently remove them from averages in the queries/reports.

So they'd enter 1,2,3,4,5 or N/A. They are entering the # as text.

 

[Rolliee]

a text number in a textbox is generally treated as text or number. To be rigorous, you should use the function Val( ) to convert to a number and the function str( ) to convert or treat a anumbere as text.

Is that what you wanted to know?

rollie@bwsys.net

 

[judgehopkins]

Rolliee, do you have a sample of such a function, i.e., to convert text (e.g., "1&quot into the numeric value of 1?

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[Rolliee]

Judge,

I just set up a form with a textbox named txtPlay and a command button. The following code, with the number 2 in the textbox.

Private Sub Command18_Click()
MsgBox VarType(Me.txtPlay)
MsgBox Str(1.235 * Val(Me.txtPlay))
End Sub

Produces a vartype = 8 indication it was indeed a type Text and the answer to the next msgbox was 2.47. Try it out str() and val() are smart functions. It will not work if the text is "two2" as some Basic functions will but this one does what is intuitive.

rollie@bwsys.net

 

[lmn]

Hello,

The val() seems to work (Csng() also works) - but what neither of those do is become useful for me other than visually in my first step. Let me explain.

This is my SQL statement:
SELECT TEST_Surveys.SurveyID, IIf([quest1]="N/A",Null,Val([quest1])) AS Q1, IIf([quest2]="N/A",Null,Val([quest2])) AS Q2, IIf([quest3]="N/A",Null,Val([quest3])) AS Q3, IIf([quest4]="N/A",Null,Val([quest4])) AS Q4, IIf([quest5]="N/A",Null,Val([quest5])) AS Q5, IIf([quest6]="N/A",Null,Val([quest6])) AS Q6, IIf([quest7]="N/A",Null,Val([quest7])) AS Q7, IIf([quest8]="N/A",Null,Val([quest8])) AS Q8, IIf([quest9]="N/A",Null,Val([quest9])) AS Q9, IIf([quest10]="N/A",Null,Val([quest10])) AS Q10
FROM TEST_Surveys
GROUP BY TEST_Surveys.SurveyID, IIf([quest1]="N/A",Null,Val([quest1])), IIf([quest2]="N/A",Null,Val([quest2])), IIf([quest3]="N/A",Null,Val([quest3])), IIf([quest4]="N/A",Null,Val([quest4])), IIf([quest5]="N/A",Null,Val([quest5])), IIf([quest6]="N/A",Null,Val([quest6])), IIf([quest7]="N/A",Null,Val([quest7])), IIf([quest8]="N/A",Null,Val([quest8])), IIf([quest9]="N/A",Null,Val([quest9])), IIf([quest10]="N/A",Null,Val([quest10]));


This is what it returns

# Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10
1 1 1 0 1 1 0 1 0 1
2 1 1 1 1 1 1 1 1 1
3 0 1 1 1 1 1 1 1 1 1
4 1 0
5 1 1 1 1 1 1 1 1 1 1


The issue is that I now try and sum up questions 1-10 (Q1+Q2...) - but it only gives me a number for question 3 and question 5. The rest of them are null - I'm assuming this is because I have a null value?

What I want to do with these scores now is:

Assign each question a set number of points or % of total. If the question is null, it then assigns that null questions value evenly across all of the questions that aren't null.

Granted - that's probably outside of what I'm able to do quickly - but I'm sure I could figure it out.....I've put some time into getting these numbers to add up and I can't - which is starting to get frustrating.....am I doing something wrong????

:-/

Lisa

 

[Rolliee]

Lisa,

There is no question but that I would do this with VB. I would rather write the VB than try to figure out your query. As you know, this is a killer to debug and easy to be fooled.

send me a zipped mdb of your table and I will show you what I would do.

rollie@bwsys.net

 

[lmn]

2 problems here...

1) I'm a temp - they won't give me a license for VB on my machine (this company is ENORMOUS by the way)

2) I'm a temp - I want to make it as simple as possible - for the next person who has to work on this mess if you know what I mean

3) I'm a temp....(I just have to remind myself of this over and over to realize it's not that bad)

 

[Rolliee]

Do some whiz VB and you will not remain a temp. I will still do some simple stuff and send it back - zipped files both ways. the VB, by the way, resides in ACCESS. I am not talking about a separate VB.

rollie@bwsys.net

 

[lmn]

Ha.....they've offered me the job and I've declined....

Ok...so then VB Access is what you are talking about - my mistake - I've worked with developers before who required the full blown version of VB and that's what I thought u were referring to.

The DB is way too large to send via email - I will ask around here to see if there are any VB experts.

Thanks for pointing me in the right direction.