backend data input

[dan_allen_1]

Hi

I am exporting select data from a superquery ive built in a query tool. The data is coming from an Ingres database.

The data is being exported into a comma seperated .txt file.

I am then using a form built in access to import the data into Access. The issue i am having is that the data exported into .txt file contains nulls in positions where nulls are not accpetable in the Access database. For this reason the import is not being allowed. The data will not import. Access will not enter the "ifnull" values because the data isn't being entered through a front end form.

An answer I have come up with is:

Upon clicking the "import" button on Access form carry out the following:
-genterate a temporary table with no requirements
-populate this table with all data from .txt file
-check eact datafield and where Null, insert a Null value
-Shift comleted data to final resting table
-destroy data in temp table and destroy temp table.

My Question is: How do i do this!!!

If you cannot answer this question within this forum, maybe you could point me towards tutorial - altho I think this may be a little indepth for a tutorial.

Kind Regards

Dan Allen

 

[richary]

To populate your final table it would probably best to use an append query. Without testing and no guarentees etc. I imagine it would look something like

INSERT INTO tblFinalTable( DateOfValue )
SELECT IIf(IsNull([Datefield]),#1/1/2000#,[datefield]) AS Expr1
FROM tblTemp;

assuming your temp table is tblTemp, the final table is tblFinalTable, your datefield is Datefield and the value for missing (required) dates is 1/1/2000. Modify as required for your purposes.

To delete the temptable use the SQL DROP keyword.