My invoice number (Auto number) formatted like “Binu/21-22/”0000. While running the query, invoice number is showing “Binu/21-22/0001” which exactly I want When I am exporting it to excel with following VBA code -DoCmd.OutputTo acOutputQuery, "Qry_Test", acFormatXLSX, sFileName, True- that time also excel file is showing the format “Binu/21-22/0001” but when I am clicking on the invoice number on excel file cell then its showing only “1”. I want excel file also with the correct format “Binu/21-22/0001” instead of “1”. Anyone can help me how to resolve this”
I am assuming you are using format option to display invoice number as "Binu/21-22/"0000. So when it is exporting to excel then it is exporting with format not as value. So, you can write you query with FORMAT() function which will display as your desired format and export to excel will export as value. Try-
SELECT "Binu/21-22/" & FORMAT(MyTable.[InvoiceNo],0000) as [InvoiceNo] FROM MyTable
Save this query and then export to excel. Add your other fields in the query. as required.