Appending a table from a download

[Alex Martinez]

Hello,

I get a daily download from our IT department into Access database I then
append the table called "DownLoad" into main table call "Inventory" via
append query called qryAppend.. Both the "Download" and "Inventory" table
are the same except that the "Inventory" table has a date field called
"MonthEnd" Here is what I like to do, I want to code the append query if
possible so the "MonthEnd" date will automatically populate into the
"Inventory" table. The "MonthEnd" date will be based on a field call
[ReceivedDate] from the download. For example if the [ReceivedDate] is
2/13/2006 or 2/20/2006 the "MonthEnd" in the "Inventory" table should be
2/28/2006. Below is code in the form that is in the 'Add' command button
when the user has to manually input the received date the "MonthEnd" date is
automatically populated. Any tips or a different approach will be
appreciated thank you in advance.

Me!cboMonthEndDate = DateSerial(Year(CDate(ReceivedDate)),
Month(CDate(ReceivedDate)) + 1, 0)

 

[Guest]

I know there are cleaner ways to do it but this in your query will get you
the MonthEnd date --
MonthEnd:
(DateAdd("m",1,[ReceivedDate]-Day([ReceivedDate]))+5)-Day(DateAdd("m",1,[ReceivedDate]-Day([ReceivedDate]))+5)

 

[John Vinson]

I want to code the append query if
possible so the "MonthEnd" date will automatically populate into the
"Inventory" table.

You're almost there already!

Simply copy the DateSerial() function call from your form control into
a vacant Field cell in the append query, and append that field to
MonthEnd.

John W. Vinson[MVP]

 

[Alex Martinez]

Thanks Karl,

I appreciate your help.

I know there are cleaner ways to do it but this in your query will get you
the MonthEnd date --
MonthEnd:
(DateAdd("m",1,[ReceivedDate]-Day([ReceivedDate]))+5)-Day(DateAdd("m",1,[ReceivedDate]-Day([ReceivedDate]))+5)

Hello,

I get a daily download from our IT department into Access database I then
append the table called "DownLoad" into main table call "Inventory" via
append query called qryAppend.. Both the "Download" and "Inventory"
table
are the same except that the "Inventory" table has a date field called
"MonthEnd" Here is what I like to do, I want to code the append query if
possible so the "MonthEnd" date will automatically populate into the
"Inventory" table. The "MonthEnd" date will be based on a field call
[ReceivedDate] from the download. For example if the [ReceivedDate] is
2/13/2006 or 2/20/2006 the "MonthEnd" in the "Inventory" table should be
2/28/2006. Below is code in the form that is in the 'Add' command
button
when the user has to manually input the received date the "MonthEnd" date
is
automatically populated. Any tips or a different approach will be
appreciated thank you in advance.

Me!cboMonthEndDate = DateSerial(Year(CDate(ReceivedDate)),
Month(CDate(ReceivedDate)) + 1, 0)

 

[Alex Martinez]

Thanks John,

I appreciate the help.

Regards

You're almost there already!

Simply copy the DateSerial() function call from your form control into
a vacant Field cell in the append query, and append that field to
MonthEnd.

John W. Vinson[MVP]