Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filtered Date Range in Query

Posted on 2013-01-27
40
Medium Priority
?
360 Views
Last Modified: 2013-02-12
I have a form that allows the user to choose s start date and an end date and it returns the dates (via two global date variables) to build a filter string (strReportFilter) such as
"int([IN1])>=#2013/01/01# AND int([IN1])<=#2013/01/27#"
with the [IN1] parameter being a query field (date/time - general format).

This is used as a filter parameter for a report call such as...
DoCmd.OpenReport "ReportName", acViewPreview, , strReportFilter

This works just fine when the regional setting for dates is customized as a format of "yyyy/MM/dd" but does not work if another date format is in effect due to the regional settings. So, for the next example, the following formats do not work as an empty data set is returned instead of the records for the date range.

int([IN1])>=#12/12/01# AND int([IN1])<=#12/12/31#
int([IN1])>=#12-12-01# AND int([IN1])<=#12-12-31#

Now, since [IN1] is in 'general' date format, why is this happening? Both should be in the same format for comparison and there should be no problem.

When the system date format IS yyyy/MM/dd all is fine.
0
Comment
Question by:DanielT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 23
  • 9
  • 7
  • +1
40 Comments
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825634
First, you shouldn't be using the int() function here,  maybe cdate() but definitely not int().  That may fix all your problems.  If you have hardcoded dates like #1/27/13# then they should respect the system setting.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38825863
An expression like:

"[IN1] >= #2013/01/01# AND [IN1] < #2013/01/28#"

will work in any case.

/gustav
0
 
LVL 2

Author Comment

by:DanielT
ID: 38828778
Thanks for the comments!

The INT function was used to strip off the time portion of the date so that, instead of
41302.64665 representing 2013/01/28 at 15:31, I would simply have 41302.00000 or 2013/01/28 at 00:00. Also cdate(x) would take a text parameter and IN1 is a date/time field so I'm sure this would generate an error.

But the code you mentioned in the last post may work without the INT function... I just expected there would be a problem when it compares with a time such as comparing 2013/01/28 at 15:31 against 2013/01/28 at 00:00 using the <= operator. Instead of including the 2013/01/28 date it would be excluded because 15:31 is greater than 00:00.

The dates are not really hardcoded per-se; at least not until the filter string is built. They are derived from a form that passes the parameters back via two global date variables. These global date variables expand to create the filter string according to the system date setting and should match the format automatically. This is why I expected all to be OK.

There is one potential oddity that I have not looked at further. There are two text boxes on the form that use the yyyy/mm/dd format. I have not considered this though because those values are assigned to the global date variables and they return the dates as expected but converted to the system specified format which tells me all should be OK there. So - if system date format was yy-MM-dd an entry of 2013/01/28 in the text box it would be returned as 13-01-28.

Any change in your comments/opinion with the additional info? :))
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38828988
It's the actual value of the textbox that is used, not the formatted and displayed value/string.

/gustav
0
 
LVL 2

Author Comment

by:DanielT
ID: 38829236
Hmmm... I'll look further into that - in case I'm missing something - but the selection form simply gives this textbox option to the user for direct entry if they desire. It is also used to show to selections made, if done via calendar controls.

The date is passed (as mentioned) via a global date variable which - as a true date - represents the date (I thought) according to the system format. This was not just guessing but - when inserting a break in code, the format of the returned date was as per Windows regional setting.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38829623
Well, I've had a further look and have tracked the variables through. All is OK for the Windows regional setting except this. Regardless of the system setting, the database stores 4 digit years.

When the system setting includes a 4 digit year (whether yyyy/MM/dd or yyyy-MM-dd) all is workable but when the system setting only specs a 2 digit year it fails. I have not tried an alternative format yet such as MM/dd/yyyy.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38830266
> Regardless of the system setting, the database stores 4 digit years.

No, it stores date values. These carry no format.

> The date is passed (as mentioned) via a global date variable which - as a true date -
> represents the date (I thought) according to the system format.

No, it represents the date value. Formats are for display only.

> This was not just guessing but - when inserting a break in code, the format of
> the returned date was as per Windows regional setting.

