Source language: Translate to:

Searching between dates

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Postby Gaev » Sat Nov 10, 2007 10:40 am

smartmedia:

Found this ....

http://forums.devarticles.com/microsoft ... -9587.html ... where they say ...
Access doesn't use date fields, or time fields for that matter. It uses date/time fields. When you enter a date without a time, Access is reading it as mm/dd/yyyy 00:00:00, regardless of how the field is formatted.

I have not found a way around this,


... and this ...

http://en.allexperts.com/q/Using-MS-Acc ... ameter.htm
Just a point on how dates are stored. If you use a Date/Time datatype (as I assume you are), then what's actual stored is a serial number counting the number of days since 30/12/1899. How that value is displayed is determined by your Windows Regional settings. The problem is, when using criteria, Access expects US notation (mm/dd/yy) on date strings.


... and this ...

http://www.dbforums.com/showthread.php? ... did=492931
Dates in an SQL statement MUST always be in the format #mm/dd/yyyy#.

When you view dates in a tables thet are presented to you in the format you have defines Regional and Language Options in Control Panel. With Access they are always stored in mm/dd/yyyy format, so that is how you must specify them in SQL.


... and this ...

http://visualbasic.ittoolbox.com/groups ... ing-394382
For some reason, you have to pass dates into access sql in the format "mm/dd/yyyy". If you pass dates in any other format, you will have prloblems.


More such information if you Google with "access query date strings"
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Sat Nov 10, 2007 1:27 pm

Hi...

Geav, after reading very carefully all and play with it i finally came up with the following conclusions.

1) Even if the dbpSetFieldProperties is fixed to represent the date as dd/mm/yyyy the search not working.

2) Dosen't matter if the Date field is defined as Date or DateTime, in both cases search is working when 3) is true.

3) Searching is working only if the start and end dates are in mm/dd/yyyy format.


So i think that those information must added to help file with big letters, preventing others to forget slip for 24 hours.

Your help as always is important to me.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Sat Nov 10, 2007 2:02 pm

smartmedia:

Even if the dbpSetFieldProperties is fixed to represent the date as dd/mm/yyyy the search not working.
... dbpSetFieldproperties is used to tell NeoBook to take values returned by the MS Access driver ... and make them "pretty" ... when you construct your query, this information is not relevant ... the data between the # wrappers is passed to MS Access AS IS ... it is only when MS Access returns a matching record set ... that NeoBook formats it according to instructions in the dbpSetFieldPropperties command.

Dosen't matter if the Date field is defined as Date or DateTime, in both cases search is working when 3) is true.
... may work when field is defined as Date ... but should NOT be used with MS Access databases ... other problems may be encountered.

Searching is working only if the start and end dates are in mm/dd/yyyy format.
... a simple way to convert Entered Dates to Query Dates is ...
Code: Select all
DateToNum "[StartDate]" "Default" "[DateNum]"
NumToDate "[DateNum]" "mm/dd/yyyy" "[StartQueryDate]"
DateToNum "[EndDate]" "Default" "[DateNum]"
NumToDate "[DateNum]" "mm/dd/yyyy" "[EndQueryDate]"
dbpQuery "Search" "Search" "Date1 BETWEEN #[StartQueryDate]# AND #[EndQueryDate]#"
... of course, you still need to make sure user has entered a valid date ... entering 29/02/2007 or 35/03/2004 or 21/16/2001 will produce unexpected results ... and no computer can read a user's mind ... so 6/9/2003 (where Windows regional setting for date is dd/mm/yyyy) will be 6th of September and NOT 9th of June.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Sun Nov 11, 2007 7:13 am

Hi...

Gaev, i use Hans (freeware and not open source) hpwUtility wich is very nice for that job. Thanks again...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Previous

Return to NeoBookDBPro

Who is online

Users browsing this forum: Bing [Bot] and 1 guest