match values

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?
0
Utf
4/2/2010 12:16:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
542 Views

Similar Articles

[PageSpeed] 32

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

Belinda7237 wrote:
> 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?
0
John
4/2/2010 12:45:28 PM
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.

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Belinda7237" wrote:

> 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?
0
Utf
4/2/2010 1:07:01 PM
Reply:

Similar Artilces:

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Conditional format for unique values
Hello, I would like to set a conditional format to color each unique value a different color. It will also be applied to multiple occurences of the value. The problem is that the values are dynamic and may not always be the same number of occurences, in the same cell, etc. I have used the formula IF(ISNUMBER(COUNTIF($F$3:F3,F3)),ROW(),"")) to try to identify the occurences but creating a conditional format has proven out of my league! Please help! And thanks!! Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of ...

Change default search to Value, not formula?
Every time I start a word search, the default searches for fomulas instead of value. So everytime I have to open up the option button and change it to value instead of formula. How do I change that default setting? lbbss Saved from a previous post: Excel tries to help by remembering the last settings you used--except for the first search in that session. You can use that to your advantage. You could make a dummy workbook and put it in your xlStart folder. Have a macro in that workbook that does a find (and sets all the stuff the way you like). Then closes and gets out of the way. ...

Multiply all values by 10
Is there a way to multiply all values in my spreadsheet by 10? If so, how? Type the 10 in some random cell and copy. Select your target cells and Edit > Paste Special, and select the Multiply radio button. "RTimberlake" wrote: > Is there a way to multiply all values in my spreadsheet by 10? If so, how? First, make a copy of the workbook in case you make a typo or something, then put 10 in an empty cell, copy it, then select all values needed and do edit>paste special and select multiply -- Regards, Peo Sjoblom (No private emails please) "RTimberlake" ...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

if value is greater than # * by 20% if less * by 40
if price is higher than 30 dollars markup by 20% if less than 30$ markup by 40% Try this: For A1: (a price) The marked up price B1: =A1*IF(A1>30,1.2,1.4) Does that help? *********** Regards, Ron XL2002, WinXP "sypher" wrote: > if price is higher than 30 dollars markup by 20% if less than 30$ markup by 40% ...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

This forum does have value!
As year end approaches and I am considering upgrading to Money 2005, I found the message traffic extremely valuable. After this summers fiasco with the update to MSN Money locking us out of our local data file, I thought, or was hoping M$ would make an extra effort to make sure that Money 2005 was a significant improvement and without the assorted problems that I have found posted here. Some still unresolved. I have made my decision and I do not care how much work it is going to take me, bt I am going back to Quicken after giving Money a two year trial. This program is just not ready f...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

Index, Match, Min and Max question
I'm trying to retrieve a date (in column A) that corresponds with a Min and Max amount (in columns B:D). Basically I need to know when my amounts hit their Highs and Lows. Can someone help me with this please? Thanks, Maria Dates in column A; values in B Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0)) Returns the date corresponding the max value in B Is this what was needed? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email <mgriffiths@klmtel.net> wrote in message news:1194464476.732636.234500@50g2000hsm.googlegroups.com... >...

Scatter chart without numerical values
For some time now, when I create a scatter chart from tabulated (numerical) data, the chart does not display the x/y values, but the serquence numbers instead. This is similar to a bar chart, but should'nt happen with a scatter chart. The scatter charts have worked ok in the past. I'm using Excel 2000 in a company office environment where I have little control over the Excel installation/set-up. -- Regards Fred Are you really making an XY chart? If you make a Line chart with two numeric columns AND the first row have labels, Excel thinks both columns are y-data and uses 1,2,3...

How to get rid of #Value when multiplying
I am very new to Excel and I am trying to build an invoice sheet for my employer. Here is the scenario =F21*G21 But when I leave f21 and g21 blank I get #VALUE I need it to just be blank so I have a blank invoice. I also have =G37*9 if G37 is blank I get #VALUE Also my subtotal =SUM(H20:H44) if any field is blank i get #VALUE PLEASE HELP!! Submitted via EggHeadCafe - Software Developer Portal of Choice REPAIRING / REINSTALLING ASP.NET http://www.eggheadcafe.com/tutorials/aspnet/00a19e81-5b5a-4d96-8717-9f4b623c162f/repairing--reinstalling.aspx I assume you have a...

version matching
Do both people have to have the same version of Outlook running in order for Tasks and Appointments to sync up properly when emailed between clients? I'm seeing some irregularities between someone running Outlook in Office 2000 and mine which is part of Office 2003. Appointments with reminders sent from Office 2002 or later will show up as text in Outlook 2000 unless a hotfix is applied to the Outlook 2000 installation. Besides this issue, I am not aware of any others except for ..pst file type incompatibilities, easily solved on the 2003 side. -- Milly Staples [MVP - Outlook] ...

combo box recording incorrect values
I have a form with a combo box that looks up values from a table. There are three columns, ID, lngClient and txtCompany from tSuppliers. The cbo is bound on column 0. ID and lngClient are hidden. When I create a new record and select a supplier, say Alpha Inc. it records the next company on the list. In this case the ID for Alpha Inc. is 1 and next on the list is ID 2, Beta Inc. The record created is shows Beta. I have recreated the cbo, but I still get the same result. Any thoughts on what is causing this? Let me know if you need any clarification. Thanks in advance. PJ ...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

fuzzy logic matching in Excel
Is there such a thing as fuzzy logic matching in excel? For example, if I have Name Addr1 Addr2 Addr3 Davie Kings Road London England and Davie Kings Road - London This should match as a potential pair. How can I do this in Excel? Any ideas? Nothing built in. Davie wrote: > > Is there such a thing as fuzzy logic matching in excel? > > For example, if I have > Name Addr1 Addr2 Addr3 > Davie Kings Road London Eng...

Eliminating categories of zero values from X-axix #2
Hi , I have a range of sales data from 1 - 30 of a month, i will select the all range while creating chart.What i want when click on the chart it could eliminate the zero value items from the X-axis. Could i have your help on this ? ...

Sum values using cell with varying values
I have two columns, one with numeric values that I would like to sum if the value in the second column is not null. The values of the second column are either an 'X', a date (mm/dd) or blank. This will sum A1:A20 if B1:B20 is non-blank: =SUMIF(B1:B20,"<>",A1:A20) dmullen wrote: > > I have two columns, one with numeric values that I would like to sum if the > value in the second column is not null. The values of the second column are > either an 'X', a date (mm/dd) or blank. -- Dave Peterson ec35720@msn.com adjust the c6:c8 for your colu...

In excel, how do you asign numeric values to text or letters, eg..
How can I asign numeric values for text of letters, eg. A=1, B=2, C=3, etc. Hi if you have this kind of order try (letter in cell A1): =CODE(A1)-64 -- Regards Frank Kabel Frankfurt, Germany adefreitas wrote: > How can I asign numeric values for text of letters, eg. A=1, B=2, > C=3, etc. ...

using MATCH for multiple matches
Greetings Is it possible to use the MATCH function to list all matches in a column rather than just the first one, or is there another way of doing this? Many thanks. Mat, Not sure what you want to extract, but the easiest way is to autofilter your list: Data | Filter | Autofilter.... HTH, Bernie MS Excel MVP "Mat" <buffaloesinc@DELETE2REPLYhotmail.com> wrote in message news:c7diur$ksu$1@news5.svr.pol.co.uk... > Greetings > > Is it possible to use the MATCH function to list all matches in a column > rather than just the first one, or is there another way o...