By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,213 Members | 1,342 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,213 IT Pros & Developers. It's quick & easy.

filter by form/selection MISMATCH in Criteria Expression

P: n/a
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])

Feb 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <mt********@msn.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
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])

Feb 15 '06 #2

P: n/a
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

Feb 15 '06 #3

P: n/a
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" <mt********@msn.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
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

Feb 15 '06 #4

P: n/a
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

Feb 15 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.