Need another If/then code

[David C. Holley]

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

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

 

[Guest]

Ooopppss! Sorry! Here is the code as I have it in VBA:

Me.txtInsertionFee = Dlookup("Value","ValueLookUp",Me.txtStartingPrice &
">=MinNum AND " & Me.txtStartingPrice & "<MaxNum)

Thanks!
W~

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

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.

Wendy Anderson wrote:

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

 

[David C. Holley]

Need to close the quotes around <MaxNum

Side note: Its not required, but I typically put spaces around the
operators (eg >= ) to help in reading.

Ooopppss! Sorry! Here is the code as I have it in VBA:

Me.txtInsertionFee = Dlookup("Value","ValueLookUp",Me.txtStartingPrice &
">=MinNum AND " & Me.txtStartingPrice & "<MaxNum)

Thanks!
W~


:

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

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.

Wendy Anderson wrote:


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

 

[Guest]

I got this code from a friend a little while ago who uses it in FileMaker
Pro. Looks like it should work, but it doesn't (another syntax error
message).......
IIf(StartingPrice="";Case(InsertionFee = "";"";InsertionFee < .99 >
0;.25;InsertionFee < 9.99 ;.35;InsertionFee < 24.99;.6;InsertionFee <
49.99;1.2;InsertionFee < 199.99;2.4;InsertionFee < 499.99;3.6;InsertionFee >
500;4.8;Case(StartingPrice > .01 < .99;.3;StartingPrice < 9.99
;.35;StartingPrice < 24.99;.6;StartingPrice < 49.99;1.2;StartingPrice <
199.99;2.4;StartingPrice < 499.99;3.6;StartingPrice > 500;4.8))

Do you see anything wrong with it?
thanks!
W~

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

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.

Wendy Anderson wrote:

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

 

[Guest]

ALL THAT BECAUSE OF A QUOTE MARK MISSING!! Well, you may be happy to know (I
know I am), that .... IT'S WORKING!!! Yeh!!!! Thanks to EVERYONE who
helped me through this ordeal....I've definitely learned a thing or two about
codes!!
Take Care!!!
W~

Need to close the quotes around <MaxNum

Side note: Its not required, but I typically put spaces around the
operators (eg >= ) to help in reading.

Ooopppss! Sorry! Here is the code as I have it in VBA:

Me.txtInsertionFee = Dlookup("Value","ValueLookUp",Me.txtStartingPrice &
">=MinNum AND " & Me.txtStartingPrice & "<MaxNum)

Thanks!
W~


:

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

Wendy Anderson wrote:

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.

Wendy Anderson wrote:


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

 

[David C. Holley]

Personally I would scrap it. Back in college, one of my professors had
two mantras 1) The true cost of developing code is not in how much it
costs to write the code, but how much it costs to fix the code when
something goes wrong and 2) If a third person can't easily understand
what your code is doing, you should probably rewrite it. I would go with
the table solution previsously mentioned or at minimum the
beforementioned Select Case.

I got this code from a friend a little while ago who uses it in FileMaker
Pro. Looks like it should work, but it doesn't (another syntax error
message).......
IIf(StartingPrice="";Case(InsertionFee = "";"";InsertionFee < .99 >
0;.25;InsertionFee < 9.99 ;.35;InsertionFee < 24.99;.6;InsertionFee <
49.99;1.2;InsertionFee < 199.99;2.4;InsertionFee < 499.99;3.6;InsertionFee >
500;4.8;Case(StartingPrice > .01 < .99;.3;StartingPrice < 9.99
;.35;StartingPrice < 24.99;.6;StartingPrice < 49.99;1.2;StartingPrice <
199.99;2.4;StartingPrice < 499.99;3.6;StartingPrice > 500;4.8))

Do you see anything wrong with it?
thanks!
W~


:

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

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.

Wendy Anderson wrote:


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

 

[David C. Holley]

If you haven't read the message that I just posted, read it. I didn't
specifically mention it, but in the satement that was posted given the
complexity of the code, there was a HIGH probablity that a single
punctuation mark would wack out the code and it would be a B(ear) to
figure out. Hence I would scrap it.

ALL THAT BECAUSE OF A QUOTE MARK MISSING!! Well, you may be happy to know (I
know I am), that .... IT'S WORKING!!! Yeh!!!! Thanks to EVERYONE who
helped me through this ordeal....I've definitely learned a thing or two about
codes!!
Take Care!!!
W~

:

Need to close the quotes around <MaxNum

Side note: Its not required, but I typically put spaces around the
operators (eg >= ) to help in reading.

Ooopppss! Sorry! Here is the code as I have it in VBA:

Me.txtInsertionFee = Dlookup("Value","ValueLookUp",Me.txtStartingPrice &
">=MinNum AND " & Me.txtStartingPrice & "<MaxNum)

Thanks!
W~


:



The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

Wendy Anderson wrote:


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.

Wendy Anderson wrote:



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

 

[Duane Hookom]

What he (David) said...

--
Duane Hookom
MS Access MVP

Personally I would scrap it. Back in college, one of my professors had two
mantras 1) The true cost of developing code is not in how much it costs to
write the code, but how much it costs to fix the code when something goes
wrong and 2) If a third person can't easily understand what your code is
doing, you should probably rewrite it. I would go with the table solution
previsously mentioned or at minimum the beforementioned Select Case.

I got this code from a friend a little while ago who uses it in FileMaker
Pro. Looks like it should work, but it doesn't (another syntax error
message).......
IIf(StartingPrice="";Case(InsertionFee = "";"";InsertionFee < .99 >
0;.25;InsertionFee < 9.99 ;.35;InsertionFee < 24.99;.6;InsertionFee <
49.99;1.2;InsertionFee < 199.99;2.4;InsertionFee <
499.99;3.6;InsertionFee > 500;4.8;Case(StartingPrice > .01 <
.99;.3;StartingPrice < 9.99 ;.35;StartingPrice < 24.99;.6;StartingPrice <
49.99;1.2;StartingPrice < 199.99;2.4;StartingPrice <
499.99;3.6;StartingPrice > 500;4.8))

Do you see anything wrong with it?
thanks!
W~


:

The next step is to copy & paste the actual code when you post. That
allows us to see what's going on.

Wendy Anderson wrote:

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.

Wendy Anderson wrote:


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


message



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