Prev: Changing Form's view in VBA
Next: How to use operators Like and Not Like together?
From: sharsy on 24 Jun 2008 19:40
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



From: Salad on 24 Jun 2008 20:29
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.

From: sharsy on 25 Jun 2008 01:53
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...(a)vinegar.com> wrote:
> 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 -

From: Mike P on 25 Jun 2008 04:44
On 25 Jun, 00:40, sharsy <sh...(a)ptpartners.net.au> 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

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
From: Salad on 25 Jun 2008 12:27
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...(a)vinegar.com> wrote:
>
>>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 -
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Changing Form's view in VBA
Next: How to use operators Like and Not Like together?