where statement

[Dries]

Hi Guys,

I got the following problem I have a table

Nr Country Cost
1234 DE 1.21
1234 NL 1.28
1234 BE
1234 UK
1235 DE 1.98

Now the purpuse of my query should be that if the Cost field is empty it
should take the cost of the DE country of the number so this should be the
result

Nr Country Cost
1234 DE 1.21
1234 NL 1.28
1234 BE 1.21
1234 UK 1.21
1235 DE 1.98

Does anyone have an idea on how to solve this?

 

[kingston via AccessMonster.com]

You can do this with two queries. The first one simply finds all the records
where Country='DE'. The second one contains the table and the first query
with a left join (show all table records and only those query records that
match) on Nr. Output the first two fields from the table, and as the third
field use something like this:

CostNew: IIF(IsNull(

.[Cost]),[Query].[Cost],

.[Cost])

 

[Jerry Whittle]

SELECT Dries.Nr,
Dries.Country,
Dries.Cost,
IIf(IsNull([Cost])=False, [COST],
DLookUp("[Cost]","Dries","[Nr] =[Nr]
AND [Country] =[Country] ")) AS TheCost
FROM Dries;