Need another If/then code

[Guest]

The If/then code is a little confusing to me and I'm having trouble with
another calculated field. If someone could help me (again), I would be
greatly appreciative!! I keep putting in what I think is the code, but I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[Douglas J. Steele]

You can use If, ElseIf and Then:

If xyz >= .01 And xyz < 1.00 Then
Answer = X
ElseIf xyz >= 1.00 And xyz < 10.00 Then
Answer = XX
ElseIf xyz >= 10.00 And xyz < 25 Then
Answer = XXX
etc.
End if

or you can use Select Case

Select Case xyz
Case .01 To 0.99
Answer = X
Case 1.00 To 9.99
Answer = XX
Case 10.00 To 24.99
Answer = XXX
etc.
End Case

 

[David C. Holley]

I would go with using a SELECT CASE which would be

Select Case lngNumber
Case 0.01 To 0.99
resultValue = 1
Case 1 To 9.99
resultValue = 2
Case 10 To 24.99
resultValue = 3
Case Else
resultValue = 4
End Select

I've never used a Select Case in this particular manner. When I tested
it some of the values returned where not what I expected. 1 returned 1
as opposed to 2. That should get you started though

 

[Graham R Seach]

Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the newsgroup,
so that everyone can benefit from both the question and the answer. It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about what X,
XX and XXX are. Is there some numerical progression or mathematical pattern
to the values you want returned? If not, then this is going to be a massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[David C. Holley]

Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves learn.

 

[Guest]

So sorry guys!! This is my first time posting help questions on this forum,
and for that matter, I just found this forum TODAY! Sure glad it's here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one to
work for me. I must be missing some syntax somewhere, and I think that may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated field
that comes from field1. There are a total of 6 If/thens. Can this be done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and 9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack thereof!!!
It seems to be simple enough, but I think I have just tried too hard for too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy

 

[Duane Hookom]

I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an expression,
you edit data.

 

[David C. Holley]

What is the big picture of what you're trying to do? If you need to go
up to 500, you may want to seriously think about creating a table with
the various values and then using DLOOKUP()

The table design would look something like...

startValue endValue percentage
..01 .99 .10
1.00 9.99 .20
(and so on)

Then you would use DLookup() to get the percentage as in

