Solved

filter not working on my access

Posted on 2012-12-25
14
374 Views
Last Modified: 2012-12-27
hey guys, i have a query filter that doesn't work on my compter but works on irogsinta's computer. i haven't had the chance to try it on anyone else's computer at my work area (christmas) - but can yall run this query using the dates 1 dec to 11 dec? mine always turns up blank. i tried compacting and repairing database - couldn't work, tried copying objects to new database couldn't work, tried restarting computer couldn't work.

problemokQuery.mdb
0
Comment
Question by:developingprogrammer
  • 6
  • 6
  • 2
14 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
Comment Utility
I have other regional settings, but, when I've removed Datevalue from filter expression (why do you need it?) it is working:
Dim f As String
If Not IsNull(Me.txtServicingWorkHoursStartDate) And Not IsNull(Me.txtServicingWorkHoursEndDate) Then
    f = "[CalendarDate] >= #" & Me.txtServicingWorkHoursStartDate & "# AND [CalendarDate] <= #" & Me.txtServicingWorkHoursEndDate & "#"
    Forms!frmMTKManager.subfrmServicingWorkHours.Form.Filter = f

Open in new window

On problem picture I see yellow "Filtered", when on my working form it is gray, but filter working. May be you have some other filter?
Query.mdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
Comment Utility
I believe the Date that is coming from your query is actually text - which would make the comparison alphabetical rather than by date.

Try any of these:

    Forms!frmMTKManager.subfrmServicingWorkHours.Form.Filter = "Format([CalendarDate], 'yyyymmdd')  >= '" & Format(Me.txtServicingWorkHoursStartDate, "yyyymmdd")  & "' AND Format([CalendarDate], 'yyyymmdd') <= '" & Format(Me.txtServicingWorkHoursEndDate, "yyyymmdd") & "'"

Open in new window


Or:

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

Open in new window


Or:

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

Open in new window

0
 

Author Comment

by:developingprogrammer
Comment Utility
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!!!!!
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
0
 

Author Comment

by:developingprogrammer
Comment Utility
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 = (
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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#
0
 

Author Comment

by:developingprogrammer
Comment Utility
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)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:als315
Comment Utility
Try to set format of field to short date. In Access 2010 I have datepicker on your form
0
 

Author Comment

by:developingprogrammer
Comment Utility
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 = )
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Thanks, I prefer test these things :)
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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.
0
 

Author Comment

by:developingprogrammer
Comment Utility
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) & "#"

Open 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!
0
 
LVL 39

Expert Comment

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

Author Comment

by:developingprogrammer
Comment Utility
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!!! = ))
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now