Solved

Filtered Date Range in Query

Posted on 2013-01-27
40
352 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
  • 23
  • 9
  • 7
  • +1
40 Comments
 
LVL 16

Expert Comment

by:kmslogic
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
> 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 47

Expert Comment

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

Author Comment

by:DanielT
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I just use format(datField, "mm/dd/yyyy")
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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
Comment Utility
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
Comment Utility
My bad... Thought it was for string data (and didn't double check).
Will see what happens.
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 2

Author Comment

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

Expert Comment

by:kmslogic
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 350 total points
Comment Utility
> 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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
Comment Utility
Oh... BTW - I will still give the delim thing a shot!
0
 
LVL 16

Assisted Solution

by:kmslogic
kmslogic earned 150 total points
Comment Utility
@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
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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 49

Expert Comment

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

/gustav
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
Actually - no offense taken. ;)
0
 
LVL 2

Author Comment

by:DanielT
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
Great. Thanks for the feedback.

/gustav
0

Featured Post

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

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

771 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

7 Experts available now in Live!

Get 1:1 Help Now