Can I use a query to change a data type?

[Guest]

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.

 

[Guest]

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)

 

[Jeff Boyce]

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/

 

[Guest]

Worked like a charm. Thanks a million.

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

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.

 

[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

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/

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.