Non null results input to a query

[Guest]

I apologize for my ignorance. but I'm doing this for the first time and need
the advice of someone in the know.

I'm creating the frmSales based on qrySales. My sales revenues are based on
either (not both) city or county populations. Hence, every sale created by
my form will be assigned to a population bracket to provide a base price
multiple once I've chosen either the [City] or [County] comboboxes on my
form. One of those two fields will ALWAYS be null. The [PopulationBracket]
field to ultimately be populated by my choice resides only on qrySales, not
on the form. Here's the code (I think it's correct, but if you think
otherwise I'd appreciate the correction) which gets part of the job done:

SELECT IIf((Nz(CountyPopulation, CityPopulation)
Between 1 and 20000, 1,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 20001 and 40000, 1.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 40001 and 100000, 2.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 100001 and 200000, 3,)
IIf(Nz(CountyPopulation, CityPopulation)

200001, 3.5)) As Result

FROM QrySales

Since the result goes into: qrySales!PopulationBracket, my question is where
do I most effectively place the code (an event proceedure after the City or
County decision has been made?) and how do I complete the snippet so that the
result ends up in the right place? Hope someone can help. Thanks.

 

[Guest]

I am a little confused here. You talk about putting the result in a text box
on a form, then you show a query and the address what appears to be a text
box on a query, which does not happen. I am guessing, based on your
information, that a query is not really needed for this task.

I would suggest you put this code in the before update event of the text box
in frmSales bound to the population bracket. You do not need the query here
for that data. Assuming population bracket in the query is bound to the
population bracket text box on the form, putting the code in the before
update event will put the data in place, and it will be updated when the
record is updated.

SELECT Case Nz(CountyPopulation, CityPopulation)
Case is 1 to 20000
Result = 1
Case is 20001 to 40000
Result = 1.5
Case is 40001 to 100000
Result = 2.5
Case is 100001 to 20000
Result = 3
Case Else
Result = 2.5
End Select
Me.txtPopulationBracket = Result

I apologize for my ignorance. but I'm doing this for the first time and need
the advice of someone in the know.

I'm creating the frmSales based on qrySales. My sales revenues are based on
either (not both) city or county populations. Hence, every sale created by
my form will be assigned to a population bracket to provide a base price
multiple once I've chosen either the [City] or [County] comboboxes on my
form. One of those two fields will ALWAYS be null. The [PopulationBracket]
field to ultimately be populated by my choice resides only on qrySales, not
on the form. Here's the code (I think it's correct, but if you think
otherwise I'd appreciate the correction) which gets part of the job done:

SELECT IIf((Nz(CountyPopulation, CityPopulation)
Between 1 and 20000, 1,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 20001 and 40000, 1.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 40001 and 100000, 2.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 100001 and 200000, 3,)
IIf(Nz(CountyPopulation, CityPopulation)

200001, 3.5)) As Result

FROM QrySales

Since the result goes into: qrySales!PopulationBracket, my question is where
do I most effectively place the code (an event proceedure after the City or
County decision has been made?) and how do I complete the snippet so that the
result ends up in the right place? Hope someone can help. Thanks.

 

[Guest]

Sorry if I was vague.

No text box [PopulationBracket] exists on my frmSales. The field only
exists in qrySales (on which frmSales is based) although the [BillingID]
combo box on which the code is based DOES appear on frmSales.
[PopulationBracket] is in qrySales, because the result will ultimately be
incorporated with some other data to calculate the [SalePrice] on frmSales,
but since it is only used for calculation purposes--to have the text box
[PopulationBracket] appear on the form would only be superfluous.

With that information, where do you recommend I put the code? Thanks so
much for your patience.
--
Tom

I am a little confused here. You talk about putting the result in a text box
on a form, then you show a query and the address what appears to be a text
box on a query, which does not happen. I am guessing, based on your
information, that a query is not really needed for this task.

