Access 97 -- generating numbers into forms

[Beth Kevles]

Hi --

I'm afraid I'm still using Access 97, but hope someone can help me.

I have a form (for a school auction database). In it I have a number
(currency) that I enter. From that number I'd like to generate:

-- a text field with the text version of that same number, that I can
overwrite with something different (such as "priceless") if I choose
-- another pair of currency numbers based on a formula (minimum bid at
20% of the number, minimum increment at 1/2 of minimum bid) both
overwrite-able if I choose.

And finally, I'd like to do this in some way such that when the database
is updated to more recent versions of Access, that it is likely to do so
smoothly.

I'd be happy to be directed to a web site or book with examples of how
to do these kinds of things.

Thanks for any advice you can offer! (And thanks to those of you who
told me how to get Access 97 working on my XP machine in the first
place. I *do* appreciate the help.)

--Beth Kevles
Access Advanced Beginner/Intermediate
School Fundraising Auction Database !Expert! (Can you believe it?)

 

[Tom Wickerath]

Hi Beth,

To convert a currency amount into English text, you can use either of the following two methods:

http://www.mvps.org/access/modules/mdl0001.htm

http://support.microsoft.com/?id=210586
Note: Although the Microsoft KB article indicates "Access 2000" in it's title, this code works
fine in Access 97.

The text field, minimum bid and minimum increment values will need to be stored in a table if you
wish to overwrite the calculated values and have your changes saved. However, be aware that doing
so violates both 2NF and 3NF of good database design. In general, you never want to store the
results of a calculation. If you do, make sure that the bid amount is only updated using this
form. If you update the value directly in the table, or a query, this code will not be run and
you can end up with inconsistent data.

I have a working sample in Access 97 format, which I just created. I can send you a copy by
private e-mail, if you don't mind receiving an attached .mdb file (it would be zipped).

Tom
____________________________________


Hi --

I'm afraid I'm still using Access 97, but hope someone can help me.

I have a form (for a school auction database). In it I have a number
(currency) that I enter. From that number I'd like to generate:

-- a text field with the text version of that same number, that I can
overwrite with something different (such as "priceless") if I choose
-- another pair of currency numbers based on a formula (minimum bid at
20% of the number, minimum increment at 1/2 of minimum bid) both
overwrite-able if I choose.

And finally, I'd like to do this in some way such that when the database
is updated to more recent versions of Access, that it is likely to do so
smoothly.

I'd be happy to be directed to a web site or book with examples of how
to do these kinds of things.

Thanks for any advice you can offer! (And thanks to those of you who
told me how to get Access 97 working on my XP machine in the first
place. I *do* appreciate the help.)

--Beth Kevles
Access Advanced Beginner/Intermediate
School Fundraising Auction Database !Expert! (Can you believe it?)

 

[Barry-Jon]

As to a book: I thoroughly recommend "Access 97 Developer's Handbook"
published by Sybex, authors; Paul Litwin, Ken Getz, Mike Gilbert (ISBN
0782119417). You should be able to pick one up second hand for a good
price. You say you are and advanced beginner - well I read it at that
point and it brought me on leaps and bounds (I know program in Access
full time for a bank). I would actually suggest reading the whole book
if you are serious about progressing your knowledge.

 

[Beth Kevles]

Tom and Barry --

Thank you both for your very helpful responses. I have a follow-up
question which is, perhaps, more about the *best* way to do things than
just a way that works.

The database I'm working on, as I mentioned, is for a school fundraising
auction. The three relevant fields, all of which are saved in a table,
are:

