Query Criteria to Identify Expired Credit Cards

[Larry Linson]

Re: Query Criteria to Identify Expired Credit Cards

"sharsy" <shari@ptpartne rs.net.auwrote

In my country (Australia) credit cards have
an expiry date on the them in the format
MM/YY. e.g. 09/12 would expire in Sep-
tember 2012 (no specific day but in that
month). I am simply storing the expiry dates
of credit cards in that format.

I think you will find that is common to credit card processing everywhere,
and there is an implied specific date of "last day of the month" if that is
important to you -- thus a card with an expiration date of "09/12" could
last be used on 30 September 2012.

Larry Linson
Microsoft Office Access MVP

[Chuck Grimsby]

Re: Query Criteria to Identify Expired Credit Cards

On Tue, 24 Jun 2008 16:40:55 -0700 (PDT), sharsy
<shari@ptpartne rs.net.auwrote:

>Hi Chuck & Mike P,
>I have tried both of your solutions in relation to converting the
>initial data into a suitable form for sorting/applying criteria to and
>both work.
>But now I'm having trouble with the criteria part... I keep getting
>the error: "Data type mismatch in criteria expression".
>Chuck, I put the following as my Field entry which gets the date into
>a 1/11/2012 format:
>C/Card Expiry: DateSerial(Righ t([C/C Expiry],2),Left([C/C Expiry],2),
>1)
>But I cannot apply a normal date criteria like I have in all my other
>reports.
>Mike P, I tried putting YYMM Format: Right$([C/C Expiry],2) & "/" &
>Left$([C/C Expiry],2) as my field entry, which puts them all in YY/MM
>format, but I couldn't get the criteria part to work. This is what I
>put in:
><=[12/11]
>But then it asked me to put in a parameter value (which I couldn't)
>and then said the criteria was incorrect or too complicated.
>Could either of you please help me?!!

First off, an apology: As Larry quite correctly pointed out, Credit
Cards expire on the _last_ day of the month that's on the card, not
the first. So the criteria I gave you for the DateSerial command was
incorrect. It should of been:
DateSerial(Righ t([fieldName],2),Left([FieldName],2)+1, 0)

Note the +1 for the month and the 0 for the day. That criteria will
correctly calculate the last day of the month the card expires in.

Now, to your current question, DateSerial results in a Date type
field, which Access knows how to sort, so that shouldn't be a problem.
However it also means that the selection criteria also has to be a
Date.
The easiest way to resolve this problem is to provide the query with a
text box (or combobox) on a form that holds the date to be compared
against the query. This will allow the query to reference it as many
times as it needs to "build" the date into a format it needs. Use the
same DateSerial function as posed above, however replace the
"[FieldName]" with the name (and form name) of the control the query
should use to work with. (If your Form is named "MyForm" and the
TextBox on it is named "MyTextBox" , then you'd do something like:

>=DateSerial(Ye ar([Forms]![MyForm]![MyTextBox]), Month([Forms]![MyForm]![MyTextBox]), Day([Forms]![MyForm]![MyTextBox])

Again, remember that "MyTextBox" should hold a =valid= date. Day,
Month and year!

If you just want the user(s) to enter in a mm/yy format, then use:

>=DateSerial(Ri ght([Forms]![MyForm]![MyTextBox],2), Left([Forms]![MyForm]![MyTextBox],2)+1, 0)

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

[sharsy]

Re: Query Criteria to Identify Expired Credit Cards

On Jun 26, 2:27 am, Salad <o...@vinegar.c omwrote:

sharsywrote:

In my country (Australia) credit cards have an expiry date on the them
in the format MM/YY. e.g. 09/12 would expire in September 2012 (no
specific day but in that month).

>
Same in the US.
>
  I am simply storing the expiry dates
>

of credit cards in that format.

>
In your initial posts it seemed you wanted to compare them to a date.
>
It would be much simpler to filter your requests using a date field.
>
Since you don't want or need to use a date field then in the query
builder, I guess I'd drag your expiration field to 3 columns.  I guess
you are storing it in MM/YY format.  The first field would be for
display, the last two fields I'd parse out the year and month for
filtering purposes...I'd uncheck the Show box for them.
>
'This will create string column fields
ExpMonth : Left([C/C Expiry],2)
ExpYear : Right([C/C Expiry],2)
>
'This will create Numeric column fields
ExpMonth : Cint(Left([C/C Expiry],2))
ExpYear : Cint(Right([C/C Expiry],2))
>
Let's say you had a text box MMYY with 01/12...expires Jan, 2012.
Then you could filter to if string
        <Left(Forms!You rFormName!MMYY, 2)
        <Right(Forms!Yo urFormName!MMYY ,2)

Cool - having the two extra fields split it up so that I could sort it
normally. Great! thanks heaps!!