Number format in access query

[chegra26]

I am trying to format a field in an access query as a number without any decimals. The field in the table is set to General number with 0 decimal places. The number displays correctly in the query, however when I export to a text file (which is the format needed for an upload), the particular field has decimals places in it.

Anyone have a solution????

 

[Joe4]

Do you have any need to store/keep the decimals at all?
If not, then use an Integer or Long number data type, which does not store decimals.
Choosing one of those options will drop all decimals.

Or, in your query, you can use the FORMAT function on that field in a calculated field, i.e.

FORMAT([NumberField],"0")

 

[chegra26]

Thanks Joe4.

FORMAT([NumberField], "0") is the first thing we tried which works great until we export it a txt file. After the export that particular field shows as a decimal. We do not need the decimals and get upload errors with decimals.

Thanks again

 

[Joe4]

FORMAT([NumberField], "0") is the first thing we tried which works great until we export it a txt file. After the export that particular field shows as a decimal.

It definitely should NOT do that, as using the FORMAT function actually changes it to a Text field.
Are you sure that you are using the FORMAT and not just using the Format property on that field in your query?
Are you sure that you are exporting the query and not the table?
How exactly are you exporting the data?
What format is the data exported to (what kind of txt file, what is the delimiter)?
How are you viewing the exported data?

 

[chegra26]

Definatly using the FORMAT function and exporting the query. We are exporting the query from the External Data Tab, and selecting text file and fixed width.

 

[Joe4]

Are you sure that you have the Query highlighted and not the Table when you are clicking on that Text File button from the Export ribbon.

Note, here is a way that gives you a little more control.
Right-click on the Query name, select Export, then go through the Export Wizard where you can change certain settings.

 

[chegra26]

that's how we are doing it. Positive that we are using the query.

 

[Joe4]

Can you post the SQL code of the query you are exporting (switch query to SQL View and copy/paste SQL code here)?
Also, what are you using to view the data file that gets produced?
Is it showing the original decimal value, or just .00 (it might be helpful if you could post a few examples of what you are seeing)?

 

[chegra26]

Here's the SQL on one of the fields in the query.
Still when exported to text fixed width, it does not show 4 places/

SELECT Format([Days Attending],"0000") AS DaysAttending
FROM September_Attendance_Report_100;

 

[Joe4]

Still when exported to text fixed width, it does not show 4 places/

What a minute. What exactly are you trying to do?
Originally you said:

The number displays correctly in the query, however when I export to a text file (which is the format needed for an upload), the particular field has decimals places in it.

So, is the problem that it is showing decimals places when it should not;
or is the problem you want to show 4 numbers and it is not?

Please answer these following two questions (one I have already asked twice and you have yet to answer it):

1. What program are you using to view your output?
Do NOT use Excel, it will not give you an accurate picture of what the exported data really looks like. Excel will drop the leading zeroes upon opening the text file (it does its own auto conversion).
To truly see what the data in a text file or CSV file looks like, open it in a Text Editor, like NotePad or WordPad.

2. Please provide a few data samples of this "Days Attending" field, showing what the entries in the table look like, and what you want the exported values to look like.