Of course, Access has to display the date for you as a string and will for this use the date format from your Windows settings.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38830943
BTW, you can use the DateValue() function to return the date part of a date/time field.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38831228
fyed,
Thanks. Did not know that DateValue would accept a date/time parameter until you mentioned this and I tried it. Expected the parameter had to be a string.

cactus_data
You know from earlier posts that I know this, right? See back a little earlier when I advised on Jan 28 16:28 of the actual date/time values. I was just looking for reasons why this is occurring and was comparing how Access was 'displaying' in case there was something unexpected that could cause a mismatch in the table date vs filter date.

What you have mentioned is why this is/was a puzzle. If a date/time field or variable uses the system setting for format, why would I have an empty record set when the date format is "yy/MM/dd" vs "yyyy/MM/dd"? The filter string built is ultimately based on the system setting and should be the same as how the table or query values are seen.

There should be no incompatibility unless the filter mechanism itself is mis-handling by not recognizing that "13/01/29" is the same as "2013/01/29" - Hmmm - I may have just ANSWERED BY OWN QUESTION! - - The system would not know what to do with this unless it defaulted to the current century and that may not be what was intended so, perhaps, the filter string must be expanded to be a full four digit year or the filter result is ambiguous!?

NEW - related question...
I have not checked yet, myself but do any of you following this know how to determine the current Windows date format? I suspect I'd need to code a translation if a short year is set so that it simply expands to a 4 digit year and I'd need this info to know if a conversion was required.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38831322
OK, I thought we had left the 2/4-digit year question.

But you could use DateValue to convert your date string. It will try to read the string according to the Windows settings.

There is no way to know for sure which date, say, "06/07/08" represents while "1906/07/08" leaves no doubt.

> perhaps, the filter string must be expanded to be a full four digit year or
> the filter result is ambiguous!?

Yes.

/gustav
0
 
LVL 2

Author Comment

by:DanielT
ID: 38832780
Appreciate your comments, of course!!

But what did I miss that we would have "left the 2/4 digit year question"? So far that is all that I can see that is causing the issue for reasons above.

The problem with DateValue is that it does not have a parameter to set a specific date format that is independent of the system date format. Therefore if the system date is set to yy/mm/dd (forgive the non-cap MM's) then DateValue will use that format - which is giving problems as a filter parameter.

It seems that what I need to do is to check windows regional date setting and convert parameter to a 4 digit year if it is not already. Do you agree? (or - again - did I miss something).

:))
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38833239
I think what's happening here is that Access wants dates represented in the US format when using the # notation and beyond that I'd recommend always putting a 4 digit year in your queries when specifying the date.

So always be using #mm/dd/yyyy# in your queries like #12/31/2012# and it should work in all regions.  

I wasn't aware of this behavior and personally assumed that it would just using whatever the localized settings were, but from a quick browse of the internet that seems to not be the case.

Of course you can have user's input and you can format output of dates however you want, this is an issue with queries only.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833290
kmslogic

Thanks.
I am also thinking this must be the case and is the solution. I want this 'form' to fully handle different system date settings though so I can use it as a module in other projects reliably.

It's funny, as I only ran across this during a computer change (a failed m/b) when English - Canada was set as region on the new system and the date format was set to the short version of 'yy-MM-dd' to replace 'dd-MM-yy' which I personally, really dislike. Then the app started to misbehave, indicating no records were available for ranges where there was actually data.

Now - how can I reliably control the date formatting and expand the year from 2 to 4 digits so I can build the filter string consistently? I know there is the VbDateTimeFormat function but it is limited to specific date formats as it is still tied to the Windows Regional settings.

Maybe simply the year() function - I have not tested it (yet) to see if it always returns 4 digits or also conforms to regional format.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833301
Quick update... The Year() function seems to reliably return 4 digits.

I think the solution is the build the filter string using the year() function. If I embed it in code that builds the statement it should reliably create a filter string that will not fail... back to it...
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38833305
I just use format(datField, "mm/dd/yyyy")
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833311
I learned something above in regard to this but does the FORMAT function not require a string variable vs date? Will it auto-convert?

