Not a problem. Scott. Glad to be of assistance.
If you want to want to export data to an Excel spreadsheet using a query,
open the query builder and select the table you want to use for the export.
The select the fields you want to export and put each in a column in the
query builder. Now, to create a field for the Deadline date, you use what is
called a Calculated Field. That is a field that does not exist in the table,
but will be derived from values either in the table or from values in
controls on a form, or any other location the query can see. This includes
VBA functions and user defined functions. In this case, you want to use the
DateAdd function and the BidDate field to detemine the DeadLine date.
To create a calculated field, you type in the name you want the field to be
followed by a colon followed by the expression that will create the value.
So in the field column you would put
DeadLine: DateAdd("d", -2, [BidDate])
For each row, it will return a date two days prior the the bid date for that
row.
Then you use the TransferSpreadsheet method to export the query using the
name of the query in the table argument of the TransferSpreadsheet.
If you want to populate an Access table, using data in another table, the
process is very similar except you will want to create an update query. To
do this, you follow the same steps as described above to create the query.
Then you change the query type to Append Query. There is an icon on the menu
bar in query design that allows you to choose a Select, Update, Append, or
Delete query. When you select Append query, it will ask you what table you
want the data to be appended to. You select that table. Then there will be
a new row in the query builder named Append To: For each column in the Field
Name: column, you put the name of the field in the table you are going to
append to in the corresponding column in the Append To: row. You would use
the caclulated field here as described above.
--
Dave Hargis, Microsoft Access MVP
theintern said:
Sorry, I've never worked with Queries before. Is there a way for it to
select only certain entries and output them to an Access file using a query?
and during that step i'd name the calculated field "deadline"? Can you
explain how? I'm trying to play around with the query wizard, etc. but it's
not obvious to me what I should be doing.
Thanks for your patience.
scott
:
Okay, in either case you would want to do the calculation at the time of
export.
If you are exporting to Excel, you would use a query to do the export and
name the calculated field Deadline. In the query builder it would look like:
DeadLine: DateAdd("d", -2, [BidDate])
Or when you populate the table to export to project, you would use the same
technique.
--
Dave Hargis, Microsoft Access MVP
:
One of Two ways: 1) I already have it set up to export to an excel file and
then read that file into project. It involves three programs, but it works
flawlessly.
or 2) I'm working on writing a macro which would save the data I wanted in a
new Access table, and then i'm going to try and directly import that into
Project, bypassing the Excel file.
Either way the date has to be figured out in Access because when it is
imported it has to be imported into the Deadline column in Project (you can't
enter a formula for that column) and the excel files are recreated each time
the info is updated (~1/day) so making a separate column in excel to
calculate it would do now good because it was just be deleted the next time
the info was uploaded.
Let me know if any of this doesn't make sense, and thanks for your help.
Scott
:
How are you moving the data from Access to Project?
--
Dave Hargis, Microsoft Access MVP
:
Big picture: User inputs info, including bid date in Access, which stores it
in a database. Through a series of steps that database ends up in Project.
I need, as a deadline for Project, the date 2 working days prior to the bid
date. It needs to be autocalculated, and it has to happen prior to being in
Project, i.e. while in Access. So is there a way to autocalc one date from
another, either in a table or a form, less 2 working days?
thanks
scott
:
What I am saying is that you do not put it in the table.
It is a calculated value. You do not store calculated values in tables.
You perform the calculation when you need the value.
--
Dave Hargis, Microsoft Access MVP
:
As in have it calculated within the table? If i insert a new column, called
"Deadline" how do i get that to be two workdays previous to the existing
column "Bid date", also in that table.
thanks
scott
:
Your code would be correct; however, it should not be stored in a table. It
is a calculated value based on a value you already have stored. The correct
thing to do is to use the calculation where ever you need the value.
--
Dave Hargis, Microsoft Access MVP
:
I have a form which acquires info from the user, including a date. I would
like to put another spot on the form, invisible to the user, which would be
two work days prior to that day. later on i use this date in tables which
are auto-linked to the form. i'm trying to use visual basic, with something
to the effect of
Private Sub Deadline_AfterUpdate()
Me.Deadline = Me.BidDate - 2
End Sub
but that is just a guess based on the other code I was given. The syntax
could be way off.
thanks
scott