- Sort Value (currency) which is the actual dollar value I assign to an
auction item.
- Value (text) which is usually a currency amount, but could be
something
like "Priceless" or "$100 - $300".
- Minimum Bid (currency) which will *usually( be about 20% of the Sort
Value. Sometimes, though, the Sort Value is out of line with what I
think bidders will be willing to spend, so the Minimum Bid may be much
smaller (or larger) than the 20% suggested.

I'd like to have default values for the "Value" and "Minimum Bid" fields
filled in automatically when I first enter "Sort Value" into the table,
which I always do through the same form. But I'd like to be able to
change those values if I think I should.

If filling in the values for "Value" and "Minimum Bid" shouldn't be done
as I originally envisioned through the form, what then is a better,
cleaner way to accomplish my goal? Basically, I'm tired of typing in
those related values and then having to correct my typos.

Thanks for your advice,
--Beth Kevles, Original Poster

 

[Tom Wickerath]

Very good book. I picked up a copy of this book a few years ago at a Half Price Books store for
only $15.

Tom
______________________________


As to a book: I thoroughly recommend "Access 97 Developer's Handbook"
published by Sybex, authors; Paul Litwin, Ken Getz, Mike Gilbert (ISBN
0782119417). You should be able to pick one up second hand for a good
price. You say you are and advanced beginner - well I read it at that
point and it brought me on leaps and bounds (I know program in Access
full time for a bank). I would actually suggest reading the whole book
if you are serious about progressing your knowledge.

 

[Tom Wickerath]

Hi Beth,

I'd like to have default values for the "Value" and "Minimum Bid"
fields filled in automatically when I first enter "Sort Value" into the
table, which I always do through the same form. But I'd like to be
able to change those values if I think I should.

I believe the sample you requested by private e-mail, which I have sent to you, does just this.
However, I used different field names. Here are some links to articles that are helpful for
picking good names:

Naming Conventions

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763



Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp



Using a Naming Convention

http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp


Note that the first entry shown in KB 826763 is a space. Thus, names like "Minimum Bid" should be
changed to "MinimumBid". Also, the name "Value" is a reserved word. You should avoid using any
reserved words for the names of fields, controls, or objects (tables, queries, forms, reports,
etc.).

Reserved Words

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335



List of reserved words in Jet 4.0

http://support.microsoft.com/?id=321266

If filling in the values for "Value" and "Minimum Bid" shouldn't

be done as I originally envisioned through the form, what then

is a better, cleaner way to accomplish my goal?

There is no alternative if you want the ability to change these values and save your changes.





Tom
_________________________________


Tom and Barry --

Thank you both for your very helpful responses. I have a follow-up
question which is, perhaps, more about the *best* way to do things than
just a way that works.

The database I'm working on, as I mentioned, is for a school fundraising
auction. The three relevant fields, all of which are saved in a table,
are:

- Sort Value (currency) which is the actual dollar value I assign to an
auction item.
- Value (text) which is usually a currency amount, but could be
something
like "Priceless" or "$100 - $300".
- Minimum Bid (currency) which will *usually( be about 20% of the Sort
Value. Sometimes, though, the Sort Value is out of line with what I
think bidders will be willing to spend, so the Minimum Bid may be much
smaller (or larger) than the 20% suggested.

I'd like to have default values for the "Value" and "Minimum Bid" fields
filled in automatically when I first enter "Sort Value" into the table,
which I always do through the same form. But I'd like to be able to
change those values if I think I should.

If filling in the values for "Value" and "Minimum Bid" shouldn't be done
as I originally envisioned through the form, what then is a better,
cleaner way to accomplish my goal? Basically, I'm tired of typing in
those related values and then having to correct my typos.

Thanks for your advice,
--Beth Kevles, Original Poster

 

[Barry-Jon]

Beth,

I am finding it hard to understand where the two values come from. I
understand that your minimum bid should generally, but not always, be
about 20% of the dollar value and that it quite easily done. Where
does the "SortValue" & "TextValue" come from? Is one an appraised
value and one a purchase cost? What are the two values used for other
than calculating the minimum bid? Databases design is an end to end
process; tables, fields, relationships, and queries should all be built
to model a real world process/need. If the two value fields have
seperate sources and are unrelated to each other than there is no
problem with having the seperate field. I'm sorry I can't give clearer
advice but without understanding where the second value comes from and
what it is to be used for I am unable to.

Barry-Jon

PS. The book I recommended has an excellent section on database design
and naming conventions.

 

[Beth Kevles]

Barry-Jon --

I think Tom may have answered my questions. I'll know for sure when I
have a chance to go over his code tomorrow night. But to respond to
yours ...

"Value" is a text field. It usually displays an amount, a currency, but
sometimes has to display a range ($100-$300) or the word "Priceless".
(A discount certificate would have a range; a donation of an evening out
with a favorite teacher would be priceless. A candlestick, on the other
hand, would be a simple currency value based on appraisal or purchase
price.)

"SortValue" is a currency field. For the candlestick example it would
be the same as "Value". For the other examples it would be my best
guess as to what the item will actually sell for. "DollarValue" might
be a better name, but it was originally used to sort the donations in
the database.

"MinimumBid" is simply where I want the bidding to start at the actual
auction. Usually if I start the bidding at 20% of actual value then the
bidding is pretty lively. But there are exceptions. The truly ugly
dress with actual gold thread, in a size 4, comes to mind. 20% was WAY
too high an opening bid.

I'm going to pick up a copy of the book you recommended this week. I've
clearly outgrown the Access book I've been using so far.

Thanks again, from myself and my elementary school
--Beth Kevles

 

[dongpeng]

Hi Beth,


I believe the sample you requested by private e-mail, which I have sent to you, does just this.
However, I used different field names. Here are some links to articles that are helpful for
picking good names:

Naming Conventions

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763



Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp



Using a Naming Convention

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html
/deconusingnamingconvention.asp


Note that the first entry shown in KB 826763 is a space. Thus, names like "Minimum Bid" should be
changed to "MinimumBid". Also, the name "Value" is a reserved word. You should avoid using any
reserved words for the names of fields, controls, or objects (tables, queries, forms, reports,
etc.).

Reserved Words

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335



List of reserved words in Jet 4.0

http://support.microsoft.com/?id=321266










There is no alternative if you want the ability to change these values and save your changes.





Tom
_________________________________


Tom and Barry --

Thank you both for your very helpful responses. I have a follow-up
question which is, perhaps, more about the *best* way to do things than
just a way that works.

The database I'm working on, as I mentioned, is for a school fundraising
auction. The three relevant fields, all of which are saved in a table,
are:

- Sort Value (currency) which is the actual dollar value I assign to an
auction item.
- Value (text) which is usually a currency amount, but could be
something
like "Priceless" or "$100 - $300".
- Minimum Bid (currency) which will *usually( be about 20% of the Sort
Value. Sometimes, though, the Sort Value is out of line with what I
think bidders will be willing to spend, so the Minimum Bid may be much
smaller (or larger) than the 20% suggested.

I'd like to have default values for the "Value" and "Minimum Bid" fields
filled in automatically when I first enter "Sort Value" into the table,
which I always do through the same form. But I'd like to be able to
change those values if I think I should.

If filling in the values for "Value" and "Minimum Bid" shouldn't be done
as I originally envisioned through the form, what then is a better,
cleaner way to accomplish my goal? Basically, I'm tired of typing in
those related values and then having to correct my typos.

Thanks for your advice,
--Beth Kevles, Original Poster