TextBox value depends on certain conditions 2

[jean2002]

Hello,

I have a Text box based on a form, called e.g. txtFinalValue.
I want the value that displays in this textbox, to depend on something though.
For example, if I have the following columns in a query, which the recordsource for the text box is based on:

ID Value1 SubValue1 Value2 SubValue2
1 90 12 NULL NULL
2 87 11 76 21
3 NULL NULL 95 32


The text box should always display the latest ValueX, with Value1 being the later than Value2. If e.g. Value1 is NULL, then it should take the next available ValueX.
Thus, when the record selector is on ID=1, then txtFinalValue should display 90(Value1).
If ID=2, then txtFinalValue=87(Value1).
If ID=3 then txtFinalValue=95(Value2).

How can I do this with as little VBA coding as possible(if any at all?)

Regards,

Jean

 

[PHV]

Something like Nz(Value1,Value2) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jean2002]

Thanks PH,

OK that is fine for 2 columns then, but what about if I have a Value 3 and Value 4 column?

I tried something like this, but it doesn't work:

Nz(Value1, Nz(Value2, Nz(Value3, Value4)))

Regards,

Jean

 

[KenReay]

Hi

Nz(Value1,Value2)

"OK that is fine"

Nz(Value1, Nz(Value2, Nz(Value3, Value4)))

"but it doesn't work"

clearly the NZ() function works

would you care to elaborate on what you mean by "but it doesn't work", do you get a syntax error, do you get a result which you believe to be wrong ?




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jean2002]

Yeah what I mean is that I get a syntax error. (trying to enter this expression in the Expression builder of the ControlSource property of the text box)

When I leave out the "=" sign at the beginning of the expression (as in =Nz(Value1, Nz(Value2....), I get no syntax error, but the value in the text box comes up as #NAME?

Please, I am not that experienced yet in using these functions, but am very willing to learn and your help is much appreciated.

Regards,

 

[KenReay]

Hi

"When I leave out the "=" sign at the beginning of the expression (as in =Nz(Value1, Nz(Value2....), I get no syntax error, but the value in the text box comes up as #NAME?"

is the Control by any chance called Value1 or Value2 or value3 or value4 ?

If yes try renaming it to (say) txtValueX

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jean2002]

No, as I said in my first post, the textbox is based on a column in a query, called Value1, Value2 etc.:

For example, if I have the following columns in a query, which the recordsource for the text box is based on:

 

[KenReay]

Hi

Yes, I got that, but the text box is an object, and will therefore have a name, what is it?

or

do you have the Nz(..etc) in the query to form a calculated column?, in which case you need:

ValueX:Nz([Value1], Nz([Value2], Nz([Value3], [Value4])))


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jean2002]

I have a Text box based on a form, called e.g. txtFinalValue.

The text box is called e.g. txtFinalValue, but i do not see why that should matter.

do you have the Nz(..etc) in the query to form a calculated column?, in which case you need:

ValueX:Nz([Value1], Nz([Value2], Nz([Value3], [Value4])))

I tried using this in a query, by typing it into the "field" box of QBE, but it still gives me a syntax error....

 

[KenReay]

Hi

"The text box is called e.g. txtFinalValue, but i do not see why that should matter.", this matters becuase if you call the text box by a name which is the name of one of the columns in teh formula, you get a #name error

But clearly that is not your problem in this case

Would you like to post the SQL of the query ?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jean2002]

OK here goes the SQL....
There are 4 calculated columns in this query, namely BMI_L, BMI_R, BMI_VID and BMI_VTH. See them?

