Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

filter not working on my access

Posted on 2012-12-25
14
Medium Priority
?
384 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 40

Assisted Solution

by:als315
als315 earned 1000 total points
ID: 38720829
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 1000 total points
ID: 38720976
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
ID: 38722901
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:als315
ID: 38722972
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
ID: 38722988
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 40

Expert Comment

by:als315
ID: 38723044
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
ID: 38723131
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
 
LVL 40

Expert Comment

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

Author Comment

by:developingprogrammer
ID: 38723272
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 40

Expert Comment

by:als315
ID: 38723296
Thanks, I prefer test these things :)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38723298
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
ID: 38723712
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 40

Expert Comment

by:als315
ID: 38724330
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
ID: 38724605
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

876 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