Tabular Form with an Outer Join

  • Thread starter Thread starter David Gordon
  • Start date Start date
D

David Gordon

I have a tabular form based on an outer join query (all
open sales with weekly payments. Initially, for a given
week the payments will be null).

I would like to prepopulate the payment date with the new
end of week date. If I use a IIF(payment date is null,end
of week date,payment date) in the query I can not edit the
date payment on the form. I tried to initialize the date
on the form load, but that did not work either. What can I
do to prepopulate the date field, but allow me to update
the field?

Additionally, I would like to validate the date to make
sure it is within the current date i.e. date >= datediff
("y",-6,end of week date) and date <= end of week date. I
have tried putting in events for change and after update,
but these events seem to check every row on the form. Any
suggestions on where I can put this validate code?

Thanks for the help,
David
 
You cannot update a calculated field, but that won't stop you from selecting
the records you want.

In the SELECT clause of your query, select both the dates.
In the WHERE clause of your query, use the IIF() expression to choose only
the records you want.

For validation of the date, use the BeforeUpdate event procedure of the text
box (or the BeforeUpdate event of the Form if the validation requies
comparing two fields). It will be applied to the current row only (unless
the text box is unbound).
 
Allen,

The second solution worked like a charm.

As for the first solution, my problem is not getting the
right number of records. Rather, I am trying to default
the date field to the week ending date. Is there an event
I can use that will populate the date field when it is
null?

Thanks for your help.

David
 
You could use an Update query statement to set the value of the field if you
want the date to be set.

Using the expression in only the WHERE clause rather than both the SELECT
and WHERE clauses should not make any difference to which records are
selected.

An alternate approach would be to use Nz():
WHERE Nz([payment date], [end of week]) ...

BTW, are you sure that DateDiff("y"... is what you intend, rather than
DateDiff("d"... or DateDiff("yyyy"... ?
 
Back
Top