Dear TenorPlayer- (name?)
Welcome to the group?
Why do you want to go to all this trouble? It would be much easier to let
Access assign an AutoNumber key to each record. You can still collect garment
type, size, etc. to enable you to perform searches or print a report that lists
the coats first, then the pants, etc.
If you really want to continue on this route, you'll have to use the DMax
function to find the previous higher key value. If you will have other
characters following the number, that could prove dicey to do. You would also
need to decide in advance the max number you'll ever assign to a particular item
because to enable the DMax search, each number will have to be left-padded with
zeros. Let's say you never plan to have more than 999 of any one type of item.
The numbers for coats would then look like:
C001, C002, C003, ... , C010, C011, C012, ... , C100, C101, etc.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of tenorplayer2008
Sent: Tuesday, August 21, 2012 6:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Max number within a string w/ qualifier
Greetings! I'm new to access, so please bear with me.
I'm building a database to keep track of garment inventory/assignments for my
choir program at school. I want to use a table with the key being a generated
string with the garment type and number embedded (ie Pants number 24 would have
key P24, while in the same table Coat 7 would be C7). I'm trying to build the
form to populate the garment table. The first thing the form asks for is for the
garment type (combo box with all the appropriate options). As soon as that's
updated, I need to look at the table and see what the highest number is for that
garment type and assign this new garment 1 higher (ex. I tell it I'm adding a
pair of pants. The AfterUpdate sub looks at the table and sees the highest key
with a lead character of "P" is 16, so it assigns this garment I'm adding key
P17). After that, I know how to get the measurements imported for the new
garment. Any ideas? Thanks for your help!
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar