Appending a table from a download

A

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)
 
K

KARL DEWEY

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)
 
J

John Vinson

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

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]
 
A

Alex Martinez

Thanks Karl,

I appreciate your help.

KARL DEWEY said:
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)

Alex Martinez said:
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)
Click to expand...
Click to expand...
 
A

Alex Martinez

Thanks John,

I appreciate the help.

Regards

John Vinson said:
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]
Click to expand...
 
Top