Date Query NeoBookDBPro - Forum

Forum Navigation
newLisp Contest!, take the SECOND CHALLENGE and win a free registration key for any VisualNEO software or plugin.
Click here to participate
and good luck!
You need to log in to create posts and topics.

Date Query NeoBookDBPro

I'm having a bit of trouble with doing a dbpQuery BETWEEN dates in an MDB file. Of course the Access date field includes a time which in my search is irrelevant. My goal is to get a dbpSum of a particular field in all records between a start date and an end date. Here's what I've started with:

dbpQuery "poursch" "CY" "pourdate BETWEEN [#34][CYstart]%[#34] AND [#34][CYend]%[#34]"

I receive a "Data type mismatch" error with the quotes[#34] and a syntax error without the quotes[#34].
I've tried various codes including DateToNum to make sure my date range is valid. I'm querying the start date to get the first record and it's record number (in a field), querying again to get the last record of the end date, and then doing a query BETWEEN record numbers but even that gives me extra records. For instance, my search criteria requires [recid]=15801 but my query brings back 1580 and 158. Which leads me to another question: how can I define an integer variable with leading zeros?

Basically, how do I dbpQuery BETWEEN dates? Any suggestions?

A mi me funciona así:

dbpQuery "DB" "TABLE" "Fecha BETWEEN DateValue('[fecha1]') AND DateValue('[fecha2]')"

 

Thank you danito. This almost works. It does give me my date range except for the last recorded date. For example, if my date range is 7/29/2019 - 8/1/2019 it returns 7/29/2019 - 7/31/2019. I guess I'll have to adjust the end date programmatically one day ahead? That will take some testing to check for consistency.

@pauljonestindall

if my date range is 7/29/2019 - 8/1/2019 it returns 7/29/2019 - 7/31/2019

This is because DateValue assumes a time of 00:00:00 (midnight).

So, in your case, it only checks up to 8/1/2019 00:00:00 ... if you have records for (say) 8/1/2019 08:09:35, they will be considered outside the range.

the Access date field includes a time which in my search is irrelevant
I guess I'll have to adjust the end date programmatically one day ahead? That will take some testing to check for consistency.

If you need assistance, please post the exact date and time value (text string) that MS Access returns for [fetcha2]

@pauljonestindall

Some thing you might try to add one day to a date field in MS Access ...

dbpQuery "DB" "TABLE" "Fecha BETWEEN DateValue('[fecha1]') AND DateAdd(d,1,DateValue('[fecha2]'))"

... for more information on ...

DateAdd(interval, number, date)

... refer to this page on w3schools website