How to check a text string and then assign a different field a val

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Here is my problem: I have a field named [PictureSizeItem1] that uses a
combo box with the following text sting that clients must select: "40 X 40 =
Price 69.95". I want to use an If condition (or if you have a better
suggestion) to verify this string and, if true, assign the proper price value
to the following field [PriceItem1] (currency data field). Ten different
text strings have to be verified. How do I build the update query with the
builder?

Is the following correct?

IIf([PictureSizeItem1] = "40 X 40 = Price 65.95", [PriceItem1] = 65.95)
IIf([PictureSizeItem1] = "40 X 50 = Price 69.95", [PriceItem1] = 69.95)
and so on...
 
Dear BigD:

An approach I'd recommend first is to add a table containing the PictureItem
data.

PictureSize Price
40 X 40 65.95
40 X 50 69.95

You can build the string to display in the combo box from this.

To get the data into your query, you could add a "hidden" column to the
combo box and reference that in the query. That way, you would be able to
easily control the data on which the system operates from tables, which is
what Access is built to do.

A simple, but not so recommendable, solution would be to simply search for
the last space in the existing description and convert the rest of that
string to a numeric value. I guess that's where your price is "stored" now.

Please consider how easy it would be for the USERS to add or change prices
if they are in a table, rather than coded into a combo box. Also, for the
programmer, what if you need a similar combo box at two or three places in
your application? You would have to make any price changes in several
places, and be sure not to miss any! Same thing when adding a new Picture
Size.

Database programming works well if done correctly. One of the principles to
have this happen is to create a SINGLE point at which changes, especially
maintenance, can be performed, and to plan that against any possible future
need.

Tom Ellison
 
Hi,

Use a table, with two fields, description, and price. Fill it with the
possible descriptions, and prices. Then, use:


PriceItem1 = DLookup("price", "thatTableName",
"description=FORMS!FormName!ControlName" )



Your data is now in a table, so you can change it without modifying your
code! You can sell it to a broad range of customers, and you are not obliged
to make yourself their "modifications", or you are not oblige to force them
to change (and see) you code. Keeping data elsewhere than in the code is
nice, isn't it? And you already have a database at your disposal!


Hoping it may help,
Vanderghast, Access MVP
 
Hi Tom,

Thanks for your tip. I am a bit rusty and you have to forgive me. I
haven't touched Access in 6 years although I love all aspects of it.

Could you explain in better detail how I make the query, please? To hide
the field, I go into the "column" section in design view if I remember
correctly. Must I enter 3 fields in there but hide one? Then how would I
query it?

I am building a website through FrontPage 2002 and the asp file created the
database for me. Unfortunately, it doesn't create currency fields but only
numerical ones.

Thanks again Tom for your insight and assistance.

BigD

--
Thank you all for your assistance. It is appreciated.


Tom Ellison said:
Dear BigD:

An approach I'd recommend first is to add a table containing the PictureItem
data.

PictureSize Price
40 X 40 65.95
40 X 50 69.95

You can build the string to display in the combo box from this.

To get the data into your query, you could add a "hidden" column to the
combo box and reference that in the query. That way, you would be able to
easily control the data on which the system operates from tables, which is
what Access is built to do.

A simple, but not so recommendable, solution would be to simply search for
the last space in the existing description and convert the rest of that
string to a numeric value. I guess that's where your price is "stored" now.

Please consider how easy it would be for the USERS to add or change prices
if they are in a table, rather than coded into a combo box. Also, for the
programmer, what if you need a similar combo box at two or three places in
your application? You would have to make any price changes in several
places, and be sure not to miss any! Same thing when adding a new Picture
Size.

Database programming works well if done correctly. One of the principles to
have this happen is to create a SINGLE point at which changes, especially
maintenance, can be performed, and to plan that against any possible future
need.

Tom Ellison


BigD said:
Hi,

Here is my problem: I have a field named [PictureSizeItem1] that uses a
combo box with the following text sting that clients must select: "40 X 40 =
Price 69.95". I want to use an If condition (or if you have a better
suggestion) to verify this string and, if true, assign the proper price value
to the following field [PriceItem1] (currency data field). Ten different
text strings have to be verified. How do I build the update query with the
builder?

Is the following correct?

IIf([PictureSizeItem1] = "40 X 40 = Price 65.95", [PriceItem1] = 65.95)
IIf([PictureSizeItem1] = "40 X 50 = Price 69.95", [PriceItem1] = 69.95)
and so on...
Click to expand...
Click to expand...
 
Dear BigD:

A "hidden" column in a combo box or list box is one whose width is set to 0.
It's there but the user cannot see it. This column is included in the query
just as though it were displayed.

Tom Ellison


BigD said:
Hi Tom,

Thanks for your tip. I am a bit rusty and you have to forgive me. I
haven't touched Access in 6 years although I love all aspects of it.

