filter by form/selection MISMATCH in Criteria Expression

I recently asked the group for help on converting 3 numeric variables
to a new date variable. The DATESERIAL function below worked
perfectly! My question now relates to problems with this new variable
when I try to filter either by form or selection.

If I type 10/20/2005, just as you see it, with no quotes or other
punctuation, in the testHarvestDate field I get the following error
message: DATATYPE MISMATCH IN CRITERIA EXPRESSION and Access then locks
up on me!

What is the problem here? Any help would really be appreciated.

Regards,

Mike

testHarvestDate:
DateSerial((IIf([harvest_month]=1,2006,2005)),[harvest_month],[day])

re: filter by form/selection MISMATCH in Criteria Expression

Try this:

CDate(DateSerial("yyyy", "mm", "dd)) where the date in the dateserial is
your important date. It sounds like it is treating DateSerial as a text
value.

Linda



"Takeadoe" <mtonkovich@msn.com> wrote in message
news:1139971094.423898.109490@o13g2000cwo.googlegr oups.com...[color=blue]
>I recently asked the group for help on converting 3 numeric variables
> to a new date variable. The DATESERIAL function below worked
> perfectly! My question now relates to problems with this new variable
> when I try to filter either by form or selection.
>
> If I type 10/20/2005, just as you see it, with no quotes or other
> punctuation, in the testHarvestDate field I get the following error
> message: DATATYPE MISMATCH IN CRITERIA EXPRESSION and Access then locks
> up on me!
>
> What is the problem here? Any help would really be appreciated.
>
> Regards,
>
> Mike
>
> testHarvestDate:
> DateSerial((IIf([harvest_month]=1,2006,2005)),[harvest_month],[day])
>[/color]

re: filter by form/selection MISMATCH in Criteria Expression

Linda,

Thank you for the reply. I tacked cdate on the front as suggested and
got the same error message. Any other suggestions?

Regards,

Mike

re: filter by form/selection MISMATCH in Criteria Expression

Okay, just thought of what that could be. In the query or SQL statement try
adding a # before where the date goes and right after it. Example:

Select YourDateFieldName from Yourtablename where YourDateFieldName = #" &
Me.Sometextboxintheform & "#"

Linda


"Takeadoe" <mtonkovich@msn.com> wrote in message
news:1139972396.662927.140290@g43g2000cwa.googlegr oups.com...[color=blue]
> Linda,
>
> Thank you for the reply. I tacked cdate on the front as suggested and
> got the same error message. Any other suggestions?
>
> Regards,
>
> Mike
>[/color]

re: filter by form/selection MISMATCH in Criteria Expression

Linda - This is the code that creates the new date variable called
testharvestdate.

testHarvestDate:
DateSerial((IIf([harvest_month]=1,2006,2005)),[harvest_month],[day])

I'm not sure where you want me to add the # - in the above or during
the actual filter? Okay, I guess my inexperience with Access is
shining through - loud and clear. I've been a SAS programmer for 15
years and using Access for a few weeks!
Your help and patience are much appreciated.

Mike