Convert text string to number in a query

T

twalsh

Ok i have an infopath form where users enter a number and it is stored into
an access database...
Problem is, Infopath throws a fit if you format those fields as numbers in
access.
I want to run a query that converts the text from the field into a number,
this is because the query will be used in a 'sendobject' macro that will
email data in an excel spreadsheet and the recipients need to be able to
manipulate and calculate with the data, which they can't do if it is sent as
text.
I tried using the expression "Int" as in Int([billable hours]), but it only
brought me the integer, the data thats entered is more like 7.69, 'int'
brought back 7, albeit it was in number format.
Am i on the right path or totally lost?
Please keep in mind i am a bit of a novice here....
 
J

Jeff Boyce

Perhaps there are a couple steps here.

One would be to convert text to number ... a query that uses the CInt() (or
CCur(), or CLng(), or ...) "conversion" function might help.

Then, the issue of formatting for display. Formatting as an integer (short
or long) will only display "whole numbers" (the definition of integer). If
you need up to four decimal places (but no more), the currency data type
does that accurately. If you'll need more than four places, take a look at
single, double and decimal data types and formatting.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

strive4peace

try converting to a Single precision or Double precision number

CDbl([billable hours])

"need to be able to manipulate and calculate with the data, which they
can't do if it is sent as text."

Even though it is text in Access, I think you may find that Excel will
treat it as a number without doing anything special...

Warm Regards,
Crystal

*
:) have an awesome day :)
*
 
R

raskew via AccessMonster.com

Hi -

Use the Val() function to convert a string to a number, e.g. from the Debug
(immediate) window:

x = "34.3"
? val(x)
34.3

To show that val(x)is a number
? cdbl(val(x))
34.3

HTH - Bob
Ok i have an infopath form where users enter a number and it is stored into
an access database...
Problem is, Infopath throws a fit if you format those fields as numbers in
access.
I want to run a query that converts the text from the field into a number,
this is because the query will be used in a 'sendobject' macro that will
email data in an excel spreadsheet and the recipients need to be able to
manipulate and calculate with the data, which they can't do if it is sent as
text.
I tried using the expression "Int" as in Int([billable hours]), but it only
brought me the integer, the data thats entered is more like 7.69, 'int'
brought back 7, albeit it was in number format.
Am i on the right path or totally lost?
Please keep in mind i am a bit of a novice here....
 
T

twalsh

This seems to be progress, it is converting to number, however it is
rounding. Ex:

when Billable Hours= 8.40

CDbl(Val([Billable Hours])) returns 8.00

and Val([Billable Hours]) also returns 8.00

raskew via AccessMonster.com said:
Hi -

Use the Val() function to convert a string to a number, e.g. from the Debug
(immediate) window:

x = "34.3"
? val(x)
34.3

To show that val(x)is a number
? cdbl(val(x))
34.3

HTH - Bob
Ok i have an infopath form where users enter a number and it is stored into
an access database...
Problem is, Infopath throws a fit if you format those fields as numbers in
access.
I want to run a query that converts the text from the field into a number,
this is because the query will be used in a 'sendobject' macro that will
email data in an excel spreadsheet and the recipients need to be able to
manipulate and calculate with the data, which they can't do if it is sent as
text.
I tried using the expression "Int" as in Int([billable hours]), but it only
brought me the integer, the data thats entered is more like 7.69, 'int'
brought back 7, albeit it was in number format.
Am i on the right path or totally lost?
Please keep in mind i am a bit of a novice here....
 
R

raskew via AccessMonster.com

Hi -

Keep in mind that BillableHours is a string, Example:

BillableHours= "8.40"
? val(billablehours)
8.4
? CDbl(Val(BillableHours))
8.4

Bob
This seems to be progress, it im is converting to number, however it is
rounding. Ex:

when Billable Hours= 8.40

CDbl(Val([Billable Hours])) returns 8.00

and Val([Billable Hours]) also returns 8.00
[quoted text clipped - 25 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top