Could you explain in better detail how I make the query, please? To hide
the field, I go into the "column" section in design view if I remember
correctly. Must I enter 3 fields in there but hide one? Then how would I
query it?

I am building a website through FrontPage 2002 and the asp file created the
database for me. Unfortunately, it doesn't create currency fields but only
numerical ones.

Thanks again Tom for your insight and assistance.

BigD

--
Thank you all for your assistance. It is appreciated.


Tom Ellison said:
Dear BigD:

An approach I'd recommend first is to add a table containing the PictureItem
data.

PictureSize Price
40 X 40 65.95
40 X 50 69.95

You can build the string to display in the combo box from this.

To get the data into your query, you could add a "hidden" column to the
combo box and reference that in the query. That way, you would be able to
easily control the data on which the system operates from tables, which is
what Access is built to do.

A simple, but not so recommendable, solution would be to simply search for
the last space in the existing description and convert the rest of that
string to a numeric value. I guess that's where your price is "stored" now.

Please consider how easy it would be for the USERS to add or change prices
if they are in a table, rather than coded into a combo box. Also, for the
programmer, what if you need a similar combo box at two or three places in
your application? You would have to make any price changes in several
places, and be sure not to miss any! Same thing when adding a new Picture
Size.

Database programming works well if done correctly. One of the principles to
have this happen is to create a SINGLE point at which changes, especially
maintenance, can be performed, and to plan that against any possible future
need.

Tom Ellison


BigD said:
Hi,

Here is my problem: I have a field named [PictureSizeItem1] that uses a
combo box with the following text sting that clients must select: "40
Click to expand...
X 40
=
Price 69.95". I want to use an If condition (or if you have a better
suggestion) to verify this string and, if true, assign the proper
Click to expand...
price
value
to the following field [PriceItem1] (currency data field). Ten different
text strings have to be verified. How do I build the update query
Click to expand...
with
the
builder?

Is the following correct?

IIf([PictureSizeItem1] = "40 X 40 = Price 65.95", [PriceItem1] = 65.95)
IIf([PictureSizeItem1] = "40 X 50 = Price 69.95", [PriceItem1] = 69.95)
and so on...
Click to expand...
Click to expand...
Click to expand...
 
Hi Tom,

Please forgive me for asking but I really need more input. I know how to
hide to hide the column but I am still puzzled as to how I will formulate the
query.

I have the following fields: [Picture_SizeItem1] [Price] and [Price_Item1]

On the order form, only the Picture size field will show. Customers are
informed of the prices on a previous web page. A confirmation page is
displayed for the order they placed then an invoice has to be generated from
the results table, or query in this case, to display the price for the size
they chose. [Picture_SizeItem1] and [Price_Item1] will display on the
invoice.

How do I formulate the query? What criteria do I use? Do I proceed like
this:

IIf([Picture_SizeItem1] = "40 X 40", [Price_Item1] = 65.95)

How is the hidden field play in this as it never displays initially in the
first place on the order form?

I am just a bit confused here. This is the price you pay when not having
the chance to practice what you've learned.

Thanks Tom for your understanding and assistance.

--
Thank you all for your assistance. It is appreciated.


Tom Ellison said:
Dear BigD:

A "hidden" column in a combo box or list box is one whose width is set to 0.
It's there but the user cannot see it. This column is included in the query
just as though it were displayed.

Tom Ellison


BigD said:
Hi Tom,

Thanks for your tip. I am a bit rusty and you have to forgive me. I
haven't touched Access in 6 years although I love all aspects of it.

Could you explain in better detail how I make the query, please? To hide
the field, I go into the "column" section in design view if I remember
correctly. Must I enter 3 fields in there but hide one? Then how would I
query it?

I am building a website through FrontPage 2002 and the asp file created the
database for me. Unfortunately, it doesn't create currency fields but only
numerical ones.

Thanks again Tom for your insight and assistance.

BigD

--
Thank you all for your assistance. It is appreciated.


Tom Ellison said:
Dear BigD:

An approach I'd recommend first is to add a table containing the PictureItem
data.

PictureSize Price
40 X 40 65.95
40 X 50 69.95

You can build the string to display in the combo box from this.

To get the data into your query, you could add a "hidden" column to the
combo box and reference that in the query. That way, you would be able to
easily control the data on which the system operates from tables, which is
what Access is built to do.

A simple, but not so recommendable, solution would be to simply search for
the last space in the existing description and convert the rest of that
string to a numeric value. I guess that's where your price is "stored" now.

Please consider how easy it would be for the USERS to add or change prices
if they are in a table, rather than coded into a combo box. Also, for the
programmer, what if you need a similar combo box at two or three places in
your application? You would have to make any price changes in several
places, and be sure not to miss any! Same thing when adding a new Picture
Size.

