M
Mick
is it possible to configure a number field to take a normal credit card number
5544667788998877 i.e. a 16 digit integer?
5544667788998877 i.e. a 16 digit integer?
A
a a r o n . k e m p f
SQL Server can!
It's called a BigInt.
I'm of the understanding that Integers only go up to 2.1 billion..
thus 9 characters.
So.. if you take _TWO_ integers; you should be able to kludge this.
This is the kludge that we had to use back at MSN.. to get around this
limit in Access.
And then we upsized to SQL Server and it worked like a charm!
Talk about having _GREAT_ data quality-- right out of the box.
-Aaron
It's called a BigInt.
I'm of the understanding that Integers only go up to 2.1 billion..
thus 9 characters.
So.. if you take _TWO_ integers; you should be able to kludge this.
This is the kludge that we had to use back at MSN.. to get around this
limit in Access.
And then we upsized to SQL Server and it worked like a charm!
Talk about having _GREAT_ data quality-- right out of the box.
-Aaron
A
a a r o n . k e m p f
I'm sorry.
if the credit card number.. and you're not gonig to do math on it..
then it should be _MATH_?
I just don't agree with your declaration.
If your database can't fit a real number- then get a real database.
It's a credit card _NUMBER_. What if someone tries to put TEXT in the
field? Is it necessary to enforce business rules like this in multiple
places? I think that it is considered a best practice-- to use the
best datatype for the job.
The best datatype (BIGINT) is extremely fast compared to text.
and again-- it increases / ensures the _QUALITY_ of the data.
If your midget-sized database can't fit a (PRACTICAL) number then
upsize upsize, upsize!
-Aaron
if the credit card number.. and you're not gonig to do math on it..
then it should be _MATH_?
I just don't agree with your declaration.
If your database can't fit a real number- then get a real database.
It's a credit card _NUMBER_. What if someone tries to put TEXT in the
field? Is it necessary to enforce business rules like this in multiple
places? I think that it is considered a best practice-- to use the
best datatype for the job.
The best datatype (BIGINT) is extremely fast compared to text.
and again-- it increases / ensures the _QUALITY_ of the data.
If your midget-sized database can't fit a (PRACTICAL) number then
upsize upsize, upsize!
-Aaron
K
Ken Sheridan
A credit card number is really an encoding system. The first 6 digits
signify the issuer, and the last is a check digit. The intervening digits
form the account number. A valid credit card number satisfies the Luhn
algorithm which applies a number of doubling and replacement operations to
certain digits in the number. If the sum of the resulting digits is a
multiple of 10 then the original number is a valid one. It would not be
difficult to use the algorithm in a control's BeforeUpdate event procedure to
verify a number entered in a form as a valid set of digits for a credit card
number. See the following for details of the 'Luhn check'
http://www.thetaoofmakingmoney.com/2007/04/12/324.html
The encoding is even more finely grained, the first digit being the major
Industry identifier (MII) denoting the type of body issuing the card (4 or 5
indicates a banking or financial institution for instance). As the MII can
be a zero (ISO/TC 68 and other industry assignments) the use of a number data
type would not in fact store such numbers; only the number formatted with a
leading zero would represent the 'number'.
All of this leads to the conclusion that, as Fred has said, its best to use
a column of text data type as the 'number' is really more of a string made up
of three substrings, the Issuer Identifier, the Account Number, and the Check
Digit.
Incidentally not all credit card numbers are 16 digits long as some issuers
use account numbers less than 9 digits long. Nevertheless they pass the Luhn
check.
Other examples of numbers which are really encoding systems are Zip codes
and ISBN numbers.
Ken Sheridan
Stafford, England
signify the issuer, and the last is a check digit. The intervening digits
form the account number. A valid credit card number satisfies the Luhn
algorithm which applies a number of doubling and replacement operations to
certain digits in the number. If the sum of the resulting digits is a
multiple of 10 then the original number is a valid one. It would not be
difficult to use the algorithm in a control's BeforeUpdate event procedure to
verify a number entered in a form as a valid set of digits for a credit card
number. See the following for details of the 'Luhn check'
http://www.thetaoofmakingmoney.com/2007/04/12/324.html
The encoding is even more finely grained, the first digit being the major
Industry identifier (MII) denoting the type of body issuing the card (4 or 5
indicates a banking or financial institution for instance). As the MII can
be a zero (ISO/TC 68 and other industry assignments) the use of a number data
type would not in fact store such numbers; only the number formatted with a
leading zero would represent the 'number'.
All of this leads to the conclusion that, as Fred has said, its best to use
a column of text data type as the 'number' is really more of a string made up
of three substrings, the Issuer Identifier, the Account Number, and the Check
Digit.
Incidentally not all credit card numbers are 16 digits long as some issuers
use account numbers less than 9 digits long. Nevertheless they pass the Luhn
check.
Other examples of numbers which are really encoding systems are Zip codes
and ISBN numbers.
Ken Sheridan
Stafford, England
A
a a r o n . k e m p f
Dude-- just because your database _WILL_NOT_FIT_SUCH_A_BIG_NUMBER_
that doesnt' mean that you should need to _SETTLE_ for a text field.
Settling for a text field-- means that someone can
With SQL Server; you can have a _RULE_ so that you can enforce a
particular PATTERN against a datatype like this.
Yes-- you could enforce an algorithm-- just like this-- in SQL Server;
and this would work _EVER_WHEN_THEY_HAD_MACROS_DISABLED_.
You say 'oh why would someone disable macros'--
You _ARE_ talking about Money right??
But when you get like-- 20 of these little 'gotcha' when Access/ JET
just can't compete with SQL Server-- then start adding it up.
The difference between 16 (or should I say 17) bytes and 8 bytes is
not trivial.
If the datatype is 'twice as big' then 'indexes are slower'.
If the datatype is 'twice as big' then 'getting to the data will be
twice as slow'.
If the datatype is 'twice as big' then 'writing the data will be twice
as slow'.
If the datatype is CANNOT PROTECT DATA QUALITY then you'll be stuck
with bad data
Re: Zipcodes-- if American only-- should _DEFINITELY_ be numeric.
Nobody really uses those +5 zipcodes; if they do-- put them in a
different field.
It has to do with performance.
It has to do with SIZE.
It has to do with DATA QUALITY.
If JET doesn't meet your needs then start using one of the 'more
progressive' database engines that are available to MS Access.
-Aaron
that doesnt' mean that you should need to _SETTLE_ for a text field.
Settling for a text field-- means that someone can
With SQL Server; you can have a _RULE_ so that you can enforce a
particular PATTERN against a datatype like this.
Yes-- you could enforce an algorithm-- just like this-- in SQL Server;
and this would work _EVER_WHEN_THEY_HAD_MACROS_DISABLED_.
You say 'oh why would someone disable macros'--
You _ARE_ talking about Money right??
But when you get like-- 20 of these little 'gotcha' when Access/ JET
just can't compete with SQL Server-- then start adding it up.
The difference between 16 (or should I say 17) bytes and 8 bytes is
not trivial.
If the datatype is 'twice as big' then 'indexes are slower'.
If the datatype is 'twice as big' then 'getting to the data will be
twice as slow'.
If the datatype is 'twice as big' then 'writing the data will be twice
as slow'.
If the datatype is CANNOT PROTECT DATA QUALITY then you'll be stuck
with bad data
Re: Zipcodes-- if American only-- should _DEFINITELY_ be numeric.
Nobody really uses those +5 zipcodes; if they do-- put them in a
different field.
It has to do with performance.
It has to do with SIZE.
It has to do with DATA QUALITY.
If JET doesn't meet your needs then start using one of the 'more
progressive' database engines that are available to MS Access.
-Aaron
B
BruceM
Interesting stuff. Thanks for providing the information. Even though its
original target missed the point, I'm sure it found a receptive audience.
original target missed the point, I'm sure it found a receptive audience.
A
a a r o n . k e m p f
Your database can't enforce the correct datatype.
Sorry.
I don't want to hear any more excuses.
It should be a number-- it is a number.
Sorry that it doesn't fit in Access (with the appropriate datatype).
-Aaron
Sorry.
I don't want to hear any more excuses.
It should be a number-- it is a number.
Sorry that it doesn't fit in Access (with the appropriate datatype).
-Aaron
B
BruceM
Shoo
message
Your database can't enforce the correct datatype.
Sorry.
I don't want to hear any more excuses.
It should be a number-- it is a number.
Sorry that it doesn't fit in Access (with the appropriate datatype).
-Aaron
message
Your database can't enforce the correct datatype.
Sorry.
I don't want to hear any more excuses.
It should be a number-- it is a number.
Sorry that it doesn't fit in Access (with the appropriate datatype).
-Aaron
M
Mick
To someone who some of the answers are totally lost on, and as it seems that
access as it is currently configured will not take the 16 digit numbers of a
credit card unless its a text field, thanks to all I think for simplicity I
will move on using the answers provided so far thanks
:
, > Shoo
access as it is currently configured will not take the 16 digit numbers of a
credit card unless its a text field, thanks to all I think for simplicity I
will move on using the answers provided so far thanks
:
, > Shoo
A
a a r o n . k e m p f
you're the kid in the wrong newsgroup.
Maybe they have a
MIcrosoft.Public.Access.StuckInThe90s
Until then-- this is my newsgroup, kid.
SQL Server is the default, optimal engine for MS Access.
-Aaron
Maybe they have a
MIcrosoft.Public.Access.StuckInThe90s
Until then-- this is my newsgroup, kid.
SQL Server is the default, optimal engine for MS Access.
-Aaron
A
Arvin Meyer [MVP]
You are absolutely wrong. Values which use numerals which are never going to
be used for math, such as Social Security Numbers, phone numbers, addresses,
credit card numbers, and any other numeral values used as identifiers,
should always be stored as text. Very often there isn't even a choice as
storage is required to use formatted text. In the case of credit card
number this is specifically the case because a space or dash is often
included, and the checksum algorithms use string functions.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
message
be used for math, such as Social Security Numbers, phone numbers, addresses,
credit card numbers, and any other numeral values used as identifiers,
should always be stored as text. Very often there isn't even a choice as
storage is required to use formatted text. In the case of credit card
number this is specifically the case because a space or dash is often
included, and the checksum algorithms use string functions.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
message
A
a a r o n . k e m p f
WRONG
numbers that are 'always numbers' should 'always be stored as
numbers.. even if your midget-sized database doesn't fit them very
nicely'.
It's called 'Data Quality' - something about which you Access kids
know nothing about.
http://en.wikipedia.org/wiki/Sour_grapes
Sour grapes is the _FALSE_DENIAL_ of desire for something sought but
not acquired; to denigrate and feign disdain for that which one could
not attain. This metaphor originated from the fable The Fox and the
Grapes by Aesop, where the protagonist fox fails to reach some grapes
hanging high up on a vine, retreats, and rationalizes that the grapes
are probably unripe anyway. The grapes in the original fable are more
accurately described as unripe, and the word "unripe" may have been
replaced with "sour" by the fable's Victorian translators due to the
latter's simpler sound, and because of the former's connotation as an
innuendo (ie. an unripe woman). The phenomenon has been seen as a
challenge to the rational-actor view within the social sciences, with
its significance debated by scholars such as Jon Elster and Steven
Lukes.
The phrase is sometimes also used to refer to one expressing, in an
unsportsmanlike or ungracious way, anger or frustration at having
failed to acquire something (i.e. being a "sore loser"), regardless of
whether the party denies their desire for the item. Not including the
denial of desire is technically a slipshod extension of the metaphor
because it is inconsistent with the phrase's origin in the fable and
the notion of the grapes being declared "sour". [1]
[edit] References
numbers that are 'always numbers' should 'always be stored as
numbers.. even if your midget-sized database doesn't fit them very
nicely'.
It's called 'Data Quality' - something about which you Access kids
know nothing about.
http://en.wikipedia.org/wiki/Sour_grapes
Sour grapes is the _FALSE_DENIAL_ of desire for something sought but
not acquired; to denigrate and feign disdain for that which one could
not attain. This metaphor originated from the fable The Fox and the
Grapes by Aesop, where the protagonist fox fails to reach some grapes
hanging high up on a vine, retreats, and rationalizes that the grapes
are probably unripe anyway. The grapes in the original fable are more
accurately described as unripe, and the word "unripe" may have been
replaced with "sour" by the fable's Victorian translators due to the
latter's simpler sound, and because of the former's connotation as an
innuendo (ie. an unripe woman). The phenomenon has been seen as a
challenge to the rational-actor view within the social sciences, with
its significance debated by scholars such as Jon Elster and Steven
Lukes.
The phrase is sometimes also used to refer to one expressing, in an
unsportsmanlike or ungracious way, anger or frustration at having
failed to acquire something (i.e. being a "sore loser"), regardless of
whether the party denies their desire for the item. Not including the
denial of desire is technically a slipshod extension of the metaphor
because it is inconsistent with the phrase's origin in the fable and
the notion of the grapes being declared "sour". [1]
[edit] References
A
a a r o n . k e m p f
it's called a format mask.. if you must 
I Just find it humorous that you say that this shouldn't be stored as
a number.
How are you going to ensure that your developers write it in a numeric
format.. I mean how does that work again?
-Aaron
I Just find it humorous that you say that this shouldn't be stored as
a number.
How are you going to ensure that your developers write it in a numeric
format.. I mean how does that work again?
-Aaron
A
a a r o n . k e m p f
dude where do you make this shit up?
A number-- is just like text-- in that it is smaller (less characters
in the char set).
Next thing that you'll tell me is that you use a varchar field for
SSN!!!!
Do you have -ANY- idea the performance difference between an INT and a
Varchar(12)?
Just because your DB doesnt fit a real world number.. that doesn't
make it a best practice to store it as text.
How are you going to make sure that they don't use a bad character?
WHAT IF THEY DISABLE MACROS?
What if they put a SQL Statement _IN_ the text field?
Using numeric fields - everywhere - is one of the best ways to get
away from SQL injections.
-Aaron
A number-- is just like text-- in that it is smaller (less characters
in the char set).
Next thing that you'll tell me is that you use a varchar field for
SSN!!!!
Do you have -ANY- idea the performance difference between an INT and a
Varchar(12)?
Just because your DB doesnt fit a real world number.. that doesn't
make it a best practice to store it as text.
How are you going to make sure that they don't use a bad character?
WHAT IF THEY DISABLE MACROS?
What if they put a SQL Statement _IN_ the text field?
Using numeric fields - everywhere - is one of the best ways to get
away from SQL injections.
-Aaron
A
a a r o n . k e m p f
dude you're so full of crap dog
They didn't bother 'making excuses for Access' because it's a piece of
crap database
-Aaron
They didn't bother 'making excuses for Access' because it's a piece of
crap database
-Aaron
A
a a r o n . k e m p f
re:
nope a number is something that it makes sense to add, subtract,
multiply or divide.
uh.. why is that most keys use integers?
WHY IS IT?
IS IT BECAUSE OF PERFORMANCE?
Why would you waste 10 bytes per record just because you want to store
a whole bunch of hyphens?
seriously-- don't store the formatting in the database.. if you know
anything about normalization that is.
-Aaron
nope a number is something that it makes sense to add, subtract,
multiply or divide.
uh.. why is that most keys use integers?
WHY IS IT?
IS IT BECAUSE OF PERFORMANCE?
Why would you waste 10 bytes per record just because you want to store
a whole bunch of hyphens?
seriously-- don't store the formatting in the database.. if you know
anything about normalization that is.
-Aaron
A
a a r o n . k e m p f
re:
yep. a very good idea. Do you have a clue to the fact that it takes
longer to convert an integer to a string so it can be displayed than
to write the same string directly?
ROFLMAO
I'm not talking about converting shit, dog.
Seriously.. where would I need to use a .ToString? In ASP.net? ROFL
How is it _NOT_ that storing it as a number as INT-- is 1/3 of the
storage space of a varchar(11)?
Do you not give a crap about wasting 2/3rds of your storage space?
Stop making excuses dude.. Just because your database won't fit it--
doesn't mean that you should stoop to using a looser datatype than the
optimal datatype.
And seriously.. SQL INJECTION-- if you just let numbers in the fields;
then you lower the chance of injection.
And seriously.. SQL INJECTION-- if you just let numbers in the fields;
then you lower the chance of injection.
-Aaron
yep. a very good idea. Do you have a clue to the fact that it takes
longer to convert an integer to a string so it can be displayed than
to write the same string directly?
ROFLMAO
I'm not talking about converting shit, dog.
Seriously.. where would I need to use a .ToString? In ASP.net? ROFL
How is it _NOT_ that storing it as a number as INT-- is 1/3 of the
storage space of a varchar(11)?
Do you not give a crap about wasting 2/3rds of your storage space?
Stop making excuses dude.. Just because your database won't fit it--
doesn't mean that you should stoop to using a looser datatype than the
optimal datatype.
And seriously.. SQL INJECTION-- if you just let numbers in the fields;
then you lower the chance of injection.
And seriously.. SQL INJECTION-- if you just let numbers in the fields;
then you lower the chance of injection.
-Aaron
A
a a r o n . k e m p f
How is it _NOT_ that storing it as a number as INT-- is 1/3 of the
storage space of a varchar(11)?
Do you not give a crap about wasting 2/3rds of your storage space?
Stop making excuses dude.. Just because your database won't fit it--
doesn't mean that you should stoop to using a looser datatype than
the
optimal datatype.
And seriously.. SQL INJECTION-- if you just let numbers in the
fields;
then you lower the chance of injection.
storage space of a varchar(11)?
Do you not give a crap about wasting 2/3rds of your storage space?
Stop making excuses dude.. Just because your database won't fit it--
doesn't mean that you should stoop to using a looser datatype than
the
optimal datatype.
And seriously.. SQL INJECTION-- if you just let numbers in the
fields;
then you lower the chance of injection.
M
mario
"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]> schreef
in bericht
in bericht
J
James A. Fortune
a said:
Nearly all of the known SQL injection techniques simply don't apply to
Access forms, especially ones that do some data validation. Although I
know of one rather bizarre example of the possibility of SQL injection
in Access discovered by accident while testing some rather nonstandard
SQL syntax for a union query posted in another newsgroup, I think you
would be hard pressed to come up with an example of SQL injection on an
Access form in an MDE, even with SQL Server as the backend. Thus, your
invocation of SQL injection vulnerability is counter to the point you
are trying to make unless you are looking forward to using
frontend/backend combinations in the future that don't involve Access at
all.
Although your point that integers larger than Access' Long Type's
capacity afford the possibility of saving some fields more efficiently
than text is true, those situations are limited and the resulting
storage savings are quite modest at best. You're correct that Access
having to work around the storage by using a Text field is not as
elegant or efficient as having a new data type in a future version of
Access that can handle larger integers. Of course, you can do that
anyway, as you said, using the Type statement to define a user-defined
type containing two Long integers limited to 8 places each, stored in
two separate fields. You wouldn't need to create corresponding math
functions since they are not needed with credit card numbers. BTW, why
would the OP store the credit card numbers instead of using something
like Verisign? If you must secure data like credit card numbers in your
database, such as for the purpose of user convenience, then there is,
AFAIK, unanimous agreement that SQL Server is much more secure than
Access/JET for that purpose. Having to use a Text field in Access for
such numbers does not cause enough aggravation to sway the choice to SQL
Server, per se.
James A. Fortune
(e-mail address removed)