Source language: Translate to:

Searching between dates

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Searching between dates

Postby Carl_SA » Mon Oct 15, 2007 5:27 am

I have a field in an Access database which is defined as a date/time field in Access. When using the dbpQuery command I am unable to sort the records between 2 dates. I have tried the different commands and can't seem to make things works. The query keeps returning no records. The date field in Access is stored in the following way: yyyy/mm/dd. The code I am using is:
dbpQuery "database id" "table" "strCaptain = [#34][pilot_names][#34] and datDate >= [start_date] and datDate <= [end_date]"
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Neosoft Support » Mon Oct 15, 2007 3:29 pm

You might need to tell DBPro how the date field is formatted because Access doesn't actually store that information as part of the table. For example:

dbpSetFieldProperties "database id" "table" "datDate" " yyyy/mm/dd"

Then perform your query normally.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Carl_SA » Mon Oct 15, 2007 8:58 pm

Have tried this but it does not work. I also tried to get it to work by changing the Access field definition to a "text" definition. Any more suggestions?
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby beno » Mon Oct 15, 2007 9:29 pm

Hi Carl_SA,

You can add a integer field to your database and store the date in a numeric format. See DateToNum and NumToDate.

Not the best idea, but this avoids lots of head aches and makes dealing with dates very easy.

My cent,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Carl_SA » Mon Oct 15, 2007 10:41 pm

Hi Beno,

Looks like this could work. Thanks for the response on this
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Carl_SA » Mon Oct 15, 2007 10:53 pm

Hi Beno,

Works great. Thanks!
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Neosoft Support » Tue Oct 16, 2007 11:16 am

OK, the solution for querying date fields is to surround the date with # characters. For example:

Code: Select all
dbpQuery "database id" "table" "datDate >= #[start_date]# and datDate <= #[end_date]#"


or

Code: Select all
dbpQuery "database id" "table" "datDate between #[start_date]# and #[end_date]#"


Without the # characters, the SQL engine processes the date as a mathematical formula. I don't know if this is a quirk of MS Access alone or also applies to other DBs.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Carl_SA » Tue Oct 16, 2007 9:01 pm

Works perfectly. Thanks for this
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby smartmedia » Fri Nov 09, 2007 9:29 am

Hi...

Well seems that is not working for me...
I have create a sample pub to demonstrate that i am right...
The database i have create is MS access and has 1 date field.

The coomand i use is:
Code: Select all
dbpQuery "Search" "Search" "Date1 BETWEEN #[StartDate]# AND #[EndDate]#"


Try to give as start date 5/9/2007 and as End date 8/10/2007
The result is error.
If you try to give as start date 5/9/2007 and as End date 9/10/2007
The result is right.

Strange isn't ...???

Please any solution that will work in any dates you enter to search will be welcome.

http://rapidshare.com/files/68546096/Search.rar.html
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Fri Nov 09, 2007 10:05 am

smartmedia:

a) How do the variables [StartDate] and [EndDate] get populated ? ... via a SetVar command ? ... or are these associated with TextEntry Box objects ? ... other ?

If you are using the SetVar command, you might want to make sure you have the ! before the date values ... in any case, check the Debug window to see what NeoBook considers to be the value of these variables ... or insert an AlertBox command just before the dbpQuery command.


b) What is the default date format on your machine ? ... m/d/y or d/m/y ?

c) Also, does your database contain records that are in the range 5/9/2007 and 8/10/2007 ?
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Fri Nov 09, 2007 12:02 pm

Hi...

Gaev, i don't know if you have download the pub to see for your self...???
In the example i use TextEntry.
My default format is dd/mm/yyyy
Yes, see the example.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Fri Nov 09, 2007 2:00 pm

smartmedia:
Gaev, i don't know if you have download the pub to see for your self...???
... if I recall, .rar packages need a proprietary program to extract the contents ... perhaps you could use the more universally available .zip packaging scheme.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Carl_SA » Fri Nov 09, 2007 10:59 pm

I have posted the relevant code from my application which works perfectly:

datDate BETWEEN #[start_date]# and #[end_date]#"

I also do a calculation to make sure that the end date is not before the start date. I use datetonum for this
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby smartmedia » Sat Nov 10, 2007 12:26 am

Hi...

Gaev, sorry but i use WinRaR the last 6 years as millions and millions do, after all is better on every aspect, but if you like i can upload it as zip for you.

Carl_SA, Please try to play with the example i make and you will see that there is a problem with some dates...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Sat Nov 10, 2007 6:02 am

smartmedia:

a) The error message is not coming from the ...

dbpQuery "Search" "Search" "Date1 BETWEEN #[StartDate]# AND #[EndDate]#"

... command but from the ...

dbpFieldToVar "Search" "Search" "Date1" "[FoundList]" "Delimiter=[#13];SkipBlanks=Yes;NoDuplicates=Yes"

... command following it ... and it says (in the message) that the database is empty.

b) This means that the previous dbpQuery command resulted in no matching records ... dbpFieldToVar works on the current set of records ... when there are no records in the current set, you get an error message when you try to export field contents.

c) So, the question now becomes ... why does the dbpQuery command not find records in some of the date ranges you specified ... trying out a few more combinations ... and the sample pub made this task a lot easier (thanks for taking the time) ... I found that the command is treating the values between the # delimiters as m/d/y ... and when you specify start date 5/9/2007 and as End date 8/10/2007, it is doing a search for records between May 9th and August 10th ... since your sample database has records ...

dd/mm/yyyy

06/09/2007 meaning September 6th
07/10/2007 meaning october 7th
15/09/2007 meaning September 15th
20/09/2007 meaning September 20th
21/10/2007 meaning October 21st
22/10/2007 meaning October 22nd

... then you get 0 (zero) matches ... but when you specify start date 5/9/2007 and as End date 9/10/2007, it is doing a search for records between May 9th and September 10th ... and you get 1 match.


I don't know why it is treating them as m/d/y format ... but possibilities include ...

a) What command syntax did you use to create the table fields ? ... specifically the field called "date1" ... note that the NeoBook Help file says that ...
Date. This type is not supported by MS Access, use DateTime instead.**

**Microsoft Access does not distinguish between date and time fields internally. In order to display date and time values correctly, you must manually define the field's appearance using the DisplayFormat property of the dbpSetFieldProperties action.


b) It could be that either MS Access ...

- only accepts dates (i.e. stuff between the "#" delimiters) in m/d/y format
- only accepts dates (i.e. stuff between the "#" delimiters) in the format specified in your Windows configuration
- has some other command to specify what format to use

... this needs to be researched further ... in the mean time, you could have a simple routine that converts from dd/mm/yyyy to mm/dd/yyyy ... and pass the converted values to the dbpQuery command.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest