Change Data Type of Calculated Field in Query 1

[kirstenlargent]

I have a query where I am using the MID function to extract a number out of a text string. I need this field to be a number field, as I need to use it for calculations.
I can not figure out how to change it to a Number field.
I tried selecting the field, and going to Properties - the Format drop down box does not display any choices at all.
How can I change this field’s data type to a number?
THANKS!

 

[tlbroadbent]

CSng, CDbl or CInt should do what you want. These functions will convert a string to a Single, Double or Integer data type, respectively. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kirstenlargent]

I just tried the CInt, CSng, & CDbl. They did not work.
When I view the query results, and try to sort by the number, I get the error "Data Type Mismatch in Criteria Expression".
Any other suggestions?
I am just tring to get the number of years out of a string, example, "TY+15" or "TY+8"- I am using the Mid function to get the year out (15 or 8). I need the result to be a number so I can use it for calculations.
THANKS!

 

[JimHorton]

Sounds like you need to verify if the Mid is actually returning a numeric, ie if you open the query, it may look fine, but when you scroll or sort and the query hits a record where the mid doesn't return a value that cdbl, etc, can parse, you'll get the error. Avoid it by

IIF(isnumeric(mid([myfield],n,n)),cdbl(mid([myfield],n,n)),0)
Above, the last '0', would be replaced by wherever in the results you want to put the non numerics, or Null if you don't want them at all.
--Jim

 

[tlbroadbent]

The error says that the type mismatch occurred in the criteria expression. Do the values in the Where clause match the data types of the columns? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JimHorton]

Terry, Kirsten,
Even if the mismatch is in the calculated value, Access will give the 'mismatch in criteria' error. When it hits the type mismatch in the calculated field, it puts "#Error" in the field, but you don't get an error message--if you have no criteria. But with criteria, it doesn't match the resultant calculation, and you get the 'criteria mismatch'. Try it out.
--Jim

 

[kirstenlargent]

Jim -
That worked PERFECTLY!!!!
THANKS!!!!!