I would suggest you put this code in the before update event of the text box
in frmSales bound to the population bracket. You do not need the query here
for that data. Assuming population bracket in the query is bound to the
population bracket text box on the form, putting the code in the before
update event will put the data in place, and it will be updated when the
record is updated.

SELECT Case Nz(CountyPopulation, CityPopulation)
Case is 1 to 20000
Result = 1
Case is 20001 to 40000
Result = 1.5
Case is 40001 to 100000
Result = 2.5
Case is 100001 to 20000
Result = 3
Case Else
Result = 2.5
End Select
Me.txtPopulationBracket = Result

I apologize for my ignorance. but I'm doing this for the first time and need
the advice of someone in the know.

I'm creating the frmSales based on qrySales. My sales revenues are based on
either (not both) city or county populations. Hence, every sale created by
my form will be assigned to a population bracket to provide a base price
multiple once I've chosen either the [City] or [County] comboboxes on my
form. One of those two fields will ALWAYS be null. The [PopulationBracket]
field to ultimately be populated by my choice resides only on qrySales, not
on the form. Here's the code (I think it's correct, but if you think
otherwise I'd appreciate the correction) which gets part of the job done:

SELECT IIf((Nz(CountyPopulation, CityPopulation)
Between 1 and 20000, 1,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 20001 and 40000, 1.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 40001 and 100000, 2.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 100001 and 200000, 3,)
IIf(Nz(CountyPopulation, CityPopulation)

200001, 3.5)) As Result

FROM QrySales

Since the result goes into: qrySales!PopulationBracket, my question is where
do I most effectively place the code (an event proceedure after the City or
County decision has been made?) and how do I complete the snippet so that the
result ends up in the right place? Hope someone can help. Thanks.

 

[Guest]

Yes, you did say that about the Population Bracket.... I should pay
attention. Well, although superfluous, it is by far the easiest way to do
it. Assuming you are using bound forms, then the record you are editing
updates when you move away from it. This all happens behind the scenes. To
do this seperately would be a lot of coding and would degrade performance. I
would recommend you put a text box on your form that is bound to the
Population Bracket field in your table and put the code is the before update
event of the text box. If you do not want anyone to be able to see the tex
tbox, set the Visible property for the text box to False.

Sorry if I was vague.

No text box [PopulationBracket] exists on my frmSales. The field only
exists in qrySales (on which frmSales is based) although the [BillingID]
combo box on which the code is based DOES appear on frmSales.
[PopulationBracket] is in qrySales, because the result will ultimately be
incorporated with some other data to calculate the [SalePrice] on frmSales,
but since it is only used for calculation purposes--to have the text box
[PopulationBracket] appear on the form would only be superfluous.

With that information, where do you recommend I put the code? Thanks so
much for your patience.
--
Tom

I am a little confused here. You talk about putting the result in a text box
on a form, then you show a query and the address what appears to be a text
box on a query, which does not happen. I am guessing, based on your
information, that a query is not really needed for this task.

I would suggest you put this code in the before update event of the text box
in frmSales bound to the population bracket. You do not need the query here
for that data. Assuming population bracket in the query is bound to the
population bracket text box on the form, putting the code in the before
update event will put the data in place, and it will be updated when the
record is updated.

SELECT Case Nz(CountyPopulation, CityPopulation)
Case is 1 to 20000
Result = 1
Case is 20001 to 40000
Result = 1.5
Case is 40001 to 100000
Result = 2.5
Case is 100001 to 20000
Result = 3
Case Else
Result = 2.5
End Select
Me.txtPopulationBracket = Result

I apologize for my ignorance. but I'm doing this for the first time and need
the advice of someone in the know.

I'm creating the frmSales based on qrySales. My sales revenues are based on
either (not both) city or county populations. Hence, every sale created by
my form will be assigned to a population bracket to provide a base price
multiple once I've chosen either the [City] or [County] comboboxes on my
form. One of those two fields will ALWAYS be null. The [PopulationBracket]
field to ultimately be populated by my choice resides only on qrySales, not
on the form. Here's the code (I think it's correct, but if you think
otherwise I'd appreciate the correction) which gets part of the job done:

SELECT IIf((Nz(CountyPopulation, CityPopulation)
Between 1 and 20000, 1,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 20001 and 40000, 1.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 40001 and 100000, 2.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 100001 and 200000, 3,)
IIf(Nz(CountyPopulation, CityPopulation)
200001, 3.5)) As Result
FROM QrySales

Since the result goes into: qrySales!PopulationBracket, my question is where
do I most effectively place the code (an event proceedure after the City or
County decision has been made?) and how do I complete the snippet so that the
result ends up in the right place? Hope someone can help. Thanks.

 

[Guest]

That's a great idea (Visible Property=False)! Thanks so much for the help.
--
Tom

Yes, you did say that about the Population Bracket.... I should pay
attention. Well, although superfluous, it is by far the easiest way to do
it. Assuming you are using bound forms, then the record you are editing
updates when you move away from it. This all happens behind the scenes. To
do this seperately would be a lot of coding and would degrade performance. I
would recommend you put a text box on your form that is bound to the
Population Bracket field in your table and put the code is the before update
event of the text box. If you do not want anyone to be able to see the tex
tbox, set the Visible property for the text box to False.

Sorry if I was vague.

No text box [PopulationBracket] exists on my frmSales. The field only
exists in qrySales (on which frmSales is based) although the [BillingID]
combo box on which the code is based DOES appear on frmSales.
[PopulationBracket] is in qrySales, because the result will ultimately be
incorporated with some other data to calculate the [SalePrice] on frmSales,
but since it is only used for calculation purposes--to have the text box
[PopulationBracket] appear on the form would only be superfluous.

With that information, where do you recommend I put the code? Thanks so
much for your patience.
--
Tom

I am a little confused here. You talk about putting the result in a text box
on a form, then you show a query and the address what appears to be a text
box on a query, which does not happen. I am guessing, based on your
information, that a query is not really needed for this task.

I would suggest you put this code in the before update event of the text box
in frmSales bound to the population bracket. You do not need the query here
for that data. Assuming population bracket in the query is bound to the
population bracket text box on the form, putting the code in the before
update event will put the data in place, and it will be updated when the
record is updated.

SELECT Case Nz(CountyPopulation, CityPopulation)
Case is 1 to 20000
Result = 1
Case is 20001 to 40000
Result = 1.5
Case is 40001 to 100000
Result = 2.5
Case is 100001 to 20000
Result = 3
Case Else
Result = 2.5
End Select
Me.txtPopulationBracket = Result
:

I apologize for my ignorance. but I'm doing this for the first time and need
the advice of someone in the know.

I'm creating the frmSales based on qrySales. My sales revenues are based on
either (not both) city or county populations. Hence, every sale created by
my form will be assigned to a population bracket to provide a base price
multiple once I've chosen either the [City] or [County] comboboxes on my
form. One of those two fields will ALWAYS be null. The [PopulationBracket]
field to ultimately be populated by my choice resides only on qrySales, not
on the form. Here's the code (I think it's correct, but if you think
otherwise I'd appreciate the correction) which gets part of the job done:

SELECT IIf((Nz(CountyPopulation, CityPopulation)
Between 1 and 20000, 1,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 20001 and 40000, 1.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 40001 and 100000, 2.5,)
IIf(Nz(CountyPopulation, CityPopulation)
Between 100001 and 200000, 3,)
IIf(Nz(CountyPopulation, CityPopulation)
200001, 3.5)) As Result
FROM QrySales

Since the result goes into: qrySales!PopulationBracket, my question is where
do I most effectively place the code (an event proceedure after the City or
County decision has been made?) and how do I complete the snippet so that the
result ends up in the right place? Hope someone can help. Thanks.