exporting blank text fields encapsulated by contiguous quotation marks 1

[uscitizen]

i wish to export several tables from my a2k mdb to comma delimited csv files which i will then rename to have 'txt' extensions. when i use a query to create the csv file of one of them (the "PUBLICATIONS" table), what i get is this once i rename the csv to txt:

"PUBLICATIONS","T95-0036",1,,"Effectiveness of Taxol plus Cisplatin","Journal of the American Medical Association","50",1997,"McGraw Hill","10-20"

note that the fourth field (following the 1 and just to the left of the "Effectiveness.......", is expressed by nothing.

what the people who will be uploading this information onto their computers tell me they would want under those conditions (blank text data) is a pair of contiguous quotation marks.

i have modified the above to represent this visually:

"PUBLICATIONS","T95-0036",1,"","Effectiveness of Taxol plus Cisplatin","Journal of the American Medical Association","50",1997,"McGraw Hill","10-20"

has anybody found a way to accomplish this?




“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[OnTheFly]

You can use the NZ function to accomplish this for null text fields. For example if your table is ItemListing and your fields are ItemType, ItemID, ItemNumber, ItemExtras and ItemExtras is where you could have null values you would write the Query as such...

SELECT ItemType, ItemID, ItemNumber, NZ([ItemExtras],"") AS [Item Extras] FROM ItemListing

This should get you what you are looking for.

Hope this helps.

OnTheFly

 

[uscitizen]

hi, on the fly....

i bet you're dead on with NZ function....what i've been doing is modifying the 'Allow Null Sring' properties to 'Yes' on my text fields and setting the default value to the pair of contiguous quotation marks which i sort of think is going to do the same -- hopefully. i'd try your suggestion out in a ny minute but i've invested a fair amount of time in this approach and i'll take it to the end unless something goes awry.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[OnTheFly]

Just be mindfull that changing the default value on an already populated table will not enter the default value in the null records only on new records that are entered. You have to update the old records manually or by update query.

Also, remember that you will not be able to test for Null in these fields anymore, you will always have to check for ="".

Hope this helps.

OnTheFly

 

[uscitizen]

i see what you're saying....the database is in its it pre-natal state and the data in it are for testing purposes just.

i don't say any big deal with the difference between testing for null and "" so long as one's mindful of the requirement.

again, thanks!

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[OnTheFly]

There really isn't a big deal with testing for "" versus Null but remember that Null that "" does take up space in the database where Null does not.

It would not be an issue in a small database with very few text fields but in a large database with lots of text fields it could become a factor.

Hope all goes well.

OnTheFly

 

[uscitizen]

hi onthefly,

i think you meant to say "remember that "" does take up space in the database where Null does not", am i wrong?




“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[OnTheFly]

No, you are correct.
I should have proof read my post.

Sorry.

OnTheFly