SELECT [tblVersuchspersonen_test].[VPNummer], [tblVersuchspersonen_test].[Nachname], [tblVersuchspersonen_test].[Vorname], [tblVersuchspersonen_test].[Abteilung], [tblVersuchspersonen_test].[Telefon], [tblVersuchspersonen_test].[GebJahr], [tblVersuchspersonen_test].[Alter], [tblVersuchspersonen_test].[AlterAuf], [tblVersuchspersonen_test].[Gewicht], [tblVersuchspersonen_test].[Sex], [tblVersuchspersonen_test].[Schuh], [tblVersuchspersonen_test].[StammlProp], [tblVersuchspersonen_test].[Kommentare], [tblVersuchspersonen_test].[VFG], [tblLaserscanmessungen_test].[KoerperH], [tblLaserscanmessungen_test].[BeckenB], [tblLaserscanmessungen_test].[BrustkorbB], [tblLaserscanmessungen_test].[BrustkorbT], [tblLaserscanmessungen_test].[FussB], [tblLaserscanmessungen_test].[FussH], [tblLaserscanmessungen_test].[FussL], [tblLaserscanmessungen_test].[HueftB], [tblLaserscanmessungen_test].[KnieL], [tblLaserscanmessungen_test].[KnieH], [tblLaserscanmessungen_test].[KopfB], [tblLaserscanmessungen_test].[KopfH], [tblLaserscanmessungen_test].[KopfT], [tblLaserscanmessungen_test].[OberarmL], [tblLaserscanmessungen_test].[ShulterB], [tblLaserscanmessungen_test].[UnterarmLmH], [tblLaserscanmessungen_test].[UnterarmU], [tblLaserscanmessungen_test].[StammL], [tblLaserscanmessungen_test].[TailenU], [tblLaserscanmessungen_test].[OberarmU], [tblLaserscanmessungen_test].[OberschenkelU], [tblLaserscanmessungen_test].[UnterschenkelU], [tblLaserscanmessungen_test].[NackenL], [tblLaserscanmessungen_test].[ScanDatum], [tblLaserscanmessungen_test].[ScanUhrzeit], [tblVersuchspersonen_test]![Gewicht]/(([tblLaserscanmessungen_test]![KoerperH]/1000)*([tblLaserscanmessungen_test]![KoerperH]/1000)) AS BMI_L, [tblRContourmessungen_test].[KoerperH], [tblRContourmessungen_test].[KoerperHm], [tblRContourmessungen_test].[StammL], [tblRContourmessungen_test].[KopfH], [tblRContourmessungen_test].[KopfB], [tblRContourmessungen_test].[KopfT], [tblRContourmessungen_test].[NackenL], [tblRContourmessungen_test].[ShulterB], [tblRContourmessungen_test].[OberarmL], [tblRContourmessungen_test].[UnterarmLmH], [tblRContourmessungen_test].[UnterarmU], [tblRContourmessungen_test].[BrustkorbB], [tblRContourmessungen_test].[BrustkorbT], [tblRContourmessungen_test].[TailenU], [tblRContourmessungen_test].[BeckenB], [tblRContourmessungen_test].[HueftB], [tblRContourmessungen_test].[KnieL], [tblRContourmessungen_test].[KnieH], [tblRContourmessungen_test].[FussH], [tblRContourmessungen_test].[FussB], [tblRContourmessungen_test].[Datum], [tblVersuchspersonen_test]![Gewicht]/(([tblRContourmessungen_test]![KoerperH]/1000)*([tblRContourmessungen_test]![KoerperH]/1000)) AS BMI_R, [tblVideomessungen_test].[KoerperH], [tblVideomessungen_test].[StammL], [tblVideomessungen_test].[KnieL], [tblVideomessungen_test].[KnieH], [tblVideomessungen_test].[OberarmL], [tblVideomessungen_test].[UnterarmLmH], [tblVideomessungen_test].[HalsL], [tblVideomessungen_test].[FussL], [tblVideomessungen_test].[BrustkorbB], [tblVideomessungen_test].[BeckenB], [tblVideomessungen_test].[BrustkorbT], [tblVideomessungen_test].[HueftB], [tblVideomessungen_test].[HalsU], [tblVideomessungen_test].[TailenU], [tblVideomessungen_test].[UnterarmU], [tblVideomessungen_test].[SchulterblattD], [tblVideomessungen_test].[KopfH], [tblVideomessungen_test].[KopfL], [tblVideomessungen_test].[KopfB], [tblVideomessungen_test].[FussH], [tblVideomessungen_test].[FussB], [tblVideomessungen_test].[AbsatzH], [tblVideomessungen_test].[Datum], [tblVersuchspersonen_test]![Gewicht]/(([tblVideomessungen_test]![KoerperH]/1000)*([tblVideomessungen_test]![KoerperH]/1000)) AS BMI_VID, [tblVThronmessungen_test].[KoerperH], [tblVThronmessungen_test].[AugenHSte], [tblVThronmessungen_test].[ReichWSte], [tblVThronmessungen_test].[ReichWnv], [tblVThronmessungen_test].[GriffachsenspannW], [tblVThronmessungen_test].[StammL], [tblVThronmessungen_test].[AugenHSitz], [tblVThronmessungen_test].[CervicalH], [tblVThronmessungen_test].[ShulterHSitz], [tblVThronmessungen_test].[EllenbogenH], [tblVThronmessungen_test].[OberarmL], [tblVThronmessungen_test].[UnterarmL], [tblVThronmessungen_test].[EllenbogengriffachsenL], [tblVThronmessungen_test].[KnieT], [tblVThronmessungen_test].[SitzT], [tblVThronmessungen_test].[KnieH], [tblVThronmessungen_test].[UnterschenkelL], [tblVThronmessungen_test].[SitzB], [tblVThronmessungen_test].[AbsatzH], [tblVThronmessungen_test].[Datum], [tblVThronmessungen_test].[Bekleidung], [tblVThronmessungen_test].[Zeit], [tblVersuchspersonen_test]![Gewicht]/(([tblVThronmessungen_test]![KoerperH]/1000)*([tblVThronmessungen_test]![KoerperH]/1000)) AS BMI_VTH
FROM (((tblVersuchspersonen_test LEFT JOIN tblRContourmessungen_test ON [tblVersuchspersonen_test].[VPNummer]=[tblRContourmessungen_test].[VPNummer]) LEFT JOIN tblVideomessungen_test ON [tblVersuchspersonen_test].[VPNummer]=[tblVideomessungen_test].[VPNummer]) LEFT JOIN tblVThronmessungen_test ON [tblVersuchspersonen_test].[VPNummer]=[tblVThronmessungen_test].[VPNummer]) LEFT JOIN tblLaserscanmessungen_test ON [tblVersuchspersonen_test].[VPNummer]=[tblLaserscanmessungen_test].[VPNummer];

