forms question

  • Thread starter Thread starter Jack Wilson
  • Start date Start date
J

Jack Wilson

Greetings all,

I am really new to access (was given a project to "figure
out" ) and I am trying to develop a form for a table that
is going to intergrate with another database. I have a
field called IDnumber which is autogenerated. I have a
field called Cardnumber, which is supposed to be the
IDnumber+01 if it is their first card and +02 if it is
their second card. For example, if the persons IDnumber is
2345 their card number if it is their 1st card if 234501.
If it is their 2nd card (they lost one) it is 234502. My
question is, I am trying to do this using an expression
and failing miserably. Would any gurus on here be able to
point me in the right direction. As far as knowing what
card they have I guess I would need to put a field (01) so
it would know to add a 01, 02 if it needed to add a 02,
etc.Then after all is said and done this database will be
exported into some format and imported into another
application.
I told my boss I am NOT a access programmer :)

Thanks in advance to anyone that can help. You may send
replies direct or on here.

Thanks jack
 
Jack,

First things first. Do not, I repeat, do not, append a number to the end of
the IDNumber to make the card number. This is really poor database design.
Instead, create a new field, call it CardSeq or CardNum, whatever you want,
and store a numeric value in that field. This way, when you need to issue a
new card, you can easily determine the previous CardSeq using the DMAX
function. If you later need to concatenate these two fields together to
integrate it with your other database, you can do that then.

NewCardSeq = NZ(DMAX("CardSeq", "yourTableName", "IDNumber = 2345"), 0) + 1

Since you are new to Access, I'll explain these functions briefly. The DMAX
function takes three parameters (FieldName, TableName, and Criteria). It
returns the maximum value in the field you indicate, of the table you
indicate, based on the criteria you pass it. If no record meets the
criteria you pass it, It returns a Null value.

The NZ() function takes one required parameter and a second [optional] field
which has a default value of zero. If the value passed to the function is
Null, the return value is whatever you supplied as the optional parameter,
or zero.

HTH
If you need more info, post back

Dale
 
Dale,

Thanks for the quick response. Could you e-mail me
directly please? I have a couple of other questions.
Thanks Jack
-----Original Message-----
Jack,

First things first. Do not, I repeat, do not, append a number to the end of
the IDNumber to make the card number. This is really poor database design.
Instead, create a new field, call it CardSeq or CardNum, whatever you want,
and store a numeric value in that field. This way, when you need to issue a
new card, you can easily determine the previous CardSeq using the DMAX
function. If you later need to concatenate these two fields together to
integrate it with your other database, you can do that then.

NewCardSeq = NZ(DMAX
Click to expand...
("CardSeq", "yourTableName", "IDNumber = 2345"), 0) + 1
Since you are new to Access, I'll explain these functions briefly. The DMAX
function takes three parameters (FieldName, TableName, and Criteria). It
returns the maximum value in the field you indicate, of the table you
indicate, based on the criteria you pass it. If no record meets the
criteria you pass it, It returns a Null value.

The NZ() function takes one required parameter and a second [optional] field
which has a default value of zero. If the value passed to the function is
Null, the return value is whatever you supplied as the optional parameter,
or zero.

HTH
If you need more info, post back

Dale


Jack Wilson said:
Greetings all,

I am really new to access (was given a project to "figure
out" ) and I am trying to develop a form for a table that
is going to intergrate with another database. I have a
field called IDnumber which is autogenerated. I have a
field called Cardnumber, which is supposed to be the
IDnumber+01 if it is their first card and +02 if it is
their second card. For example, if the persons IDnumber is
2345 their card number if it is their 1st card if 234501.
If it is their 2nd card (they lost one) it is 234502. My
question is, I am trying to do this using an expression
and failing miserably. Would any gurus on here be able to
point me in the right direction. As far as knowing what
card they have I guess I would need to put a field (01) so
it would know to add a 01, 02 if it needed to add a 02,
etc.Then after all is said and done this database will be
exported into some format and imported into another
application.
I told my boss I am NOT a access programmer :)

Thanks in advance to anyone that can help. You may send
replies direct or on here.

Thanks jack
Click to expand...


.
Click to expand...
 
Jack,

I prefer to use the newsgroup, as it allows others to share in the knowledge
learning process.

jack Wilson said:
Dale,

Thanks for the quick response. Could you e-mail me
directly please? I have a couple of other questions.
Thanks Jack
-----Original Message-----
Jack,

First things first. Do not, I repeat, do not, append a number to the end of
the IDNumber to make the card number. This is really poor database design.
Instead, create a new field, call it CardSeq or CardNum, whatever you want,
and store a numeric value in that field. This way, when you need to issue a
new card, you can easily determine the previous CardSeq using the DMAX
function. If you later need to concatenate these two fields together to
integrate it with your other database, you can do that then.

NewCardSeq = NZ(DMAX
Click to expand...
("CardSeq", "yourTableName", "IDNumber = 2345"), 0) + 1
Since you are new to Access, I'll explain these functions briefly. The DMAX
function takes three parameters (FieldName, TableName, and Criteria). It
returns the maximum value in the field you indicate, of the table you
indicate, based on the criteria you pass it. If no record meets the
criteria you pass it, It returns a Null value.

The NZ() function takes one required parameter and a second [optional] field
which has a default value of zero. If the value passed to the function is
Null, the return value is whatever you supplied as the optional parameter,
or zero.

HTH
If you need more info, post back

Dale


Jack Wilson said:
Greetings all,

I am really new to access (was given a project to "figure
out" ) and I am trying to develop a form for a table that
is going to intergrate with another database. I have a
field called IDnumber which is autogenerated. I have a
field called Cardnumber, which is supposed to be the
IDnumber+01 if it is their first card and +02 if it is
their second card. For example, if the persons IDnumber is
2345 their card number if it is their 1st card if 234501.
If it is their 2nd card (they lost one) it is 234502. My
question is, I am trying to do this using an expression
and failing miserably. Would any gurus on here be able to
point me in the right direction. As far as knowing what
card they have I guess I would need to put a field (01) so
it would know to add a 01, 02 if it needed to add a 02,
etc.Then after all is said and done this database will be
exported into some format and imported into another
application.
I told my boss I am NOT a access programmer :)

Thanks in advance to anyone that can help. You may send
replies direct or on here.

Thanks jack
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top