something with "IF"

[taco]

Hi everybody...

I'm really out of time and thanks in advance for your help.
First question is..
There are three text boxes in a form. third one must get an automatic value
after second one updated. I don't know how to write the code..
If first box is between "x" and "y" and second box is between "z" and "q"
third box must be "a". From a to g there are 7 different values for third box
and for each group 6 different situation.

second question..

I would like to have a search form in my database. when user enters via
combo boxes the criterias as "nationality" "city" "amount of trade; (between
"x" and "y")" will be able to see the customers from that nation and from
that city and trade amount between mentioned amounts.

thanks a lot

 

[Arvin Meyer [MVP]]

Assuming the values for the letters are real numbers. If they are variables,
you need to dim and set them.

Sub Text2 AfterUpdate()
If Text1 >= x And Text1 <=y Then
If Text2 >= z And Text2 <= q Then
Text3 = "some value"
End If
End If
End Sub

You might also use a Select Case statement (or 2 if necessary) if there are
multiple possible values.

There is an example of using 2 list boxes to find a record or records on my
website. Just use the logic and add the 3rd list box, or combo box:

http://www.accessmvp.com/Arvin/Combo.zip

 

[Larry Linson]

There are three text boxes in a form. third
one must get an automatic value after second
one updated. I don't know how to write the code..
If first box is between "x" and "y" and second
box is between "z" and "q" third box must be "a".

Arvin has given you a solution for this much -- with the limitations he
described.

From a to g there are 7 different values for third box
and for each group 6 different situation.

It's not clear what this has to do with anything.

The questions that get the best, most accurate answers are precise and
concise (and clear). This one is, perhaps to concise, but not precise nor
clear.

If you can clarify, perhaps someone can give you a helpful suggestion.

Larry Linson
Microsoft Access MVP

 

[taco]

first of all thanks a lot for your time and answer.

and let me try to repeat my question, this time as easier to understand.

I have three text boxes in a form. user enters data with first two boxes.
after second one is updated, third one must get value according to first two
data.

when first box is between "60" and "120", ıf second one is between "200" and
"400"
third box should get "3"
or
when first box is between "120" and "180", if second one is between "100"
and "200"
third box should get again "3"

there are 6 different situation for third box to get "3"
and there are 8 different categories for third box and each category has 6
different situation.

if any part of the situation is out of rule, third box should remain blank.
for example first box is 150, it is between 120 and 180. but second one is
250. so this value is not between 100 and 200. so now third box should remain
blank. Or should get a different category number which can be explained in
another condition, like;

when first box is between "120" and "180", if second one is between "200"
and "300"
third box should get "2"

I hope it is more clear now..

once more thanks a lot..

"Larry Linson":

 

[taco]

Thanks a lot.. I'm gonna try these codes tonight and I'll let you know if
they worked out..

"Arvin Meyer [MVP]":

 

[Steve Sanford]

It sounds like there might be 40 to 48 conditions. That's going to get ugly
quick.

With that many conditions it might be easier and cleaner to use a table and
code to look up get the result for the third text box.

The table, lets call it "tblRules", could have 6 fields:

name type
lngRules_PK AutoNumber
intFirstBegin Integer
intFirstEnd Integer
intSecondBegin Integer
intSecondEnd Integer
intThird Integer

where "First", "Second" & "Third" refer to the text boxes.


The first and second text boxes have code in the after update event. The
code would look something like this: (watch for line wrap!)

'----------------------------------------------

Private Sub tbFirst_AfterUpdate()
GetThirdValue
End Sub

Private Sub tbSecond_AfterUpdate()
GetThirdValue
End Sub

Sub GetThirdValue()
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim strSQL As String
Dim varT1 As Integer
Dim varT2 As Integer

varT1 = Nz(Me.tbFirst, 0)
varT2 = Nz(Me.tbSecond, 0)

If Not IsNull(varT1) And Not IsNull(varT2) Then
Set d = CurrentDb

strSQL = "SELECT intThird FROM tblRules"
strSQL = strSQL & " WHERE (intFirstBegin <= " & varT1 & " AND
intFirstEnd >= " & varT1 & ")"
strSQL = strSQL & " And (intSecondBegin <= " & varT2 & " AND
intSecondEnd >= " & varT2 & ");"
' Debug.Print strSQL
Set r = d.OpenRecordset(strSQL)

' might need more limits if recordset returns
' more than one record
If Not r.BOF And Not r.EOF Then
Me.tbThird = r.Fields("intThird")
Else
Me.tbThird = Null
End If

r.Close
Set r = Nothing
Set d = Nothing

End If
End Sub
'----------------------------------------------

The code probably needs an error handler also.

Just an idea....

HTH

 

[taco]

The formula for IF statement worked perfectly.. thanks a lot.. but the sample
form and database is not doing exactly what I wanted.
let me tell you like that..

imagine an unbound form.. contains three combo
boxes;(UnitPrice,UnitsInStock,UnitsOnOrder) and one checkbox;(Discountinued)

when user fills these boxes, for example; "Show me Discontinued Products
with "x" unit price and with "y" stock amount" a report comes as print
preview with the list of products according all criterias.

thanks a lot for your time

"Arvin Meyer [MVP]":

 

[taco]

thanks a lot Steve.. I really appreciated..

"Steve Sanford":