Excel Numbers format

Hi all i'm having an issues when i trying to do a report:

i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.

I would really appriciate your Help

Thanks

The value in the cell is inserted as a string, so filtering should work. The indicator is just Excel warning you that this string could be changed into a number. You can turn off this warning in the options settings of Excel.

Ian|||

Thanks Ian but the autofilter doesn't work if i do a greater than or less than filter.

But i have another field that only contains names and it does the filter by this criterio, i have try to put in the RDL CSTR() function but doesn't work. something similar happened to me with date fiels and i just addded a Cdate to the RDl field and added the mm/dd/yyyy format for that field and i got the autofilter.

I don't know what else i could try

Thanks

|||

You know how in excel you can prefix a value with a single quote, to force it to accept the input as text. You could try that in your report. The downside is that you'd see this in the web view but should not be visible in the export.

I played around in excel and if I prefix my values with a single quote then the excel filter only works if I also include the single quote in my filter expression.

|||
If you can provide an example of what you a trying to accomplish in the generated spreadsheet, I may be able to point you in the right direction. Using just Excel, can you give me an example of how to add a less than or greater than filter for text?

Ian|||sure i can give you an excel sample, but how can i attache the excel here ? or what address can i send it|||In a excel you can auto filter by greater than and less than by using the custom filtering, that appears when you click the selected column with the autofilter on and there it show custom, then it opens a dialog box where you can especify if you want the condition , Equal, greater than or less than...|||Thanks Adam for t he tip, i added the single quote to the expresion in the report and i created a new report and exported to excel, absouly it works, it stops givng me the error for the text to number conversion but i can see the single quote when i open the Excel even if i print it, is there a way that that single quote can be invisible?... jejjeje or am i asking too much?|||

I have this exact same issue. I have an Access Database that exports a tool report to Excel. The report exports properly and has worked for a long time. I recently updated it to Access 2003 and now it has problem. The tools part number is in the form 99-99-9999 but when it tries to export to Excel it turns it into a number and I get a single value such as 359. So I already figured I could just insert the single quote in front of the values and that works but I can see it in Excel. If you enter a number such as this in Excel directly with the single quote it does not have the problem but exporting from Access to Excel it does.

Are there any solutions to that?

|||

this issues for me began with sorting dates, and i have the same problem that you have wit hthis i couldn't get the date right to be filter, but in my RDL i found the solution to that on, in the expresion field that you are getting the date value you have to add the Cdate function and in the format of that textbox in the RDl you have to specify the format yo want it to be: mm/dd/yyyy so that you will avoid to add the single quote to the date fields, but i still have found a solution for my filtering issues when there are numbers and numbers with characters in the same column :-(

Anyone?

|||
You may want to try using the CStr() function, or ToString() on the value of the field, to cast the value to a string. The latter approach, however, may cause an exception if the value of the field is Nothing.

Ian|||

Thank you for the advice, I tried it but I think the problem is I am using an Access report to export to Excel using DoCmd.OutputTo. So between the report and Excel I can't change the formatting apparently. Maybe I need to look into a way to export directly from my Access Query to Excel. That may improve things as I would be taking out a step. If you know which command that would be please let me know but I will probably begin a search for it.