DLookup("percentage", tblName, variable & ">= startValue and " &
variable & "=< endValue"

 

[Guest]

Hi Duane! Thanks for your suggestion. I will try that; however, I already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table in
a form. I just have these two fields I need fixed: For example: when 9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my project
(I think). Thanks for your input!!!

Wendy

I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an expression,
you edit data.
--
Duane Hookom
MS Access MVP

So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and 9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy

 

[Duane Hookom]

In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 & ">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP

Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table
in
a form. I just have these two fields I need fixed: For example: when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP

So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one
to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:

Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:
Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message

The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would
be
greatly appreciative!! I keep putting in what I think is the code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[John Nurick]

If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX
(and so on until 500.00 or more)

There are gaps between 0.99 and 1.00 and so on. What should happen if
xyz is (say) 0.995?

 

[David C. Holley]

You don't have to do any additional linking or relationships. My
creating a new table, you're just creating a place to hold the data. The
DLookup() is the method that would reach out and grab the data that you
need.

Hi Duane! Thanks for your suggestion. I will try that; however, I already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table in
a form. I just have these two fields I need fixed: For example: when 9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my project
(I think). Thanks for your input!!!

Wendy

:

I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an expression,
you edit data.
--
Duane Hookom
MS Access MVP

So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and 9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:


Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves learn.

Graham R Seach wrote:

Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------



The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would be
greatly appreciative!! I keep putting in what I think is the code, but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[David C. Holley]

I was assuming that you were dealing with currency. In that case use

..01 - 1.00
1.00 - 10.00
10.00 - 25.00

and go with xyx >= .01 and xyz < 1.00
that will fill in the gaps

 

[Guest]

Yes, it is currency, so there would be no gaps. I haven't tried creating the
additional table yet to apply your suggestions. I really do appreciate
everyone's time with this and will repost again once I have tried them.
Thanks so so much!!!
Wendy

I was assuming that you were dealing with currency. In that case use

..01 - 1.00
1.00 - 10.00
10.00 - 25.00

and go with xyx >= .01 and xyz < 1.00
that will fill in the gaps

 

[Guest]

Hi Duane!
I created the table, added the following code you suggested to the necessary
field, and I got an error that says "Can't find macro 'Me' ". Macro or macro
group does not exist..." Maybe I've done something wrong. My new table has
these fields: MinNum, MaxNum, Value.
Thanks for your help.
Wendy

In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 & ">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP

Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table
in
a form. I just have these two fields I need fixed: For example: when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP


So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one
to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:

Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:
Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message

The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would
be
greatly appreciative!! I keep putting in what I think is the code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[David C. Holley]

The ME is a reference to the form. When used in a Form module, its a
quick way to reference a control or form property as in

Me.txtControlName versus [Forms]![frmReservations]![txtControlName]

But if you have the code in the afterUpdate event of the text box, it
should work without any problems.

Hi Duane!
I created the table, added the following code you suggested to the necessary
field, and I got an error that says "Can't find macro 'Me' ". Macro or macro
group does not exist..." Maybe I've done something wrong. My new table has
these fields: MinNum, MaxNum, Value.
Thanks for your help.
Wendy


:

In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 & ">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP

Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table
in
a form. I just have these two fields I need fixed: For example: when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

:


I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP



So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one
to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:


Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:

Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message


The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would
be
greatly appreciative!! I keep putting in what I think is the code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[Guest]

I hate to be a nuisance with this, I really do....but it's not working for
me. This is what I have in the AfterUpdate event of the "Price" field:
[Forms]![Listings]![Fee]=DLookUp("Value","ValueLookUp",[Forms]![Listings]![Price] & ">= MinNum AND " & [Forms]![Listings]! [Price] & "=< MaxNum")

When I tried putting the same code in a macro, I get a syntax error that an
operator is missing. I linked the macro to the OnClick event in the "Fee"
field. But the macro doesn't seem to work with that code.

My hope is to calculate the correct fee in the "Fee" field when a certain
price is entered in the "Price" field. The price ranges carry a different
fee.

This is frustrating, isn't it??? Thanks for any input!

Wendy

The ME is a reference to the form. When used in a Form module, its a
quick way to reference a control or form property as in

Me.txtControlName versus [Forms]![frmReservations]![txtControlName]

But if you have the code in the afterUpdate event of the text box, it
should work without any problems.

Hi Duane!
I created the table, added the following code you suggested to the necessary
field, and I got an error that says "Can't find macro 'Me' ". Macro or macro
group does not exist..." Maybe I've done something wrong. My new table has
these fields: MinNum, MaxNum, Value.
Thanks for your help.
Wendy


:

In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 & ">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP



Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table
in
a form. I just have these two fields I need fixed: For example: when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

:


I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP



So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one
to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:


Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:

Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message


The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would
be
greatly appreciative!! I keep putting in what I think is the code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[Duane Hookom]

You must enter the event procedure code window to use code. You can't just
paste the code into the property.

--
Duane Hookom
MS Access MVP

I hate to be a nuisance with this, I really do....but it's not working for
me. This is what I have in the AfterUpdate event of the "Price" field:
[Forms]![Listings]![Fee]=DLookUp("Value","ValueLookUp",[Forms]![Listings]![Price]
& ">= MinNum AND " & [Forms]![Listings]! [Price] & "=< MaxNum")

When I tried putting the same code in a macro, I get a syntax error that
an
operator is missing. I linked the macro to the OnClick event in the "Fee"
field. But the macro doesn't seem to work with that code.

My hope is to calculate the correct fee in the "Fee" field when a certain
price is entered in the "Price" field. The price ranges carry a different
fee.

This is frustrating, isn't it??? Thanks for any input!

Wendy

The ME is a reference to the form. When used in a Form module, its a
quick way to reference a control or form property as in

Me.txtControlName versus [Forms]![frmReservations]![txtControlName]

But if you have the code in the afterUpdate event of the text box, it
should work without any problems.

Hi Duane!
I created the table, added the following code you suggested to the
necessary
field, and I got an error that says "Can't find macro 'Me' ". Macro or
macro
group does not exist..." Maybe I've done something wrong. My new table
has
these fields: MinNum, MaxNum, Value.
Thanks for your help.
Wendy


:


In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 &
">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP



Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not
real
knowledgeable about linking and relationships between more than one
table
in
a form. I just have these two fields I need fixed: For example:
when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

:


I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression.
Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you
query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP


message

So sorry guys!! This is my first time posting help questions on
this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I
tried
putting both suggestions into my field and I just can't get either
one
to
work for me. I must be missing some syntax somewhere, and I think
that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this
be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:


Yes, (SERIOUSLY) please tell us if what we've suggested doesn't
work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:

Wendy just sent me an email because she didn't like the two
answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the
answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or
mathematical
pattern
to the values you want returned? If not, then this is going to be
a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message


The If/then code is a little confusing to me and I'm having
trouble
with
another calculated field. If someone could help me (again), I
would
be
greatly appreciative!! I keep putting in what I think is the
code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[David C. Holley]

Let me guess, you put the entire line of code into the AfterUpdate
property listed in the Events section. Using VBA is NOT like using a
macro. To utilize a VBA event procedure, select [Event Procedure] for
the AfterUpdate event. You'll then see a box with three elipses appear,
click on that and you'll view the event procedure in the VBA Editor with
code that looks like this...

Private Sub cboOrigination_AfterUpdate()

End sub

Add the line of code between the two statements.

The [Event Procedure] statement in the property sheets tells Access that
you have VBA code that should be executed when the event occurs. When it
does, Access jumps over to VBA, looks for the code and executes what it
finds.

I hate to be a nuisance with this, I really do....but it's not working for
me. This is what I have in the AfterUpdate event of the "Price" field:
[Forms]![Listings]![Fee]=DLookUp("Value","ValueLookUp",[Forms]![Listings]![Price] & ">= MinNum AND " & [Forms]![Listings]! [Price] & "=< MaxNum")

When I tried putting the same code in a macro, I get a syntax error that an
operator is missing. I linked the macro to the OnClick event in the "Fee"
field. But the macro doesn't seem to work with that code.

My hope is to calculate the correct fee in the "Fee" field when a certain
price is entered in the "Price" field. The price ranges carry a different
fee.

This is frustrating, isn't it??? Thanks for any input!

Wendy


:

The ME is a reference to the form. When used in a Form module, its a
quick way to reference a control or form property as in

Me.txtControlName versus [Forms]![frmReservations]![txtControlName]

But if you have the code in the afterUpdate event of the text box, it
should work without any problems.

Hi Duane!
I created the table, added the following code you suggested to the necessary
field, and I got an error that says "Can't find macro 'Me' ". Macro or macro
group does not exist..." Maybe I've done something wrong. My new table has
these fields: MinNum, MaxNum, Value.
Thanks for your help.
Wendy


:



In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 & ">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP




Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table
in
a form. I just have these two fields I need fixed: For example: when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

:



I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP




So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one
to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:



Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:


Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message



The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would
be
greatly appreciative!! I keep putting in what I think is the code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy

 

[Guest]

Yep, that's what I did. Okay, so I've done it the way you say in VBA and I
am getting this message: "Syntax error - missing operator in query
expression '15.33 >=MinNum AND 15.33 <MaxNum." ($15.33 was the price I
plugged into the field).

