Change format on query criteria (1 Viewer)

[ice051505]

Hi, I have a small question here, hope someone can solve that

I use expression on the query field, but after I run it, the field change its format from number to text, can anyone tell me how to reformat on the query criteria to change the text to integer format?

Thanks

 

[CJ_London]

use the CInt, Clng or Cdbl functions to convert text to a number

e.g. Cint("123") will return 123

 

[ice051505]

I did like:
FSC:Cint([FSCtol])
in the query design field, but it returns me error in the query table, Do you mind check it for me?

use the CInt, Clng or Cdbl functions to convert text to a number

e.g. Cint("123") will return 123

 

[CJ_London]

You need to provide me with the values in your table - if [FSCtol] has alpha characters or is null it will fail

It would also be helpful if you can post what the error is - there's thousands of them

 

[ice051505]

[FSCtol] contain many numbers and some of empty field(null value)
The error is after I run the query, all the cells under [FSCtol] gives me #Error

Thanks CJ_London!

You need to provide me with the values in your table - if [FSCtol] has alpha characters or is null it will fail

It would also be helpful if you can post what the error is - there's thousands of them

 

[CJ_London]

The problem is, you have a problem but have not provided sufficient information for someone else to solve it

Here is a link, so you should be able to solve it yourself

http://office.microsoft.com/en-gb/access-help/type-conversion-functions-HA001229018.aspx

The chances are your number is not an integer, but since you have not provided the information I asked for, I cannot determine wether or not this is the case and I don't have time to speculate.

 

[ice051505]

Sorry! I am away from my desk for a while, so I can not provide the value from my table, but every data under [FSC] is either integer or empty cell, I thought you ask me the data type in my table, so sorry for the confusion, my situation is I have a table which in the right integer format, but when I try to make a query with iff statement: FSCtol:IIf ([type].[FSCtype]="B", [FSC], "*")
The data format in FSCtol is change to text, and i want to change it back to data type, hope this could help you understand my situation
Thanks!

The problem is, you have a problem but have not provided sufficient information for someone else to solve it

Here is a link, so you should be able to solve it yourself

http://office.microsoft.com/en-gb/access-help/type-conversion-functions-HA001229018.aspx

The chances are your number is not an integer, but since you have not provided the information I asked for, I cannot determine wether or not this is the case and I don't have time to speculate.

 

[CJ_London]

It would help if you provided the right information in the first place.

You are using a reserved word - Type - which can have unpredicatable results and I recommend you change it.

Also, you are returning "*" if type<>"B" and "*" is not a number so CInt will fail

Try changing the name of your type field and using the following

FSCtol:IIf ([Tbltype].[FSCtype]="B", [FSC], 0)

 

[ice051505]

It's totally work now! Thank you so much for help me! Thanks CJ_London!!!

It would help if you provided the right information in the first place.

You are using a reserved word - Type - which can have unpredicatable results and I recommend you change it.

Also, you are returning "*" if type<>"B" and "*" is not a number so CInt will fail

Try changing the name of your type field and using the following

FSCtol:IIf ([Tbltype].[FSCtype]="B", [FSC], 0)