472,981 Members | 1,318 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,981 software developers and data experts.

Query Criteria to Identify Expired Credit Cards

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
Jun 27 '08 #1
17 5455
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

Jun 27 '08 #2
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.
Jun 27 '08 #3
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,cdate("03/2021"))-1
3/31/2021

Skye
http://www.youtube.com/watch?v=jpfBl33if4U
Jun 27 '08 #4
On Wed, 04 Jun 2008 07:08:29 -0700, Salad <oi*@vinegar.comwrote:
>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,cdate("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(Right([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!
Jun 27 '08 #5
Chuck Grimsby wrote:
On Wed, 04 Jun 2008 07:08:29 -0700, Salad <oi*@vinegar.comwrote:

>>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,cdate("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(Right([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
Jun 27 '08 #6
On Jun 4, 6:35*pm, Salad <o...@vinegar.comwrote:
Chuck Grimsby wrote:
On Wed, 04 Jun 2008 07:08:29 -0700, Salad <o...@vinegar.comwrote:
>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,cdate("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(Right([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=OqprGc3ZlOw- 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
Jun 27 '08 #7
On Jun 4, 3:05 am, sharsy <sh...@ptpartners.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
Jun 27 '08 #8
I just tried Chuck's solution - and it totally worked perfectly!!!!!
Thank you very much!
Jun 27 '08 #9

You're quite welcome.

Thanks for letting us know what worked!

On Thu, 5 Jun 2008 16:28:58 -0700 (PDT), sharsy
<sh***@ptpartners.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!
Jun 27 '08 #10
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(Right([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

Jun 27 '08 #11
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(Right([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.

Jun 27 '08 #12
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.comwrote:
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(Right([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 -
Jun 27 '08 #13
On 25 Jun, 00:40, sharsy <sh...@ptpartners.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(Right([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
Jun 27 '08 #14
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!YourFormName!MMYY,2)
<Right(Forms!YourFormName!MMYY,2)
or numeric
<Cint(Left(Forms!YourFormName!MMYY,2))
<Cint(Right(Forms!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.comwrote:
>>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(Right([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 -

Jun 27 '08 #15
"sharsy" <sh***@ptpartners.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

Jun 27 '08 #16
On Tue, 24 Jun 2008 16:40:55 -0700 (PDT), sharsy
<sh***@ptpartners.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(Right([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(Right([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(Year([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(Right([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!
Jun 27 '08 #17
On Jun 26, 2:27*am, Salad <o...@vinegar.comwrote:
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!YourFormName!MMYY,2)
* * * * <Right(Forms!YourFormName!MMYY,2)
Cool - having the two extra fields split it up so that I could sort it
normally. Great! thanks heaps!!
Jun 27 '08 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Josh Armstrong | last post by:
I have a option group(ogpLimiter) with 3 choices on my form(SearchMaster), "All"=3, "Expired"=2 and "Valid"=1. I would like to set it so that when the user selects one of the options it will...
3
by: Matthew | last post by:
I am trying to build a form that has certain criteria on it (combo boxes) which a user can pick from a range of values to specify the criteria he wants when he runs a query on the form. I am...
3
by: Chuck | last post by:
Hi, I have a somewhat unique problem (at least unique to me, and I've been doing this for longer than I care to admit). I have a client that needs to print cards onto perforated card stock (so...
5
by: Jake | last post by:
I in the process of building a web-site using .net (C#). I just finished building the shopping cart. What I need is to find examples, details, or code packages that I can include in my web-site...
6
by: Arne | last post by:
What would be a good component for processing credit cards? (I am not using commerce server.) Would I need to encrypt the credit card column in the database?
36
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I...
5
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed...
3
by: hharriel | last post by:
Hi All, I have created an update query related to high school course information (name of course; credit hour; course description, etc.) I am updating a master course information table. I am...
0
by: Alagas | last post by:
Hi, I'm new to access 2003 & would like to know how to create a report that shows the grand total of counts, details are as the following: 1) I've a database of credit cards where I would like to...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.