Export to text file

[DBAMJA]

I'm having an issue with formatting when exporting a query to a text file. I have a field in a table that is set to Short Text. Generally there is a number in the field but there is an occasional alpha character in the field. When I export the query that this field is part of to a text file, Access automatically adds a decimial point and 00 to the data in the field it it appears to be a number. Unless there is an actual decimal point and numbers after it, I just want it to show the whole number. In other words, what is happening is 28 is getting converted to 28.00 where I only want 28 to be exported.

Any ideas to fix this?

Thanks in advance.

[flush]

Michael

It is said that God will give you no more than you can handle. I just wish God didn't think I was just a bad ass.

 

[Andrzejek]

Could you show your query and indicate with which field you have this issue?


---- Andy

There is a great need for a sarcasm font.

 

[DBAMJA]

SELECT PD_LAST_NAME AS [Last Name], PD_FIRST_NAME AS [First Name], Null AS [Middle Initial], PATIENT_ID AS [Patient Identifier], GENDER, Format([PD_BIRTH_DATE],"yyyymmdd") AS [Date Of Birth], Null AS Street, Null AS City, Null AS State, Null AS [ZIP Code], Null AS Phone, Null AS [Social Security Number], RevenueCode AS [Revenue Code], Null AS HCPCS, RX_NUMBER AS [Prescription Number], Left$([DRUG_NDC],5) & "-" & Mid$([drug_ndc],6,4) & "-" & Right$([drug_ndc],2) AS [NDC Code], DRUG_STRENGTH_UNITS AS [NDC Unit], [highlight #FCE94F][QTY_DISPENSED] AS [NDC Quantity][/highlight], ready_date_key AS [Service Date], [highlight #FCE94F][QTY_DISPENSED] AS Quantity[/highlight], TP_PRICE_PAID AS Price, Drug_Label_Name AS Comment
FROM McKessonEnterpriseHospiceClaims
WHERE (((ImportDate)=Date()));


The highlighted fields are formatted as Short text in the table this query is based on.

Data looks like this in the table:

QTY_DISPENSED
1
30
30
30
90
30
30

[flush]

Michael

It is said that God will give you no more than you can handle. I just wish God didn't think I was just a bad ass.

 

[DBAMJA]

I figured it out, sort of.

I redid the export spec and now it's working fine. Very odd but it's working now.

Thanks for taking a look.

[flush]

Michael

It is said that God will give you no more than you can handle. I just wish God didn't think I was just a bad ass.