I must not have the spaces right, or the quotes (") right, or something.

P.S., as you can tell, I'm not real knowledgeable in the technical stuff,
like VBA and values. Thanks a lot for helping out!

Let me guess, you put the entire line of code into the AfterUpdate
property listed in the Events section. Using VBA is NOT like using a
macro. To utilize a VBA event procedure, select [Event Procedure] for
the AfterUpdate event. You'll then see a box with three elipses appear,
click on that and you'll view the event procedure in the VBA Editor with
code that looks like this...

Private Sub cboOrigination_AfterUpdate()

End sub

Add the line of code between the two statements.

The [Event Procedure] statement in the property sheets tells Access that
you have VBA code that should be executed when the event occurs. When it
does, Access jumps over to VBA, looks for the code and executes what it
finds.

I hate to be a nuisance with this, I really do....but it's not working for
me. This is what I have in the AfterUpdate event of the "Price" field:
[Forms]![Listings]![Fee]=DLookUp("Value","ValueLookUp",[Forms]![Listings]![Price] & ">= MinNum AND " & [Forms]![Listings]! [Price] & "=< MaxNum")

When I tried putting the same code in a macro, I get a syntax error that an
operator is missing. I linked the macro to the OnClick event in the "Fee"
field. But the macro doesn't seem to work with that code.

My hope is to calculate the correct fee in the "Fee" field when a certain
price is entered in the "Price" field. The price ranges carry a different
fee.

This is frustrating, isn't it??? Thanks for any input!

Wendy


:

The ME is a reference to the form. When used in a Form module, its a
quick way to reference a control or form property as in

Me.txtControlName versus [Forms]![frmReservations]![txtControlName]

But if you have the code in the afterUpdate event of the text box, it
should work without any problems.

Wendy Anderson wrote:

Hi Duane!
I created the table, added the following code you suggested to the necessary
field, and I got an error that says "Can't find macro 'Me' ". Macro or macro
group does not exist..." Maybe I've done something wrong. My new table has
these fields: MinNum, MaxNum, Value.
Thanks for your help.
Wendy


:



In the after update of the text box bound to field1, add this code:
Me.txtField2 = Dlookup("TheValue","tblXYZLookup",Me.txtField1 & ">=MinNum
AND " & Me.txtField1 & " <MaxNum")

--
Duane Hookom
MS Access MVP




Hi Duane! Thanks for your suggestion. I will try that; however, I
already
have this form set up with these fields linked to a table. I'm not real
knowledgeable about linking and relationships between more than one table
in
a form. I just have these two fields I need fixed: For example: when
9.99
is typed in Field1, then Field2 should automatically say .20 .

If I can get this one issue to work, I'll be just about done with my
project
(I think). Thanks for your input!!!

Wendy

:



I hate to send you off in another direction but you should not be
maintaining this "business rule/calculation" in an expression. Consider
creating a table:
tblXYZlookup
==============
MinNum
MaxNum
TheValue

Add records like:
..01 1 .1
1 10 .2
10 25 .35
25 50 .6
etc

You can then add tblXYZLookup to your query use a criteria in you query
like
WHERE XYZ >=MinNum and XYZ<MaxNum
This should select the proper [TheValue] for your expression.

When the min, max, and values change, you don't have to edit an
expression,
you edit data.
--
Duane Hookom
MS Access MVP




So sorry guys!! This is my first time posting help questions on this
forum,
and for that matter, I just found this forum TODAY! Sure glad it's
here,
it's been very helpful.

I did receive two helps on my problem with the If/then code. I tried
putting both suggestions into my field and I just can't get either one
to
work for me. I must be missing some syntax somewhere, and I think that
may
be my whole problem here. This is what I need:

If field1 is .01 to .99, then field2 = .10
If field1 is 1.00 to 9.99, then field2 = .20
If field1 is 10.00 to 24.99, then field2 = .35
If field1 is 25.00 to 49.99, then field2 = .60

And so on until field1 is 500.00 or more. Field2 is the calculated
field
that comes from field1. There are a total of 6 If/thens. Can this be
done
on a one-line formula, (example)
=IIf(Field1) >= .99, (Field1) = .10 : IIf(Field1) between 1.00 and
9.99,
(Field1) = .20)) and so on....