Database programming works well if done correctly. One of the principles to
have this happen is to create a SINGLE point at which changes, especially
maintenance, can be performed, and to plan that against any possible future
need.

Tom Ellison


Hi,

Here is my problem: I have a field named [PictureSizeItem1] that uses a
combo box with the following text sting that clients must select: "40 X 40
=
Price 69.95". I want to use an If condition (or if you have a better
suggestion) to verify this string and, if true, assign the proper price
value
to the following field [PriceItem1] (currency data field). Ten different
text strings have to be verified. How do I build the update query with
the
builder?

Is the following correct?

IIf([PictureSizeItem1] = "40 X 40 = Price 65.95", [PriceItem1] = 65.95)
IIf([PictureSizeItem1] = "40 X 50 = Price 69.95", [PriceItem1] = 69.95)
and so on...
Click to expand...
Click to expand...
Click to expand...
 
Dear D:

In a query, you can refer to a contorl on an OPEN form this way:

[forms]![form name]![control name]

The exception to this is if you have implemented "multiple instances of
forms" (which I really like to do, but it complicates things, and is rarely
done). You probably don't need to give this another thought, at least at
this time.

Using this syntax, you can refer to any column of the currently selected row
of the combo box. That is how you query can obtain the selected Price.

Tom Ellison

BigD said:
Hi Tom,

Please forgive me for asking but I really need more input. I know how to
hide to hide the column but I am still puzzled as to how I will formulate the
query.

I have the following fields: [Picture_SizeItem1] [Price] and [Price_Item1]

On the order form, only the Picture size field will show. Customers are
informed of the prices on a previous web page. A confirmation page is
displayed for the order they placed then an invoice has to be generated from
the results table, or query in this case, to display the price for the size
they chose. [Picture_SizeItem1] and [Price_Item1] will display on the
invoice.

How do I formulate the query? What criteria do I use? Do I proceed like
this:

IIf([Picture_SizeItem1] = "40 X 40", [Price_Item1] = 65.95)

How is the hidden field play in this as it never displays initially in the
first place on the order form?

I am just a bit confused here. This is the price you pay when not having
the chance to practice what you've learned.

Thanks Tom for your understanding and assistance.

--
Thank you all for your assistance. It is appreciated.


Tom Ellison said:
Dear BigD:

A "hidden" column in a combo box or list box is one whose width is set to 0.
It's there but the user cannot see it. This column is included in the query
just as though it were displayed.

Tom Ellison


BigD said:
Hi Tom,

Thanks for your tip. I am a bit rusty and you have to forgive me. I
haven't touched Access in 6 years although I love all aspects of it.

Could you explain in better detail how I make the query, please? To hide
the field, I go into the "column" section in design view if I remember
correctly. Must I enter 3 fields in there but hide one? Then how would I
query it?

I am building a website through FrontPage 2002 and the asp file
Click to expand...
created
the
database for me. Unfortunately, it doesn't create currency fields but only
numerical ones.

Thanks again Tom for your insight and assistance.

BigD

--
Thank you all for your assistance. It is appreciated.


:

Dear BigD:

An approach I'd recommend first is to add a table containing the PictureItem
data.

PictureSize Price
40 X 40 65.95
40 X 50 69.95

You can build the string to display in the combo box from this.

To get the data into your query, you could add a "hidden" column to the
combo box and reference that in the query. That way, you would be
Click to expand...
able
to
easily control the data on which the system operates from tables,
Click to expand...
which
is
what Access is built to do.

A simple, but not so recommendable, solution would be to simply
Click to expand...
search
for
the last space in the existing description and convert the rest of that
string to a numeric value. I guess that's where your price is
Click to expand...
"stored"
now.
Please consider how easy it would be for the USERS to add or change prices
if they are in a table, rather than coded into a combo box. Also,
Click to expand...
for
the
programmer, what if you need a similar combo box at two or three
Click to expand...
places
in
your application? You would have to make any price changes in several
places, and be sure not to miss any! Same thing when adding a new Picture
Size.

Database programming works well if done correctly. One of the principles to
have this happen is to create a SINGLE point at which changes, especially
maintenance, can be performed, and to plan that against any possible future
need.

Tom Ellison


Hi,

Here is my problem: I have a field named [PictureSizeItem1] that
Click to expand...
uses
a
combo box with the following text sting that clients must select:
Click to expand...
"40
X 40
=
Price 69.95". I want to use an If condition (or if you have a better
suggestion) to verify this string and, if true, assign the proper price
value
to the following field [PriceItem1] (currency data field). Ten different
text strings have to be verified. How do I build the update query with
the
builder?

Is the following correct?

IIf([PictureSizeItem1] = "40 X 40 = Price 65.95", [PriceItem1] = 65.95)
IIf([PictureSizeItem1] = "40 X 50 = Price 69.95", [PriceItem1] = 69.95)
and so on...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top