filter not working on my access

my goodness guys yall are geniuses!!!

als315 - how come i can't put the datevalue? why does it give me an error? if we datevalue a date then it's still ok right? the reason why i put it there was cause the user was typing in text e.g. "28 nov" "8 dec" and i wanted Access to definitively convert it to date e.g. 41241 for "28 nov" cause i'm feeling that letting the user type text and letting Access guess is a bit risky cause Access is 'guessing'. what if (ok i know it sounds stupid but..!!) someone's name was "8 nov" and it was meant to be a text?

so anyway als315 how come:
1) datevaluing the textbox gives me an error?
2) how do i convert a text date into 41241 (which stands for 28 nov 2012)?

 mbizup - thanks so much for your help too!!! the 1st and 3rd line of code works! the 2nd says type mismatch.

1) why does the 2nd line give a type mismatch error? i think this might link to the question i asked als315 above on why can't i datevalue the textbox
2) in VBA there is a TypeName(variable) function - how did you know what...
2a) type the textbox is
2b) type the query was outputting [calendardate] as
2c) change the [calendardate] output from string to date? as of now i'm still not too sure what type the calendardate is right now.

THANKS SO MUCH GUYS!!!!! SAVED ME ONCE AGAIN!!!!!

You can't convert "8 dec" to date with datevalue if year is not added, so you have an error. You can parse entered value and add year, but I'm not sure it is the best way.
I don't know why "8 dec" is working in filter, it was surprize for me, but it is working. If you enter non-date value into text field, you'll get an error.
Seems filter is working with any part of date value, but you should always use US date notation:
Start date could be: 8 dec or 12/08, but all years with these dates range will be filtered

Whao! All years with this date range will be filtered??! I never knew that!!

Ok but great point on the lack of year causing the error. Ah but I just checked - when I datevalue 8 dec it gives me 8/12/2012. So it is entering in the current year. If datevalue functioning a yearless textbox were really the problem, then how come I can run msgbox datevalue of the text box? I'm a bit confused = (

May be better to use datepicker?
I've tested filtering, you are correct, Access is filtering only current year (and I again don't understand why). Filter expression is:
[CalendarDate] >= #1 dec# AND [CalendarDate] <= #12 dec#

ya i get what you mean. hrmm Microsoft Access is sometimes an enigma that we have to research. it's quite cool that we create something out of our bare hands and then we have to research it. hahaha, well i guess if we could put all the brains that created Access together then we'll truly know its nature = )

hrmm datepicker, it doesn't exist for .mdb files right? Access 2007 onwards has it i understand. am i correct als315? or is there a way i can do a date picker (non-manual one using combo boxes)

Try to set format of field to short date. In Access 2010 I have datepicker on your form

Oh so sorry als315! I got confused. The video at Lynda.com was saying that the difference between 07 and 03 is that 07 you can put images as a data type and also tables in cells cause they help us normalise it. But the date picker is the 07 onwards feature in terms of the Access application not the jetdatabase. If the video on the differences will be useful to you I can Dropbox it to you = )

Thanks, I prefer test these things :)

In response to your questions about my post:

1.  If your data has any blanks/nulls, the CDate function will return a mismatch error
2a. You  can treat your controls as variants.  They can be added to numbers, concatenated with text, compared to dates, etc...
2b.  This was a bit of a guess...Even if your underlying field is defined as a Date, queries often will return the data as text (This is syntax dependent). So the variant from your textbox and 'date' from your query are now being compared/sorted as text not dates (ie:alphabetically).  We see similar issues show up with numbers in some queries too -- in some cases you need to explicitly convert to numbers or format data so that it sorts properly.
2c.  >>> change the [calendardate] output from string to date?
       use CDate or DateValue  (you need to do this on both sides of the comparison for it to work properly).
       >>> i'm still not too sure what type the calendardate is right now
       Probably Date in your original table, but after querying it is coming out as text and sorting as text.

thanks mbizup for your response!

for the second line of code that wasn't working

Forms!frmMTKManager.subfrmServicingWorkHours.Form.Filter = "CDate([CalendarDate])  >= #" & DateValue(Me.txtServicingWorkHoursStartDate) & "# AND CDate([CalendarDate]) <= #" & DateValue(Me.txtServicingWorkHoursEndDate) & "#"

Select allOpen in new window

the data had no blanks or nulls (i tried using the database i uploaded in the first post containing the question). i think it has to do with the datevalue cause as als315 was pointing out just now i shouldn't put the datevalue there - and when i removed it, it worked. do you know why the second line of code doesn't work?

but we were both puzzled why msgbox datevaluing the textbox worked fine but put it in a query and it exploded! why is that so do you have any idea? so strange!

It is not good idea to use "#" and Datevalue(..) after it. Will be better:
CDate([CalendarDate])  >= #" & Me.txtServicingWorkHoursStartDate & "#...
or
CDate([CalendarDate])  >= DateValue(" & Me.txtServicingWorkHoursStartDate & ") "

hrmm, not sure why now when i try it again it didn't churn out an error, just gave me empty records. but als315 if i remove datevalue and still keep the # it gives me the correct result. if i take away the # and leave the datevalue i get empty records. and also for the 3rd line now. ah sigh not sure what i was doing just now vs now but anyway... (exhausting!)

oh well anyway think this incessant date thingy has gone on for long enough. i'll try the variations the next time round i hit a date problem again guys. thanks for all your help!!! = ))