Source language: Translate to:

Searching between Dates

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Searching between Dates

Postby Carl_SA » Fri Aug 13, 2010 2:09 am

I am trying to search between dates using the dbpexecsql command. The syntax is relatively simple but I just dont seem to get results. The syntax I am using is:
dbpExecSQL "adsql" "SELECT * FROM responses WHERE cap_date |BETWEEN '[sdat]' AND '[edat]'" ""

The cap_date field is a date field. I have also tried to search using a field which is a normal character field but which has the date values correctly formatted. All to no avail.

Any suggestions please
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Gaev » Fri Aug 13, 2010 4:32 am

Carl:

Read this forum post for discussion on same topic.

http://www.neosoftware.com/forum/viewtopic.php?t=17264
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Fri Aug 13, 2010 4:55 am

Hi..

If you work with Access database you have to remember that Access accepts Dates only in US format which is MM-DD-YYYY.
One other think you must remember is that Access accepts the Dates only when surrounded by #. Example #12/31/2010#

The folowing example query search between 2 Dates.

Code: Select all
dbpExecSQL "Data" "SELECT * FROM ProgAdion WHERE Status = '[ComboBox20]' AND (Startdate>=#[SearchStartDate]# AND EndDate<=#[SearchEndDate]#)" ""


Also you can try that.

Code: Select all
dbpQuery "DatabaseID" "Table" "StartDate Between #[firstdate1]# AND #[seconddate1]#"


If is difficult for you to use Dates fields you can you use integer field and use DateToNum and NumToDate NB commands prior to convert the dates and then search between numbers.

Hope i help you...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Carl_SA » Fri Aug 13, 2010 7:00 am

Thanks very much for the input on this.
I am going to go with the idea where I create an additional column which represents the date in the datetonum format.
Great insight once again!
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests