Query Criteria to Identify Expired Credit Cards

[sharsy]

Hello guys,

I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.

The specific Field [C/Card Expire] is formatted with a Data Type of
'Text' and has an input mask of '00/00', so all expiry dates are set
out for example as 10/13 (which represents October 2013).

I have hada brief go at trying to work it out, but I was unable to
come up with criterion that looked at the year first...

Any help would be greatly appreciated!!!

Kind regards,

sharsy

[Salad]

Re: Query Criteria to Identify Expired Credit Cards

sharsy wrote:

Hello guys,
>
I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.
>
The specific Field [C/Card Expire] is formatted with a Data Type of
'Text' and has an input mask of '00/00', so all expiry dates are set
out for example as 10/13 (which represents October 2013).
>
I have hada brief go at trying to work it out, but I was unable to
come up with criterion that looked at the year first...
>
Any help would be greatly appreciated!!!
>
Kind regards,
>
sharsy

Maybe this concept will help. This was done from the debug/immediate
window.
? cdate("03/2013")
3/1/2013
? cdate("03/2013") < #3/1/2013#
False
? cdate("03/2013") = #3/1/2013#
True
? cdate("03/2013") #3/1/2013#
False

OK, so you can create a "calculated " field in a query. Your field is
called [C/Card Expire]. Not the way I'd name a field but if you like
your naming scheme more power to you.

A calculated query field has these components
ColumnName : Function/Constant

In your case
CCDateFld : Cdate(Left([C/Card Expire],3) & "20" & Mid([C/Card Expire],4))

Basically I'm creating a date field by adding "20" to the 2 digit year.
I suppose you can use an IIF() statement if you need to go back to the
1900's. This column in your query can then be filtered.

Water

http://www.youtube.com/watch?v=IWbe-NSK6Ic

[sharsy]

Re: Query Criteria to Identify Expired Credit Cards

Yeah I just tried that but it doesn't seem to work - it displays dates
but they don't match up to the expiry date. Also, it seems to allocate
a specific date (ie. the 19th or 31st) which is not relevant for the
purposes I need it for - It would be fine if they were all allocated a
date of the 1st (e.g. 01/05/08) because then they would still be equal.

[Salad]

Re: Query Criteria to Identify Expired Credit Cards

sharsy wrote:

Yeah I just tried that but it doesn't seem to work - it displays dates
but they don't match up to the expiry date.

I have no idea what your expiry date is. And, IMO, if its supposed to
match an expiry date, use the expiry date instead

Also, it seems to allocate

a specific date (ie. the 19th or 31st) which is not relevant for the
purposes I need it for - It would be fine if they were all allocated a
date of the 1st (e.g. 01/05/08) because then they would still be equal.

? cdate("03/2021")
3/1/2021

It always defaults to the 1st date of the month for me. Maybe its due
to you using a European date format. Look at DataSerial so you can
stuff the month, day, year if you need specific.

Actually, I suppose you'd want it to be the last day of the month so I'd
add a month and subtract a day to get the last date of the month.

? DateAdd("m",1,c date("03/2021"))-1
3/31/2021

Skye

http://www.youtube.com/watch?v=jpfBl33if4U

[Chuck Grimsby]

Re: Query Criteria to Identify Expired Credit Cards

On Wed, 04 Jun 2008 07:08:29 -0700, Salad <oil@vinegar.co mwrote:

>sharsy wrote:

>Yeah I just tried that but it doesn't seem to work - it displays dates
>but they don't match up to the expiry date.

>
>I have no idea what your expiry date is. And, IMO, if its supposed to
>match an expiry date, use the expiry date instead
>
Also, it seems to allocate

>a specific date (ie. the 19th or 31st) which is not relevant for the
>purposes I need it for - It would be fine if they were all allocated a
>date of the 1st (e.g. 01/05/08) because then they would still be equal.

>
>? cdate("03/2021")
>3/1/2021
>
>It always defaults to the 1st date of the month for me. Maybe its due
>to you using a European date format. Look at DataSerial so you can
>stuff the month, day, year if you need specific.
>
>Actually, I suppose you'd want it to be the last day of the month so I'd
>add a month and subtract a day to get the last date of the month.
>
>? DateAdd("m",1,c date("03/2021"))-1
>3/31/2021

In the original post, the OP mentioned that the field was a text field
with a mask of "00/00" meaning "mm/yy". Since that's the case, VBA
won't be able to figure out what the first 2 characters of the year
will be when using CDate. Indeed, VBA will assume a year isn't being
given at all.

Using DateSerial would be a better option here. Something along the
lines of:
DateSerial(Righ t([fieldName],2),Left([FieldName],2),1)

You may notice in the above example, I used a "fixed" day of the month
(1) to ensure that the resultant date is always the first of the
month. The OP may want to use 0 however, to get the last day of the
previous month for their calculations if they are using Greater Then
or Less Then as needed.

When using DateSerial, if a 2 digit year is given, VBA will use the
System Calendar settings as when to break between 1900's and 2000's.

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

[Salad]

Re: Query Criteria to Identify Expired Credit Cards

Chuck Grimsby wrote:

On Wed, 04 Jun 2008 07:08:29 -0700, Salad <oil@vinegar.co mwrote:
>
>

>>sharsy wrote:
>>

>>>Yeah I just tried that but it doesn't seem to work - it displays dates
>>>but they don't match up to the expiry date.

>>
>>I have no idea what your expiry date is. And, IMO, if its supposed to
>>match an expiry date, use the expiry date instead
>>
>Also, it seems to allocate
>>

>>>a specific date (ie. the 19th or 31st) which is not relevant for the
>>>purposes I need it for - It would be fine if they were all allocated a
>>>date of the 1st (e.g. 01/05/08) because then they would still be equal.

>>
>>? cdate("03/2021")
>>3/1/2021
>>
>>It always defaults to the 1st date of the month for me. Maybe its due
>>to you using a European date format. Look at DataSerial so you can
>>stuff the month, day, year if you need specific.
>>
>>Actually, I suppose you'd want it to be the last day of the month so I'd
>>add a month and subtract a day to get the last date of the month.
>>
>>? DateAdd("m",1,c date("03/2021"))-1
>>3/31/2021

>
>
In the original post, the OP mentioned that the field was a text field
with a mask of "00/00" meaning "mm/yy". Since that's the case, VBA
won't be able to figure out what the first 2 characters of the year
will be when using CDate. Indeed, VBA will assume a year isn't being
given at all.
>
Using DateSerial would be a better option here. Something along the
lines of:
DateSerial(Righ t([fieldName],2),Left([FieldName],2),1)
>
You may notice in the above example, I used a "fixed" day of the month
(1) to ensure that the resultant date is always the first of the
month. The OP may want to use 0 however, to get the last day of the
previous month for their calculations if they are using Greater Then
or Less Then as needed.
>
When using DateSerial, if a 2 digit year is given, VBA will use the
System Calendar settings as when to break between 1900's and 2000's.
>

Hi Chuck. Yes, I mentioned DateSerial if one needed a specific date. I
noticed he wants to compare the date from the "00/00" format to an
expiration date. I wonder why the person just doesn't use the
expiration date. Not enough info from the OP.

In my first example to the person, that was snipped, I added a "20" to
the year and I always got the first day of that month/year field.
CCDateFld : Cdate(Left([C/Card Expire],3) & "20" & Mid([C/Card Expire],4))

The OP says that doesn't work, gives him random day results, so I figure
the person must be using a European date format and that is why I
mentioned using DateSerial. Date field formats are a mini tower of Babel.

Then I wondered when it really expires; does it expire on the first day
or the last day of the month or anytime within the month. So I added a
month in my Cdate calc to get last of month if last of month is
required. Again, not enough info from the OP.

Basically, the OPs problem is not complicated if the person knows what
he/she needs. I suppose I'd have simply put the expiration, instead of
"06/08", into the table as a DateField with an actual expiration date.
I could then output the "06/08" using Format() for ease of use. Some
people add complexity to their issues.

Expiration Date

http://www.youtube.com/watch?v=OqprGc3ZlOw

[Roger]

Re: Query Criteria to Identify Expired Credit Cards

On Jun 4, 6:35 pm, Salad <o...@vinegar.c omwrote:

Chuck Grimsby wrote:

On Wed, 04 Jun 2008 07:08:29 -0700, Salad <o...@vinegar.c omwrote:

>

>sharsy wrote:

>

>>Yeah I just tried that but it doesn't seem to work - it displays dates
>>but they don't match up to the expiry date.

>

>I have no idea what your expiry date is.  And, IMO, if its supposed to
>match an expiry date, use the expiry date instead

>

Also, it seems to allocate

>

>>a specific date (ie. the 19th or 31st) which is not relevant for the
>>purposes I need it for - It would be fine if they were all allocated a
>>date of the 1st (e.g. 01/05/08) because then they would still be equal.

>

>? cdate("03/2021")
>3/1/2021

>

>It always defaults to the 1st date of the month for me.  Maybe its due
>to you using a European date format.  Look at DataSerial so you can
>stuff the month, day, year if you need specific.

>

>Actually, I suppose you'd want it to be the last day of the month so I'd
>add a month and subtract a day to get the last date of the month.

>

>? DateAdd("m",1,c date("03/2021"))-1
>3/31/2021

>

In the original post, the OP mentioned that the field was a text field
with a mask of "00/00" meaning "mm/yy".  Since that's the case, VBA
won't be able to figure out what the first 2 characters of the year
will be when using CDate.  Indeed, VBA will assume a year isn't being
given at all.

>

Using DateSerial would be a better option here.  Something along the
lines of:
DateSerial(Righ t([fieldName],2),Left([FieldName],2),1)

>

You may notice in the above example, I used a "fixed" day of the month
(1) to ensure that the resultant date is always the first of the
month.  The OP may want to use 0 however, to get the last day of the
previous month for their calculations if they are using Greater Then
or Less Then as needed.

>

When using DateSerial, if a 2 digit year is given, VBA will use the
System Calendar settings as when to break between 1900's and 2000's.

>
Hi Chuck.  Yes, I mentioned DateSerial if one needed a specific date.  I
noticed he wants to compare the date from the "00/00" format to an
expiration date.  I wonder why the person just doesn't use the
expiration date.  Not enough info from the OP.
>
In my first example to the person, that was snipped, I added a "20" to
the year and I always got the first day of that month/year field.
        CCDateFld : Cdate(Left([C/Card Expire],3) & "20" & Mid([C/Card Expire],4))
>
The OP says that doesn't work, gives him random day results, so I figure
the person must be using a European date format and that is why I
mentioned using DateSerial.  Date field formats are a mini tower of Babel.
>
Then I wondered when it really expires; does it expire on the first day
or the last day of the month or anytime within the month.  So I added a
month in my Cdate calc to get last of month if last of month is
required.  Again, not enough info from the OP.
>
Basically, the OPs problem is not complicated if the person knows what
he/she needs.  I suppose I'd have simply put the expiration, instead of
"06/08", into the table as a DateField with an actual expiration date.
I could then output the "06/08" using Format() for ease of use.  Some
people add complexity to their issues.
>
Expiration Datehttp://www.youtube.com/watch?v=OqprGc3 ZlOw- Hide quoted text -
>
- Show quoted text -

it's not added complexity, credit card expiry dates don't deal with
the 'day' of the month
they just have month / year in "mm/yy" format

[Mike P]

Re: Query Criteria to Identify Expired Credit Cards

On Jun 4, 3:05 am, sharsy <sh...@ptpartne rs.net.auwrote:

Hello guys,
>
I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.
>
The specific Field [C/Card Expire] is formatted with a Data Type of
'Text' and has an input mask of '00/00', so all expiry dates are set
out for example as 10/13 (which represents October 2013).
>
I have hada brief go at trying to work it out, but I was unable to
come up with criterion that looked at the year first...
>
Any help would be greatly appreciated!!!
>
Kind regards,
>
sharsy

In the Query grid, have you tried a calculated field to transpose the
month and year of [C/Card Expire] and then do a "<=" criterion
statement on the calculated field?

For example, in a field cell, enter::

YYMM Format: Right$([C/Card Expire],2) & "/" & Left$([C/Card Expire],
2)

In the Criteria cell for the calculated field, enter:

<=[Enter Expiry Date in YY/MM format]

Run the query. Enter the required expiry date such as 08/05 when
requested.

Mike P

[sharsy]

Re: Query Criteria to Identify Expired Credit Cards

I just tried Chuck's solution - and it totally worked perfectly!!!!!
Thank you very much!

[Chuck Grimsby]

Re: Query Criteria to Identify Expired Credit Cards


You're quite welcome.

Thanks for letting us know what worked!

On Thu, 5 Jun 2008 16:28:58 -0700 (PDT), sharsy
<shari@ptpartne rs.net.auwrote:

>I just tried Chuck's solution - and it totally worked perfectly!!!!!
>Thank you very much!

--
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

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?!!

Thank you heaps

[Salad]

Re: Query Criteria to Identify Expired Credit Cards

sharsy wrote:

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?!!
>
Thank you heaps
>
>
>

