Convert text string to number in a query

[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....

 

[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

 

[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
*

 

[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

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....