IIf...then use....: a beginner at work and very desperate 1

[Spikemaster]

First of all: i am new with access 2000, but i have bot been able to find the solution to my problem in help:
in my form i have two different address-fields: approximately 90 % of my records have entries in one field(address at work), while the other 10% have entries in the other field(address at home). i am making a query and want to make an expression for the following: if the field of 'adress at work' is empty, then automatically use the value that is in 'address at home'. Both fields are textual. I have tried all the options i could find in the Help-index, but nothing did the trick, so please can someone help me.
Thanks, Spike

 

[THoey]

Try this:

SELECT Name,
IIF(NOT ISNULL(WorkAdd), WorkAdd, HomeAdd)
FROM MyTable

I didn't test it, but it should work. Terry M. Hoey

 

[JoeMiller]

I believe this will work instead:

SELECT Name, IIf(IsNull(WorkAdd)=False, WorkAdd, HomeAdd) As Address
FROM MyTable;

HTH Joe Miller
joe.miller@flotech.net

 

[Spikemaster]

Thanks alot guys for the help: i am experiencing more trouble because i have to translate everything to dutch first. this is what i managed to copy from your suggestions:
SELECT [Adressen]![AdresWerk],
IIf(IsNull( [Adressen]![AdresWerk])=False, [Adressen]![AdresWerk], [Adressen]![Adres])
FROM [Adressen]
translation:
'Adressen' is the table
'adreswerk' is workadd
'adres' is homeadd

If i use that expression i get a syntax-error.(??) All i want is for the query to automatically select the homeadd if there is no workadd...do i have to implement this expression the field-cell or the criteria-cell?tried both but no success.
I know, more questions but i am very grateful because you prvented me from fysically abusing my computer.
Thanks, Spike

 

[Chance1234]

another suggestion create a union query

SELECT [Addressen].AdresWerk AS Address
FROM [Adressen]
WHERE (([Addressen].adresswerk) Is not Null)));

UNION SELECT [Addressen].Adres AS Address
FROM [Adressen]
WHERE (([Addressen].adresswerk) Is not Null)));

Chance

 

[JoeMiller]

The problem is that the !'s should be periods (.) You've also got extra spaces in there. Try this:

SELECT [Adressen].[AdresWerk],
IIf(IsNull([Adressen].[AdresWerk])=False,[Adressen].[AdresWerk],[Adressen].[Adres]) As Expr1 FROM [Adressen]
Joe Miller
joe.miller@flotech.net

 

[Spikemaster]

well many thanks for helping me with this problem
it was Joe's fomula that did it in the end:

Expr1: IIf(IsNull([Adressen].[AdresWerk])=Onwaar;[Adressen].[AdresWerk];[Adressen].[Adres])

'onwaar' means false by the way. When i implement this expression under 'criteria' of 'adreswerk' it basically cuts out the gaps, whilst if i place it as a field it replaces all the gaps in 'adreswerk=workadd' with 'adres=homeadd'.
thanks, Spike