Hi,

I am having a few problems with this one query. What I want to do is return the left-most value which is recorded in a table/query. I am using the Nz function, and keep getting syntax errors.

With this I mean, for example:

I have a table with the following

ID----Value1----Value2----Value3----Value4
-------------------------------------------
1-----3----------NULL-------4-------NULL
2-----NULL--------8---------4-------6
3-----NULL-------NULL------2-------NULL
4-----4------------9--------NULL----4
(I include a sample DB attached at the bottom to express myself better than this dodgy table model above :D )

Now what I want in this query, is a calculated field, e.g. FIRSTVALUE, which checks the left most value in the table/query and returns it.
E.g it should do the following:
For ID=1, FIRSTVALUE=3
For ID =2 FIRSTVALUE=8
FOR ID=3, FIRSTVALUE=2
For ID=4, FIRSTVALUE=4

I was suggested in using the Nz() function, and what I did was enter the following in the field box of the QBD grid:
FIRSTVALUE:Nz([Value1], Nz([Value2], Nz([Value3], [Value4])))

I keep getting syntax errors though, saying that a character is missing etc.

Please can someone help me either to get the right syntax for this, or suggest another(maybe better?) way of getting the info i need(which is the most-left value)

Kind Regards,

JP

Making a field in your query based on the Switch function might do the job,

FieldName:Switch([Value1] Is not Null,[Value1],[Value2] Is Not Null,[Value2],[Value3] Is Not Null,[Value3],[Value4] Is Not Null,[Value4])

The Switch function returns the first true answer.

I know very little about code but I think Case in coding is similar.

Mike

The table in your zip had 0s not nulls , assuming nulls then
firstvalue: IIf(Not (IsNull([value1])),[value1],IIf(Not (IsNull([value2])),[value2],IIf(Not (IsNull([value3])),[value3],[value4])))
will work, but there maybe neater solutions.

Brian

firstvalue: IIf(nz([value1])<>0,[value1],IIf(nz([value2])<>0,[value2],IIf(nz([value3])<>0,[value3],[value4])))

alows for 0s or nulls

Brian

Thanks for the replies guys.

I tried both your suggestions, but to no avail! Did you actually try out your suggestions on the sample DB? :confused:

Mike375,

When entering the code in the field box, i get a syntax error which points to the first comma.

Brianwarnock,

Same thing happens with your first suggestion, i.e. the comma. With your second suggestion, the syntax error points that there is possibly an operand with no operator.

Please help!

Put Brian's second expression:-

firstvalue: IIf(nz([value1])<>0,[value1],IIf(nz([value2])<>0,[value2],IIf(nz([value3])<>0,[value3],[value4])))


in the Field: row of a column in the query grid, not in the SQL statement.
.

Jon K,

Yes, that is what I have been doing the whole time! ;)
(check my first post, where I said that I am using it in the QBD grid)

I just pasted the expression in your database. It ran fine. No error.
.

Thanks for sending that example back to me Jon K.

It actually ran fine on my side also.
The problem I had was that I am using a German version of MS Access, and somehow it did not understand the term Iif. Strange, cause it can translate other terms, like Nz, True, IsNull etc.

Well I got it to work now and would like to thank you all for being so kind in helping me.

Regards,

Jean

Back from my usual 4 day weekend :D glad Jon K helped sort it.
A couple of points crossed my mind which are probably irrelelevent but as I don't know the full story of your data I will mention for completeness

1. As it stands the IIf clause will pick the first nonzero value Positive or negative

2. The query does not handle all 4 values being 0/null
Can this happen? If so what do you want to do?
To not display the row <>0 in the criteria
to display a zero change the last parameter of the IIf to nz([value4],0)

Access and German, ye gods!! What's the help like :eek:

Brian

Hi Brian,

2. The query does not handle all 4 values being 0/null
Can this happen? If so what do you want to do?

This should not happen, but I might include it just to avoid any disasters. Thanks for the tip.

Access and German, ye gods!! What's the help like

Well as long as your German is perfect( and mine is not ) the word HELP is a bit misleading. I have to look at MS online help the whole time, and searching for the right thing is quite a pain.
Oh well, at least I hope to apply my language skills somewhere one day - e.g. ordering beer at the Oktoberfest after a few too many... :D