Why are you using [] in your criteria as in [12/11]? Do you have a
field name called 12/11? Why not use <"12/11".

Always make sure you have zero padded months. Personally, I'd split the
two up so you sort and filter on the month and sort filter on the year.

This seems like such an easy problem. One thing you might want to add
in your problem description is whether you use American dates or what
the date format you use in your country.

You might also want to inform us what you are storing. Are you storing
2 fields? If so, why? Does your expiration date field (mm/dd/yyyy)
start in the first of the month, end of the month, or anytime in the
month?

If I were useing a date field, I'd prefer using Year(dateField) and
Month(DateField ) to parse out the month/year values and filtering on them.

[sharsy]

Re: Query Criteria to Identify Expired Credit Cards

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). I am simply storing the expiry dates
of credit cards in that format.


On Jun 25, 10:29 am, Salad <o...@vinegar.c omwrote:

sharsywrote:

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?!!

>

Thank you heaps

>
Why are you using [] in your criteria as in [12/11]?  Do you have a
field name called  12/11?  Why not use <"12/11".
>
Always make sure you have zero padded months.  Personally, I'd split the
two up so you sort and filter on the month and sort filter on the year.
>
This seems like such an easy problem.  One thing you might want to add
in your problem description is whether you use American dates or what
the date format you use in your country.
>
You might also want to inform us what you are storing.  Are you storing
2 fields?  If so, why?  Does your expiration date field (mm/dd/yyyy)
start in the first of the month, end of the month, or anytime in the
month?
>
If I were useing a date field, I'd prefer using Year(dateField) and
Month(DateField ) to parse out the month/year values and filtering on them..- Hide quoted text -
>
- Show quoted text -

[Mike P]

Re: Query Criteria to Identify Expired Credit Cards

On 25 Jun, 00:40, sharsy <sh...@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?!!
>
Thank you heaps

In the criteria cell for the calculated expiry date, type in "<=[Enter
Expiry Date in YY/MM format] ", literally, ignoring the quotes. This
will result in a request for the expiry date to be entered when the
query is run, at which point, enter "12/11" (ignore quotes, no
comparison operators), OR
if you would like to test the selection technique, then type in the
criteria cell, <= "12/11", for expiry date November 2012; no square
brackets, include quotes.

In general, text in square brackets refers to a field name. If the
field does not exist, a parameter name is assumed. The value of the
parameter is accepted from the User when the query is run. The value
of the parameter can be displayed in the query results by including
the parameter name in a field cell, e.g. [Enter Expiry Date in YY/MM
format] (include square brackets).


Mike P

[Salad]

Re: Query Criteria to Identify Expired Credit Cards

sharsy wrote:

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)
or numeric
<Cint(Left(Form s!YourFormName! MMYY,2))
<Cint(Right(For ms!YourFormName !MMYY,2))

As I've noted previously, I'd be so much easier for you to use a real
date field. You could then filter using Month() and Year() and display
the date field however using Format(). Ex:
Format([C/C Expiry],"mm/yy")
Year([C/C Expiry])
Month([C/C Expiry])

BTW, you might be wondering why I'd split the month/year into fields for
filtering. Because you are using a string, not a number. Example.
? "05/10" < "01/12"
False
This occurred because 05 is greater than 01. I think you could see this
in action simply by opening that table and hitting the AZ button when
sitting on [C/C Expiry].


Calienta
http://www.youtube.com/watch?v=2lzqfpnhu3Y

>
On Jun 25, 10:29 am, Salad <o...@vinegar.c omwrote:
>

>>sharsywrote :
>>

>>>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?!!

>>

>>>Thank you heaps

>>
>>Why are you using [] in your criteria as in [12/11]? Do you have a
>>field name called 12/11? Why not use <"12/11".
>>
>>Always make sure you have zero padded months. Personally, I'd split the
>>two up so you sort and filter on the month and sort filter on the year.
>>
>>This seems like such an easy problem. One thing you might want to add
>>in your problem description is whether you use American dates or what
>>the date format you use in your country.
>>
>>You might also want to inform us what you are storing. Are you storing
>>2 fields? If so, why? Does your expiration date field (mm/dd/yyyy)
>>start in the first of the month, end of the month, or anytime in the
>>month?
>>
>>If I were useing a date field, I'd prefer using Year(dateField) and
>>Month(DateFie ld) to parse out the month/year values and filtering on them.- Hide quoted text -
>>
>>- Show quoted text -

>
>