Import text procedure question 1

[jon92]

Hi
I import a text file into a table using the following attached to the on click property of a button on a form:

[DoCmd.TransferText acImportFixed, "mypec", "mytablename", "e:\my documents\textfile.txt"]

This transfers 3 columns of text into fields named:
[mydate] [mytime] [mybarcode] there is a 4th field in the table named: [myname]

My question is, is it possible to add code to the transfer text command that will add data to the [myname] field for every record imported - maybe by user prompted input box ?
(the data would be the same for each feild)

Any help would be appreciated

 

[jebry]

Hi!

I don't think you can do that with the ImportText command. But, after you import the file, you can run an update query to add the information from the InputBox to the field.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[jon92]

Thanks for the quick reply Jeff

I have tried your suggestion but unfortunately the update query changes all the data in the [myname] field to the new value - including the data already in the table. What I think I need, is to append to the data being imported at the time - if that makes sense ?

regards jon

 

[jebry]

Hi Jon!

Just add criteria to your append query:

Where IsNull(MyName) = True

This will choose only the records without anything already entered.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[jon92]

Hi Jeff

I have tried what you suggest but I am now getting a warning box saying invalid syntax you may have entered an operand without an operator.

I put the code in the criteria box of the update query - in the [myname] field.

Regards Jon

 

[jebry]

Hi!

For some reason adding this in the QBE view doesn't work. Go to the SQL view and add it there. In the QBE view you can add another field like this:

Field1: IsNull(myName)

Don't Show

Criteria = True

hth


Jeff Bridgham
bridgham@purdue.edu

 

[jon92]

Thanks jeff

That now works fine

Regards Jon