Right, I want the first value of them. In simple layman's terms, if BMI_L is NULL, then check for a value in BMI_R. If BMI_R is also NULL, then check for a value in BMI_VID, and so on until it has found a value in one of these 4 calculated columns. Of course, if no value is found in any of the 4, return value NULL.
I would sort of know how to do this using nested if statements in a programming environment, but this one is really buggin me because I want it applied to a text box whose recordsource is this query.
Please have a look, and keep up the good support you have provided so far!

Thanks,

 

[PHV]

Why not simply create a new query on top of this posted ?
I guess the Nz imbrication should then works.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jean2002]

Nope,

created a new query, in design view, with the following in the "field" box:

BMI:Nz([BMI_L], Nz([BMI_R], Nz([BMI_VID], [BMI_VTH])))

still no luck...(syntax error)

 

[KenReay]

HI

OK, if you know how to do it in code, then why not:

(say) it is on a form

put code in OnCurrent Event

If IsNull([BMI_L]) Then
If IsNull([BMI_R] Then
If IsNull([BMI_VID]) Then
txtFinalValue = Nz([BMI_VTH],"")
Else
txtFinalValue = [BMI_VID]
End if
Else
txtFinalValue = [BMI_R]
Else
txtFinalValue = [BMI_L]
End if

I cannot explain why the things we have tried do not work, it looks to me as if they should, but there is clearly something we are missing

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jean2002]

Yip that worked now, but it really boggles me too why that was an error with using Nz() in the QBE...

thanks guys - stars for you

 

[jean2002]

Well just got back to this problem...
EEEESSSHH

It turns out that I will have to use this Nz function in the QBD grid (pardon my previous misspelling "QBE"), because I will be basing a search form on this query.

Can I maybe send this sample DB to someone who can then check it out?

Regards,

JP

 

[jean2002]

Solved it now...

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.

Seems there is a bit of a language barrier problem at Microsoft...

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

Regards,

Jean