473,326 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to Filter formatted Date fields in MS Access?

Hi,

I have a combobox named[listDateSelect] which allows users to filter records from my PrepareQuery table/query. The field[listDateSelect] has:

Expand|Select|Wrap|Line Numbers
  1. recordsource = "SELECT DISTINCT Format ([PrepareDate],"yyyy-mm") AS [YYYY-MM] FROM PrepareQuery ORDER BY Format([PrepareDate],"yyyy-mm") DESC"
  2.  
What I need is to format the [PrepareDate] with YYYY-MM

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "Format([PrepareDate],"yyyy-mm") = " & Me.listDateSelect
  2. Me.FilterOn = True
  3. Me.Requery
  4. Me.Refresh
  5.  
I have tried a number of times in applying the 'Format' function. Though the commands were not prompting any errors, still I cannot get the results I wanted (usually blank or zero records were displayed).


Thanks team,
Josh
Jun 9 '11 #1
4 6900
I would try changing your filter property to:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "Format([PrepareDate],"yyyy-mm") = """ & Me.listDateSelect & """"
  2.  
so that you'r comparing string to string (according to Help, the Format function returns a string). If that doesn't work, try:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "Format([PrepareDate],"yyyy-mm") = #" & Me.listDateSelect & "#"
  2.  
to compare as a date.
Jun 9 '11 #2
NeoPa
32,556 Expert Mod 16PB
Filtering is very much better done with the raw data (in other words date rather than formatted date string). It's important to know and understand exactly what you are comparing with what. Certainly both sides of the comparison must be of the same (preferably) or convertible type (even though any literals - string numeric and date - must be entered into the SQL as a string as that is how SQL commands are issued).

Filtering with dates causes loads of problems, but only because most people don't understand all the issues. Even many experts here treat date comparisons as if the format of the literal date value can be left to the local default, which is not true as it should be formatted as m/d/yyyy whether you are in the USA or in Europe.

Expand|Select|Wrap|Line Numbers
  1. Private Const conFilter As String = "([PrepareDate] = #m/d/yyyy#)"
  2.  
  3. With Me
  4.     .Filter = Replace(conFilter, _
  5.                       "m/d/yyyy", Format(CDate(.listDateSelect), "m/d/yyyy")
  6.     .FilterOn = (.Filter > "")
  7.     Call .Requery
  8. End With
Jun 13 '11 #3
Hi jpatchak, I tried them both, still there were no records displayed.
NeoPa, actually I really need to filter records with the "yyyy-mm" format. Though I changed your script, no records were displayed :-(

Should I change my approach on filtering data in forms?
Thanks jpatchak and NeoPa.
Jun 29 '11 #4
NeoPa
32,556 Expert Mod 16PB
Josh Andrews:
NeoPa, actually I really need to filter records with the "yyyy-mm" format.
Strange comment. Unless of course you really mean you need to filter the dates to match a whole month rather than a single day? That would make sense, and it seems I missed that point from your first post. I'll include below an amended version for you to try.
Josh Andrews:
Though I changed your script, no records were displayed :-(
I'd comment on this, and maybe help you further if I could, but you haven't actually posted anything indicating what you tried, other than that it's different from what I posted. I now realise what I posted would not suit your requirements, but I still can't comment on what you've tried as I have no idea what it was.

Anyway, try this code instead :
Expand|Select|Wrap|Line Numbers
  1. Private Const conFilter As String = _
  2.                   "([PrepareDate] Between #m/d/yyyy1# And #m/d/yyyy2#)"
  3. Dim strFilter As String
  4. Dim datThis As Date
  5.  
  6. With Me
  7.     strFilter = Format(DateAdd("m", 1, CDate(.listDateSelect)), "m/yyyy")
  8.     datThis = CDate(strFilter) - 1
  9.     strFilter = Replace(conFilter, "m/d/yyyy1", datThis, "m/1/yyyy")
  10.     .Filter = Replace(strFilter, "m/d/yyyy2", datThis, "m/d/yyyy")
  11.     .FilterOn = (.Filter > "")
  12.     Call .Requery
  13. End With
As a final explanation that may help your understanding, filtering is most efficient when no special functions (like Format(); Year(); Month(); etc) are used in the SQL itself (as opposed to when creating the SQL string). SQL cannot perform to its best when it has to stop and go elsewhere for each record processed. Hence, though it's easier to code using Format(), it would not be what I recommend.
Jun 29 '11 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Todd D. Levy | last post by:
Normally, I have a date field in the query that a report is based on that requests the user to select a start date and an end date. I now have a situation where there are 2 date fields that the...
4
by: Stewart Allen | last post by:
I'm trying to filter a table that has 2 date fields, the first date will always have a value but the second will only occasionally has a value. Each date field also has a corresponding text field...
4
by: S. van Beek | last post by:
Dear reader, By a Date field with Now() as default value the content of the field is date plus time. As I need a filter in a query on date only (excluding time) I invented the following...
1
by: Riley DeWiley | last post by:
I have an UPDATE query that is always setting 0 records. When I cut and paste the SQL into Access and use it, it fails in the same way unless I coerce the date fields to be '=now()', in which case...
5
by: Anja | last post by:
Hi everyone, I want to write a simple SQL statement that does a comparison on a date field. For a simple test, I have the following SQL Statement: SELECT * FROM Records_T where...
8
by: Ragbrai | last post by:
Howdy All, I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains...
9
by: dee | last post by:
I'd like to filter by the following criteria: left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767 I have no idea how to do this. Appreciate help.
2
by: jennwilson | last post by:
I am trying to generate a report based on a query that will list any records where an individual has a date listed that matches the specified time for one or both of the date fields. The two fields...
3
by: dianatokatlidis | last post by:
Hello... I have a big dilemna and I've been struggling for days how to do it I have 2 date fields. StartDate and EndDate. I am trying to get all records where the StartDate is Oct-2007 or...
2
by: KMEscherich | last post by:
Microsoft Access 2003 Hi there, am stuck with something that I am not sure on how to get done. I am attempting to have 3 check boxes and have 3 date fields. I need to have each date field be...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.