Value from form to be appended to table

  • Thread starter Thread starter Vikesh Singh
  • Start date Start date
V

Vikesh Singh

Hi. I can calculate the difference in days between to
dates with the DateDiff function in a form by editing the
control souce property. How do I append that value to the
underlying table (the table has no area for calculating
DateDiff - I've checked all the options). I need to use
the values to run a query.

Much appreciated.
 
Hi. I can calculate the difference in days between to
dates with the DateDiff function in a form by editing the
control souce property. How do I append that value to the
underlying table (the table has no area for calculating
DateDiff - I've checked all the options). I need to use
the values to run a query.
Click to expand...

Just do the calculation *in the Query itself*. There is no need nor
benefit to storing the calculated datediff in the table!

You can type

Duration: DateDiff("d", [startdate], [enddate])

in a vacant Field cell in the query grid to get a field in the Query
which can be used for sorting, searching, or anything else you can do
with a table field (other than editing it of course).
 
Hi,
I have a similar question about storing calculated data in the TABLE.
My scenario is I have a calculated date field based on another date
field in my form the expression is |=DateAdd("d",90,[RETURNED])| so my
calculated field is 90 days plus the return date. Now this calculated
date will never change once entered can I or how can I have this update
to the table.....I understand the philosophy of "if it can be
calculated don't put in table" but I don't know how to build a query to
lets say give me all records for the month of MAY based on the
calculated field... Hope that doesn't sound to confusing....

any suggestions will be appreciated
Thanks in advance

John
 
I don't know how to build a query to
lets say give me all records for the month of MAY based on the
calculated field... Hope that doesn't sound to confusing....
Click to expand...

Create a Query. Put the calculation expression in a vacant Field cell,
e.g. by typing

DateDue: DateAdd("d", 90, [CheckOutDate])

in the field cell.

This calculated field in the Query can then be used EXACTLY as you
would use a table field in a query - you can put a criterion on it
like

BETWEEN #5/1/04# AND #5/31/04#

or

BETWEEN DateSerial(Year(Date()), [Enter month number:], 1) AND
DateSerial(Year(Date()), [Enter month number:] + 1, 0)

or

BETWEEN Date() AND Date() + 7

to get all the duedates in the upcoming week.

You can sort by it, display it in forms or reports - anything you like
except edit it.
 
Back
Top