Otherwise I should indeed be able to use FORMAT(datField, "yyyy/MM/dd")...
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38833326
Format doesn't require a string variable.  Not just dates but various numeric types etc. can be used.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833333
My bad... Thought it was for string data (and didn't double check).
Will see what happens.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833342
Interesting...

This variable and resultant data...
? gdtmDateSelected_From
12-12-01

Outputs as this...
? Format(gdtmDateSelected_From, "yyyy/mm/dd")
2012-12-01

instead of what I expected as 2012/12/01
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833362
Interesting

When I manually insert filter code as...
strReportFilter = "int([IN1])>=#2012/12/01# AND int([IN1])<=#2012/12/31#"
All is OK.

But the 'normal, revised' code of...
strReportFilter = "int([IN1])>=#" & Format(gdtmDateSelected_From, "yyyy/MM/dd") & "# AND int([IN1])<=#" & Format(gdtmDateSelected_To, "yyyy/MM/dd") & "#"

This 'resolves' to ...
strReportFilter = "int([IN1])>=#2012-12-01# AND int([IN1])<=#2012-12-31#"
But does NOT work!
(even though delimiter is correctly specified as "/")

So the format() function is allowing the year expansion to 4 digits for consistency but overrides the specified delimiter??
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833365
BTW. This is in Access 2003.
Not sure of Access 2007/2010!
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38833382
Well I've never seen that happen and it didn't happen to me just now when I tested it on Access 2013.   That seems like pretty strange behavior.

So just roll your own function

Public Function FormatDate(ByVal pdat As Date) As String
    FormatDate = "#" & Year(pdat) & "/" & Format(Month(pdat), "00") & "/" & Format(Day(pdat), "00") & "#"
End Function

Open in new window

0
 
LVL 2

Author Comment

by:DanielT
ID: 38833404
Yep!
Wierd.

As you can probably gather, the values pasted in a couple of posts back
(specifically - Posted on 2013-01-29 at 18:54:12)
were directly from the immediate mode so this is not happending just for running code.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38833542
Guess I will need to see if I can check this out on another Access 2003 system before I assume it is Access. Will try and do that as soon as I can...
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1400 total points
ID: 38834340
> The problem with DateValue is that it does not have a parameter to set a
> specific date format that is independent of the system date format

That's not a "problem" with DateValue. The whole purpose of DateValue is to read a string expression for a date as to the actual Windows settings.

> It seems that what I need to do is to check windows regional date setting and
> convert parameter to a 4 digit year if it is not already.

Not quite sure what you mean. If you wish to adjust the Windows settings, that is unreliable as the user may not have the rights to perform this action.

> Outputs as this...
> ? Format(gdtmDateSelected_From, "yyyy/mm/dd")
> 2012-12-01
>
> instead of what I expected as 2012/12/01

Nothing weird here. That's because here / is not a slash but a placeholder for the date separator as to your Windows settings where it is a dash: -.
To gain control, use a backslash \ which is the escape character:

  strDateSelected_From = Format(gdtmDateSelected_From, "yyyy\/mm\/dd")

or, if you like, to build the full date expression in one go:

  strDateSelected_From = Format(gdtmDateSelected_From, "\#yyyy\/mm\/dd\#")

The yyyy-mm-dd sequence is often called - though not completely correct - the ISO format because it follows the international standard, and it is the only one that will never fail in Access as it is mandatory for ADO and its Find methods.

/gustav
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38834406
The yyyy-mm-dd sequence is often called - though not completely correct - the ISO format because it follows the international standard, and it is the only one that will never fail in Access as it is mandatory for ADO and its Find methods.

The format #2013-03-01# works for me as far as performing date comparisons in a query, so I wonder why it didn't work for him.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38834776
It also works for me.
But why bother? If the slash is used, it is guaranteed to work.

/gustav
0
 
LVL 2

Author Comment

by:DanielT
ID: 38835491
Hey cactus (for short) :)

Thanks for sticking with this. You misunderstand on the first point. It IS problem for this thread, I did not say it is not working as it should. The fact that it uses the system format is the problem.

When this started my head was not even in the format() function place as I had it in my head that it only took string parameters but, as a function parameter, I did not expect "/" to be a delimiter. Checked further and I don't see it.

This is not an official MS post but I doubt that it is wrong... Example
http://www.techonthenet.com/access/functions/date/format.php 

At this point, I am still thinking there may be something quirky with my PC that is complicating things. Seems Kmslogic is on the right track.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38835494
Oh... BTW - I will still give the delim thing a shot!
0
 
