Dynamic unbound textbox on a continous form

[Jess]

I have a continuous form with a bound textbox displaying numbers (txtnumber).
My form also has an unbound textbox –txtNumberSize- in its detail section
whose content must vary according to txtnumber. I would like txtNumberSize to
show “between 100 and 1000†when txtnumber is greater than 100 but less than
1000; “negative†when txtnumber is less than zero; “zero†when the txtnumber
is zero; and “bigger than 1000†when the showed number is bigger than 1000.
For the sake of brevity I have listed these four cases but there are actually
more. How can I accomplish this without touching the underlying table my form
is based on?

Thanks in advance

 

[Jack Leach]

You can calculate the value by using an expression in the controlsource of
the unbound textbox:

=Iif([txtnumber] < 0, "Negative", "Positive")

But in your case, because that expression might get to be fairly long and
confusing based on your conditions, it may be prudent to move this to a
function instead. The function will need to be public (in a standard module,
not a form module, and declared as Public Function...), in which case you
refer to the function in the textbox's control source:

=fGetTextSize([txtnumber])

and in a module:

Public Function fGetTextSize(lngNum As Long) As String
Dim Ret As String

If lngNum < 0 Then Ret = "Negative"
If lngNum = 0 Then Ret = "0"
If (lngNum > 0) AND (lngNum < 1000) Then Ret = "Between 1 and 999.99"
'etc, etc

fGetTextSize = Ret
End Function


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Marshall Barton]

I have a continuous form with a bound textbox displaying numbers (txtnumber).
My form also has an unbound textbox –txtNumberSize- in its detail section
whose content must vary according to txtnumber. I would like txtNumberSize to
show “between 100 and 1000” when txtnumber is greater than 100 but less than
1000; “negative” when txtnumber is less than zero; “zero” when the txtnumber
is zero; and “bigger than 1000” when the showed number is bigger than 1000.
For the sake of brevity I have listed these four cases but there are actually
more. How can I accomplish this without touching the underlying table my form
is based on?

I suggest that you create a table (named Ranges) to
translate numbers to the descriptive text. The table could
have 3 fields:

Low High Descr
-9999 -1 Negative
0 0 Zero
1 100
101 999 Between 100 and 1000
1000 99999 bgger than 1000

Then your text box could use am expression like:

=DLookup("Descr", "Ranges", txtnumber & " Between Low And
High")

Or, you could join that table to your data table in the
form's record source query.