Append query help

[UrbanGrafix]

Evening all,

Now I will start off and say that I am very new to Access and i'm learning as I go along. And i'm sure that what I need to do is simple, I just can't figure it out so any help is appreciated.

Essentially what I am trying to do is every time I import a file, I want access to ask me for a date dd/mm/yyyy which it would append into "Date File run" in my Data table.

My main reason for this is, I need to differentiate the different days that each file was generated on and the ability to enter my own date would be easier and allow for changes should there be any errors in old files.

When I run my append query, I am able to have it generate today's date and append it to data but I cant figure out how to enter my own date or for it to correctly edit my data table. Currently, if I had 100 records it would create 100 blank records with just the date and not edit the current data.

My Append query has the following expression Expr1: Date() (I just did today's day for testing purposes to see if it worked)

And that's where i'm stuck. Any help would be greatly appreciated.

 

[Newie]

I'm new(ish) to Access too, so this may seem long-winded but the only way I can think of the answer is to set up a Command button to a Macro which imports your data (or Copy data, Run Command, Paste Append) then a second step in the Macro to run an Update Query setting "Update to" to [Enter Date]

Newie

 

[plog]

Just to understand: you import a file into a temporary table, run a query to append that data to a main table and want to be able to control what date gets appended to a field of the main table for that data?

If that is correct I would set up your append query to have a field that looks like this:

DataDate: [Enter Your Date Here]

Type that in exactly as I typed it. When you run the query you will see a prompt that says something like "[Enter Your Date Here]" and an input box. Type in your date and whatever you typed in will get appendeded with that data.

 

[UrbanGrafix]

Just to understand: you import a file into a temporary table, run a query to append that data to a main table and want to be able to control what date gets appended to a field of the main table for that data?

If that is correct I would set up your append query to have a field that looks like this:

DataDate: [Enter Your Date Here]

Type that in exactly as I typed it. When you run the query you will see a prompt that says something like "[Enter Your Date Here]" and an input box. Type in your date and whatever you typed in will get appendeded with that data.

Very close. My original plan was open database, run import which would ask me for the file location etc like normal and then there would be a macro there to append a date to the entries of that file once imported.

I planned on using a temporary table for the import which after the append would then add the data to the existing main record but I was advised by someone at work that I shouldn't even need to go that in depth with that i'm trying to do.

Basically, when i import a file I need to be able to control the date appended to it and add it to my existing data without wiping any existing records.

EDIT: [Enter Your Date Here] works great, I didn't even think about that. My only issue now is how to setup the append query and macro to only add that date to the new file.

I was thinking, like IF Date File Run = null then add date but i'm not sure how to do that. (obviously still need to get the append to actually modify the data instead of just creating new data also)

 

[Newie]

I need to differentiate the different days that each file was generated on and the ability to enter my own date would be easier and allow for changes should there be any errors in old files

I'm obviously missing something here.
If you set the Default Value of the "Date File Run" field to Date(), that will automatically enter the date that you generate each file on (whether importing directly to your main Table or appending the new data from your Temporary Table to your Main Table) without the need for a Query.......... but then I get lost as I believe you may want to then change or edit that date which seems to defeat the purpose as it makes the Date the file was generated incorrect ?

An Automatic Number field will also distinguish between batches in case you import two on one day and only want to make changes to one of those.

Well, it is quite late at night and I am tired !

Newie

 

[UrbanGrafix]

I'm obviously missing something here.
If you set the Default Value of the "Date File Run" field to Date(), that will automatically enter the date that you generate each file on (whether importing directly to your main Table or appending the new data from your Temporary Table to your Main Table) without the need for a Query.......... but then I get lost as I believe you may want to then change or edit that date which seems to defeat the purpose as it makes the Date the file was generated incorrect ?

An Automatic Number field will also distinguish between batches in case you import two on one day and only want to make changes to one of those.

Well, it is quite late at night and I am tired !

Newie

My understanding of Date() would be that it would only enter the date that you import the file. Now i'm importing these files after the fact (or at least some) as the files started before the report was completed hence the need for the ability to enter date (which [Enter Your Date Here] does perfectly).

I thought that it would be easier and more redundant by having a temporary import table which would then have the appended date attached to it, after which the end of the macro could move the data created into my archive file essentially but i'm having trouble figuring out that part.

I will keep up my googling and testing to see how i go

EDIT: attached my database as I don't know enough about access yet to figure it out

 

[Newie]

Morning !
'Fraid I couldn't open your File (didn't like the accdb extension) but let's give it another bash!
What you want sounds like it should be reasonably easy but as explained before, what I'm having trouble with is understanding exactly what date you want to show because you mention "differentiate the different days that each file was generated on" which could only be today's date, but then you mention "a date" ......"there would be a macro there to append a date " which could be any date.
So I'm imagining you have a Main Table into which you are appending batches of data, setting the Default to Date() would have solved the problem if you want to show the date which the data was imported but you've already explained in your last reply that's not what you're after.
If you want to put any old date into the newly imported batch Field after it's in your Main Table, you could write an Update Query setting the "Date File Run" criteria to "Is Null" and the "Update To" option to [Enter Date].

Now, over to you....

Newie

 

[UrbanGrafix]

Morning !
'Fraid I couldn't open your File (didn't like the accdb extension) but let's give it another bash!
What you want sounds like it should be reasonably easy but as explained before, what I'm having trouble with is understanding exactly what date you want to show because you mention "differentiate the different days that each file was generated on" which could only be today's date, but then you mention "a date" ......"there would be a macro there to append a date " which could be any date.
So I'm imagining you have a Main Table into which you are appending batches of data, setting the Default to Date() would have solved the problem if you want to show the date which the data was imported but you've already explained in your last reply that's not what you're after.
If you want to put any old date into the newly imported batch Field after it's in your Main Table, you could write an Update Query setting the "Date File Run" criteria to "Is Null" and the "Update To" option to [Enter Date].

Now, over to you....

Newie

Hopefully this explains it. I create a file on a certain day and i need the date that the file was created on to be the date recorded in access. My only problem is that initially I will be importing multiple files and i need to be able to enter those dates manually so that its correct (so that all those files don't end up with the day i imported them as their date).

I will give what you mentioned a try tomorrow, and unfortunately I cant re save my database for 2002 format as it wipes all the queries etc.