Can I use a query to change a data type?

W

WildlyHarry

I am using a table linked to a Lotus Notes Database. I am using a query to
perform some calcualtions on the data in the table. The issue I have is that
the data I am trying to perform calculations on, is being read as text, when
it is actually numerical. So when I set an expression to add different
fields together, access merges them like text strings. For example
1+3+5=135, instead of 9. Is there anything I can add to my query to make
access treat these fields as numbers? Thank you in advance for your help.
 
O

Ofer Cohen

Use the Nz function to convert Null to zero, and then use the Val function to
convert it to number

In the Query add a field

Val(Nz([Field1Name),0) + Val(Nz([Field2Name),0) + Val(Nz([Field3Name),0)
 
W

WildlyHarry

Worked like a charm. Thanks a million.

Ofer Cohen said:
Use the Nz function to convert Null to zero, and then use the Val function to
convert it to number

In the Query add a field

Val(Nz([Field1Name),0) + Val(Nz([Field2Name),0) + Val(Nz([Field3Name),0)

--
Good Luck
BS"D


WildlyHarry said:
I am using a table linked to a Lotus Notes Database. I am using a query to
perform some calcualtions on the data in the table. The issue I have is that
the data I am trying to perform calculations on, is being read as text, when
it is actually numerical. So when I set an expression to add different
fields together, access merges them like text strings. For example
1+3+5=135, instead of 9. Is there anything I can add to my query to make
access treat these fields as numbers? Thank you in advance for your help.
Click to expand...
Click to expand...
 
A

Aaron Kempf

no you cannot use a query to change a datatype

because if you've got a query with the column 'EmployeeID' and you try to
convert it then you'll need to rename this column to EmployeeID2 or
somethnig


this is the worst bug in MS Access; and it's one of the main reasons I moved
to SQL Server a decade ago



Jeff Boyce said:
Take a look at the CInt() function (you did provide 'integers' in your
example).

In your query, create a new field, looking something like:

NewField: CInt([YourIncomingText])

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


WildlyHarry said:
I am using a table linked to a Lotus Notes Database. I am using a query to
perform some calcualtions on the data in the table. The issue I have is that
the data I am trying to perform calculations on, is being read as text, when
it is actually numerical. So when I set an expression to add different
fields together, access merges them like text strings. For example
1+3+5=135, instead of 9. Is there anything I can add to my query to make
access treat these fields as numbers? Thank you in advance for your
Click to expand...
help.
Click to expand...
 
Top