LVL 16

Assisted Solution

by:kmslogic
kmslogic earned 600 total points
ID: 38835524
@Daniel -- I'm pretty sure either cactus'

Format(gdtmDateSelected_From, "\#yyyy\/mm\/dd\#")

or using the simple function I posted above will solve the problem for you--as to exactly why your system isn't liking the format of #YYYY-MM-DD# which it seems like it should I don't think we know.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38835561
Can confirm the delimiters did create desired filter format!
Need to check further though when I'm back on network for results.
Will advise.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38835598
Also - some info here...
http://office.microsoft.com/en-ca/access-help/format-function-HA001228839.aspx

Very close to the bottom of the page it states the following which states that delimiters are output according to system settings. SO, I expect that we are forcing the format by using "\/" within the format string so that this 'feature' does NOT occur.

Text Extract...
##
Use the Format function in VBA code    

 NOTE   Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

This example shows various uses of the Format function to format values by using both named formats and user-defined formats. For the date separator (/), time separator (:), and AM/ PM literal, the actual formatted output displayed by your system depends on the locale settings of the computer on which the code is running. When times and dates are displayed in the development environment, the short time format and short date format of the code locale are used. When displayed by running code, the short time format and short date format of the system locale are used, which may differ from the code locale. For this example, the U.S. English locale is assumed.
##
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38835854
Well, I know all this, that's why I advised you as I did.

> The fact that it uses the system format is the problem.
It has to use _a_ format for displaying dates. If none specified, which other format should be used if not that from the Windows settings? Seems quite logical, I think.

/gustav
0
 
LVL 2

Author Comment

by:DanielT
ID: 38835914
With all due respect, it is not helpful to restate a fact that has already been established in the thread as a new revelation. The delimiter feedback was, however, great! Thanks!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38835940
Sorry, didn't intend to offense you with my knowledge.

/gustav
0
 
LVL 2

Author Comment

by:DanielT
ID: 38836003
Actually - no offense taken. ;)
0
 
LVL 2

Author Comment

by:DanielT
ID: 38836530
Well - another update - and Recap.
(Sorry but this does seem odd to me)

I believe the original code should really have worked but for some reason the system date format is not fully honoured in the filter parameter for the query. All dates in the original code would (or should be) relying on that format but it did not work. I know this part is not isolated to just my 2003 dev machine. Keep in mind again (in case newer releases handle correctly) this is in Access 2003.

What has resolved this issue is a combination of using the format() function along with the delimiter (technically, an escape character) of "\" to force the format function to always return a date in the format of 2013/01/30 and not some other 'system' affected format.

So...
for ALL of the short date formats in the attached capture image,
the following code works and returns all the records expected...

strReportFilter = "int([IN1])>=#" & Format(gdtmDateSelected_From, "yyyy\/MM\/dd") & "# AND int([IN1])<=#" & Format(gdtmDateSelected_To, "yyyy\/MM\/dd") & "#"

But these code snippets do NOT work, returning an empty data set dependent on the system regional date format!

strReportFilter = "int([IN1])>=#" & gdtmDateSelected_From & "# AND int([IN1])<=#" & gdtmDateSelected_To & "#"

strReportFilter = "int([IN1])>=#" & Format(gdtmDateSelected_From, "yyyy/MM/dd") & "# AND int([IN1])<=#" & Format(gdtmDateSelected_To, "yyyy/MM/dd") & "#"

Frankly, I thought that using the format function to built the filter would  mess up for other system date formats that were not similar but, it works for all. Go figure!

If you're inclined to say "of course it would, they're all dates" - then the original code should have worked as it was using unmodified system format dates throughout!


Anyway - it works as stated above - and that was the objective!! :))
C-2013-0130-1325-06-HL.jpg
0
 
LVL 2

Author Comment

by:DanielT
ID: 38836588
Rats. Hang on!!

Still had a hard-coded segment embedded in there for the filter...   :((
Need to do some re-checks.
0
 
LVL 2

Author Comment

by:DanielT
ID: 38836633
Whew!
All still good as posted.

What is consistent with the above code is that the date is always passed to the query in the format of "yyyy/MM/dd" regardless of the Windows system date setting. And this works!!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 38837173
Great. Thanks for the feedback.

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

721 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