match values

[Belinda7237]

I have two fields in a query:

closed date
final closed date

In my dataset I sometimes have property ids listed twice but both date
fields are not always filled in. I want the query to fill in the date field
that is blank with the corresponding date field of the same property id is
presented.

Example:

Property id closed date final closed date
123 1/1/2009
123 2/3/2009

In the example above I would want the query to update line one with a final
closed date of 2/3/2009 and I would want line 2 to be filled in with a closed
date of 1/1/2009. I still want both line items to show up - i just want to
make sure the dates are filled in.

How would I accomplish this?

 

[John Spencer]

Do you want to permanently update the fields or just show the fields filled in
the query?

To just do it in a query you can try the following. This assumes that
Property Id is a number field and not a text field.

Field: Closed: Nz([Closed Date],DMax("[Closed Date]",
"[Name of Table]","[Property ID] =" & [Property ID]))

You can do a similar thing for Final Closed Date.

If you want to permanently update the records you can use an update query and
set the criteria under the field(s) to IS NULL (use one line of criteria for
each field). And the UPDATE TO would be
Nz([Closed Date],DMax("[Closed Date]","[Name of Table]"
,"[Property ID] =" & [Property ID]))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

What you really need is a new table to handle the dates better.

tblEvents
Event_ID Property id EventDate EventType
1 123 1/1/2009 Closed
2 123 2/3/2009 Final Close

The Property ID field would be the foreign key field to join with the
Property table. This way you could query all the events by the EventDate and
EventType without having to look into various columns. You also wouldn't have
a problem with be null fields like you have now.