If so, I'm going insane with the brackets and parens, or the lack
thereof!!!
It seems to be simple enough, but I think I have just tried too hard
for
too
long and I'm burnt out with it.

Thanks to you all for your kindness and help!!!

Wendy



:



Yes, (SERIOUSLY) please tell us if what we've suggested doesn't work,
needs some work or is compeletely off base. It is how we ourselves
learn.

Graham R Seach wrote:


Wendy just sent me an email because she didn't like the two answers
suggested. Wendy, please always post follow-up questions to the
newsgroup,
so that everyone can benefit from both the question and the answer.
It's OK
to tell respondents that they didn't provide a suitable answer.

I take it you want a one-line formula.

To arrive at a one-liner, you need to be a tad more specific about
what
X,
XX and XXX are. Is there some numerical progression or mathematical
pattern
to the values you want returned? If not, then this is going to be a
massive
formula!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message



The If/then code is a little confusing to me and I'm having trouble
with
another calculated field. If someone could help me (again), I would
be
greatly appreciative!! I keep putting in what I think is the code,
but
I
seem to be missing something in the formula.
Example:
If xyz is .01 - .99, then answer is X
If xyz is 1.00 - 9.99, then answer is XX
If xyz is 10.00 - 24.99, then answer is XXX

(and so on until 500.00 or more)

Thanks so much for any help provided!!!
Wendy