How to make text in query field into values ?

[mrchonginhk]

I have a table source of 2 column of text, but so far they only contain numbers.

I want a 3rd column in query to add them up.
Now, it gives me error because they cannot be added up as text. What can I do at SQL to make them values and be added up ?

 

[SydneyGeek]

Here's a generic answer. Assuming your fields are FldA and FldB, you can do this:

In query design view, create an expression like --

Tally:CLng([FldA]) + CLng(FldB])

-- to convert the text to long integers. If you need decimals, use CDbl for ordinary numbers, and CCur for currency.

Denis

 

[VicRauch]

You can also use the following expression:

Tally: Val([FldA]) + Val([FldB])

The difference here is that the Val() function will convert any text field to a number IF it starts with a number. That is, it will convert "45AB" to a value of 45. If you have "GO31", you will get a 0.

If you use the convert functions (CLng, CDbl, CCur, etc.) you will get an error if there is a letter anywhere in the number.

In both cases, using Val or Convert functions, you might also want to use the Null catcher function, Nz(). the Nz() function will return a 0 if the field is Null. Otherwise, you will get an #error returned from the expression if there is a null in any field.

It will be your choice as to which function you use, based on the outcome you need from your query.

HTH,

 

[mrchonginhk]

I tried Nz be4 but when I run external query with Nz , it generates an error saying "undefined expression Nz".

I will try Val & Clang etc and I think they will work.

Thanks a million for both of you. You are the best !!!