credit card numbers
Mick
5544667788998877 i.e. a 16 digit integer?
a a r o n . k e m p f @ g m a i l . c o m
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
fredg
> is it possible to configure a number field to take a normal credit card number
> 5544667788998877 i.e. a 16 digit integer?
If it's a credit card number you're never going to perform math with
it so it should be text anyway.
I did a test using a Number datatype with Field Size of decimal.
Set the format property of the field to Fixed, 0 decimals, 0 Scale,
Precision 16.
It did accept a 16 digit number.
I have no personal experience using a field size of decimal, but I did
read that there were problems with it. Use it at your own risk.
Make the field Text!!!!
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
a a r o n . k e m p f @ g m a i l . c o m
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
Ken Sheridan
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 r o n . k e m p f @ g m a i l . c o m
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
On Apr 15, 3:58 pm, Ken Sheridan
BruceM
original target missed the point, I'm sure it found a receptive audience.
"Ken Sheridan" <KenSh...@discussions.microsoft.com> wrote in message
news:A58C93F6-FF87-49B6...@microsoft.com...
a a r o n . k e m p f @ g m a i l . c o m
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
On Apr 16, 4:46 am, "BruceM" <bam...@yawhodotcalm.not> wrote:
> Interesting stuff. Thanks for providing the information. Even though its
> original target missed the point, I'm sure it found a receptive audience.
>
> "Ken Sheridan" <KenSheri...@discussions.microsoft.com> wrote in message
> >> 5544667788998877 i.e. a 16 digit integer?- Hide quoted text -
>
> - Show quoted text -
BruceM
"a a r o n . k e m p f @ g m a i l . c o m" <aaron...@gmail.com> wrote in
message
news:961f6d8d-cba4-4a2b...@p25g2000pri.googlegroups.com...
Mick
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
"BruceM" wrote:
, > Shoo
a a r o n . k e m p f @ g m a i l . c o m
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
On Apr 16, 12:04 pm, "BruceM" <bam...@yawhodotcalm.not> wrote:
> Shoo
>
> "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com> wrote in
> messagenews:961f6d8d-cba4-4a2b...@p25g2000pri.googlegroups.com...
> > - Show quoted text -- Hide quoted text -
![Arvin Meyer [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjWNeBaELXBA4T-Yh0e0NpfK3HiG4ga607fRgadqcjevhVaQ=s40-c)
Arvin Meyer [MVP]
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
"a a r o n . k e m p f @ g m a i l . c o m" <aaron...@gmail.com> wrote in
message
news:8bcc9087-5e6e-4c4d...@c19g2000prf.googlegroups.com...
a a r o n . k e m p f @ g m a i l . c o m
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
On Apr 17, 5:47 pm, "Arvin Meyer [MVP]" <a...@m.com> wrote:
> 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, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
>
> "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com> wrote in
> messagenews:8bcc9087-5e6e-4c4d...@c19g2000prf.googlegroups.com...
> >> I do not reply to personal e-mail- Hide quoted text -
a a r o n . k e m p f @ g m a i l . c o m
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
On Apr 17, 5:47 pm, "Arvin Meyer [MVP]" <a...@m.com> wrote:
> 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, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
>
> "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com> wrote in
> messagenews:8bcc9087-5e6e-4c4d...@c19g2000prf.googlegroups.com...
Bob Quintal
wrote in
news:7db997fd-23d1-4d86-8ee2-941a13ca2d68
@p25g2000pri.googlegroups.co
m:
> WRONG
Bull!!!!
>
> numbers that are 'always numbers' should 'always be stored as
> numbers.. even if your midget-sized database doesn't fit them very
> nicely'.
>
A number is an abstract entity that represents a count or
measurement. If it does not represent a count or a measurement,
it's just a string.
> It's called 'Data Quality' - something about which you Access kids
> know nothing about.
>
Aaron knows absolutely nothing, about Data Quality, MS Access, or
anything else except being an ass.
--
Bob Quintal
PA is y I've altered my email address.
** Posted from http://www.teranews.com **
a a r o n . k e m p f @ g m a i l . c o m
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
On Apr 18, 4:17 pm, Bob Quintal <rquin...@sPAmpatico.ca> wrote:
> "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com>
Bob Quintal
wrote in
news:904aaa95-4471-41eb-a540-e2148367ae43
@p25g2000pri.googlegroups.co
m:
> dude where do you make this shit up?
I didn't make this up, some dudes named Boyce and Codd did, many
years ago.
>
> A number-- is just like text-- in that it is smaller (less
> characters in the char set).
nope a number is something that it makes sense to add, subtract,
multiply or divide.
>
> Next thing that you'll tell me is that you use a varchar field for
> SSN!!!!
>
What a dork. varchar(12) is a SQL server implementation.
> Do you have -ANY- idea the performance difference between an INT
> and a Varchar(12)?
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?
>
> 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?
an input mask at the table level is quick and efficient. A
validation rule at the table level works too. Alternatively one can
use an event procedure at the form level. There are more ways, but
there's no need for me to expand, since you've got a valid answer to
your question.
>
> WHAT IF THEY DISABLE MACROS?
By setting security too high? then the application doesn't run, and
they can't enter data.
>
> What if they put a SQL Statement _IN_ the text field?
the Jet database engine does a pretty good job of storing it as a
string. I've written code that grabs all the SQL from the querydefs
collection and stores it in a table in another database for
documentation purposes.
Are you implying that your suggested alternative actually executes
the SQL, dumb.
>
> Using numeric fields - everywhere - is one of the best ways to get
> away from SQL injections.
>
Are you putting us on, or are you deliberately trying to prove you
are an id10t?
> -Aaron
Go away.
Just go away.
a a r o n . k e m p f @ g m a i l . c o m
They didn't bother 'making excuses for Access' because it's a piece of
crap database
-Aaron
a a r o n . k e m p f @ g m a i l . c o m
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
> ** Posted fromhttp://www.teranews.com**- Hide quoted text -
a a r o n . k e m p f @ g m a i l . c o m
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
> ** Posted fromhttp://www.teranews.com**- Hide quoted text -
Linq Adams via AccessMonster.com
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via http://www.accessmonster.com
a a r o n . k e m p f @ g m a i l . c o m
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.
On Apr 19, 1:45 pm, "Linq Adams via AccessMonster.com" <u28780@uwe>
wrote:
mario
"a a r o n . k e m p f @ g m a i l . c o m" <aaron...@gmail.com> schreef
in bericht
news:faecf4c5-4a6f-4199...@59g2000hsb.googlegroups.com...
James A. Fortune
> 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.
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
MPAP...@FortuneJames.com
a a r o n . k e m p f @ g m a i l . c o m
Using the 'wrong datatype' is slower, more error prone, etc.
You can enforce the correct datatype-- by using the correct datatype.
Simple stuff, you know?
re:
is it possible to configure a number field to take a normal credit
card number
5544667788998877 i.e. a 16 digit integer?
I'm not so sure that they're asking about Access forms ;)
-Aaron
On Apr 22, 4:34 pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:
> MPAPos...@FortuneJames.com- Hide quoted text -
James A. Fortune
Your points are well taken. Access would have to work around those
issues using less efficient means until it has a native data type that
can accomodate a 16 digit integer. I said that the situations (and
number of fields) where such a data type are useful are limited so there
is not much impetus on Microsoft's part to create a new data type. This
peccadillo on Access' part is not enough in and of itself to cause one
to convert an existing Access application to SQL Server.
James A. Fortune
MPAP...@FortuneJames.com
a a r o n . k e m p f @ g m a i l . c o m
Using a numeric datatype-- would allow for better 'information
quality'.
It would be easier to test.
_EVERY_ single straw-- where SQL is better than Access-- is enough to
break the camels back.
A single straw-- for example 'optimal datatypes' is plenty reason to
move to SQL Server.
Access doesnt' support SmallDateTime, it doesn't even support varchar.
Everything is unicode with Access.
SQL Server is faster, easier.
Sorry-- but just because you're stuck in the 80s; that doesn't mean
that Access is inherently 'easier' than SQL Server.
SQL Server makes sense.
Access forces you to use a new workaround every single hour.
Linked table crap? NOT NECESSARY
BAD datatypes? NOT NECESSARY
I just don't get it.
There isn't a single person in the world that should ever store any
credit card information in either
a) a spreadsheet
b) a database that is _IMPOSSIBLE_ to secure.
Stop making excuses.
So sorry that your database SUCKS.
-Aaron
On May 13, 8:11 am, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:
> Aaron,
>
> Your points are well taken. Access would have to work around those
> issues using less efficient means until it has a native data type that
> can accomodate a 16 digit integer. I said that the situations (and
> number of fields) where such a data type are useful are limited so there
> is not much impetus on Microsoft's part to create a new data type. This
> peccadillo on Access' part is not enough in and of itself to cause one
> to convert an existing Access application to SQL Server.
>
> James A. Fortune
> MPAPos...@FortuneJames.com
James A. Fortune
> I'm just not sure I agree.
>
> Using a numeric datatype-- would allow for better 'information
> quality'.
>
> It would be easier to test.
You're talking about checking to see if the field has all digits, right?
That doesn't seem very hard to test.
>
> _EVERY_ single straw-- where SQL is better than Access-- is enough to
> break the camels back.
> A single straw-- for example 'optimal datatypes' is plenty reason to
> move to SQL Server.
We're only talking about one straw and one that is not "plenty reason"
to move to SQL Server.
>
> Access doesnt' support SmallDateTime, it doesn't even support varchar.
> Everything is unicode with Access.
That's just another, different small straw.
>
> SQL Server is faster, easier.
Probably not.
>
> Sorry-- but just because you're stuck in the 80s; that doesn't mean
> that Access is inherently 'easier' than SQL Server.
> SQL Server makes sense.
Access is inherently easier than SQL Server. Plus, one might argue that
anyone who uses SQL at all is stuck in the 80's. Yet it would not be
practical to give up archaic SQL before having something better in its
place.
>
> Access forces you to use a new workaround every single hour.
No argument there, but even with the workarounds it's still easier
overall to use and maintain than SQL Server. Unless you have a
situation where SQL Server has clear advantages over Access, and that
happens less often than you seem to think, it makes no sense to change
an existing Access application to SQL Server.
>
> Linked table crap? NOT NECESSARY
> BAD datatypes? NOT NECESSARY
>
> I just don't get it.
You mean you don't get why we are not all flocking over to SQL Server
with Access as fast as our nimble fingers can take us there? Maybe
we're all incredibly stupid. If that's the case, you're wasting your
time trying to enlighten us anyway. Calling something stupid without
giving good reasons is out of vogue. Very Microsoft :-). I seemed to
imply that I was calling Microsoft stupid without giving a reason, hence
the smiley. In case you need an explanation, I implied that Microsoft
marketing often resorts to name calling as a marketing tool. Actually,
I don't think Microsoft is stupid at all. They use marketing techniques
that work. It is simply unfortunate that name calling has been
effective so far. What we need from you is cool, concise, logical
reasoning. Do you feel up to it?
>
> There isn't a single person in the world that should ever store any
> credit card information in either
> a) a spreadsheet
> b) a database that is _IMPOSSIBLE_ to secure.
I agree.
>
> Stop making excuses.
> So sorry that your database SUCKS.
You're the one who's trying to dream up impractical situations to show
us why we should switch. There are good reasons to switch in some
situations, but you don't seem to know what the situations are or even
the reasons. The reasons you give seem trivial at best. There is a
good reason hidden in linked, bound tables, but your analysis has left
that reason unexposed. You seem to be struggling with the relative
importance of everything. Perhaps you have too little experience with
Access to be able to make good generalizations about it. That's
understandable if you have been putting most of your time into SQL
Server. At least you tried to come up with some valid reasons for your
last statement.
James A. Fortune
MPAP...@FortuneJames.com
a a r o n . k e m p f @ g m a i l . c o m
I don't think that Access -- continual workarounds for bugs- is easier
than a database server that 'just works'.
I don't think that Access -- impossible to secure- is easier than a
database server that 'just works'.
I don't think that Access -- not reliable- is easier than a database
server that 'just works'.
-Aaron
On May 14, 7:13 am, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:
> MPAPos...@FortuneJames.com
Aaron_Lin
"a a r o n . k e m p f @ g m a i l . c o m" <aaron...@gmail.com>
????????? 1d3f6d19-7b37-4a79...@d26g2000prg.googlegroups.com
???...
> 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
>
>
>
>
> On Apr 15, 1:20 pm, Mick <M...@discussions.microsoft.com> wrote:
>> is it possible to configure a number field to take a normal credit card
>> number
>> 5544667788998877 i.e. a 16 digit integer?
hello
nice to meet u
im working now!
let me test!
>