Text To Numbers in Query

[wheatlandacctech]

Hello, I tried to find a topic on my question but couldn't find one so hopefully I don't bug anyone to ask my question here. I'm trying to create a field in my query to change text to numbers. I did a google search and found a function that I thought would work, but it doesn't do anything when I run the query. The function I used is "numbertext:Cstr([fieldname])". Can someone point me to a topic on my question or help me out as to what the function is to write text to numbers? I apologize if I somehow missed an existing topic on this question.

Thanks in advance.

Joel

[Welshgasman]

That converts a number to a string?

Look at the Val() function.

[kd2017]

CStr is short for (C)onvert(Str)ing or convert to string.

You also have CInt, CDbl, CLng, CCur, etc that convert an input to integer, double, long, currency respectively. There are more than I've listed here, but each of these are examples of a number data type. If you don't already know do a quick Google search for ms access datatypes to find a table of numeric datatypes to find out which is appropriate for you.

[edit] Resources:
https://docs.microsoft.com/en-us/off...sion-functions
https://support.microsoft.com/en-us/...9-3418f622e482

[moke123]

All cstr() does is convert a value to a text datatype. https://www.techonthenet.com/access/...atype/cstr.php

Most of the functions I have seen usually convert currency datatype to words.
Here's one - https://support.microsoft.com/en-us/...8-69442cd55d98

Just to be clear, you want to change "Four" to 4, or "TwentySeven" to 27?

Whats the upper end of the numbers you need?

[wheatlandacctech]

Hello, I'm trying to find any threads that talk about converting numbers to text in a query. I search for threads on this topic but I can't seem to find one that tells me what function to use. Can someone point me to a topic that says specifically what function to use, or help me out? Thanks,


Joel Wiggers

[Welshgasman]

First it is Text to Numbers, now numbers to text?
Which is it?

There is no miracle function to do that. You need to find one that has already been written?
Here is one/two depending on how you look at it?
https://www.access-programmers.co.uk.../#post-1785770

[Micron]

Either way, isn't this just about built in conversion functions?
CLng("123") becomes 123 (long number type)
Cstr(123) becomes "123" string

and so on...

[Welshgasman]

Either way, isn't this just about built in conversion functions?
CLng("123") becomes 123 (long number type)
Cstr(123) becomes "123" string

and so on...

Honestly not sure. In the text to numbers post I offered Val(), but I have simply concatenated numbers to strings in the past, without having to convert to a string.?
Someone thought it might be actual numbers in words, hence my link.

[CJ_London]

you can just use

txtStr="" & number

[wheatlandacctech]

Hello, I'm trying to find any threads that talk about converting numbers to text in a query. I search for threads on this topic but I can't seem to find one that tells me what function to use. Can someone point me to a topic that says specifically what function to use, or help me out? Thanks,

Joel Wiggers

I copied the code from the link you sent me and then called it in the Form_Current event but it didn't work. It threw and error exception that says "Argument Not Optional". What did I do wrong?

Joel Wiggers

[Welshgasman]

You need to pass the number value to the function?

That is what functions do generally. They take parameters passed in and return a value using those parameters.

strNumberInWords = English(2345.75)

[pbaldy]

I merged your duplicate threads since both had replies.

[Micron]

WGM,

Val("123 Dog") = 123
Val("Dog 123") = 0

Val stops at the 1st character than cannot be recognized as a number. Examples are needed and until then, I'm going to bow out. Who knows, this could be about converting data types or it could mean converting "Four" to 4 or vice versa, as has been suggested.

[wheatlandacctech]

So I tried to pass a number to the function from a textbox on my form and I get this error. "Compile Error" "Function call on left-hand-side of assignment must return Variant or object"

I'm still doing something wrong.

Joel Wiggers

[CJ_London]

maybe, maybe not - would help if you showed the function code you are actually using and how you are using it - together with some example data of what you are inputting and what result you expect