Page 1 of 1

Late records Query

PostPosted: Sun Apr 27, 2014 9:11 am
by stu
Im having some trouble trying to generate a query that search for "late" records... Im making a program to manage rentals of certain items... so I have 3 dates in a "order detail" table (DATE RENTED, DATE TO BE RETURNED, DATE RETURNED)

so the dates are stored in format dd-mm-yyyy

and I want to generate a query that shows only the items that are late

eg:

Item 1 - taken: 01-04-2014 - should return: 10-04-2014 - returned: NULL
Item 2 - taken: 10-04-2014 - should return: 30-04-2014 - returned: 29-04-2014

so you see im looking for a way to only show record one, since is late....

problem i think is that I cannot use < or > or = or <> since the dates are not numeric fields and it would take far too much to make NUMTODATE for every field every time I execute the software...

So does anybody have any idea about how to do this?

Greetings.

Re: Late records Query

PostPosted: Sun Apr 27, 2014 7:29 pm
by petski
If you are using Microsoft Access as you database you should be able to use the following:
Advanced SQL - dbpExecSQL

Simply query where Retun Date is less then current calendar date and the item returned date field is Null

SELECT Table1.CustName, Table1.Returndate, Table1.DatetoReturn, Table1.ReturnedDate
FROM Table1
WHERE (((Table1.DateToReturn)<Now()) AND ((Table1.ReturnedDate) Is Null));

Re: Late records Query

PostPosted: Mon Apr 28, 2014 11:27 am
by stu
Hello! thanks for your answer, unfortunately im using mysql so i cant manage to make it work... Im still looking for a solution, thanks.

Re: Late records Query

PostPosted: Tue Apr 29, 2014 9:37 am
by Neosoft Support
Here's a discussion about searching date fields that might be helpful:

http://www.neosoftware.com/community/viewtopic.php?f=21&t=18013&p=11199149&hilit=query+dates#p11199149

Re: Late records Query

PostPosted: Tue Apr 29, 2014 12:36 pm
by stu
Neosoft Support wrote:Here's a discussion about searching date fields that might be helpful:

http://www.neosoftware.com/community/viewtopic.php?f=21&t=18013&p=11199149&hilit=query+dates#p11199149


mmm, this refers to the use of access database, is there a way of working my way around mysql?

cheers.

Re: Late records Query

PostPosted: Wed Apr 30, 2014 10:11 am
by Neosoft Support
The code used in the examples is SQL so it should be similar for mySql. DBPro's dbpExecSQL action simply passes the SQL code on to the database engine which executes it. In most cases, however it's done in mySql should also work with dbpExecSQL.

Re: Late records Query

PostPosted: Wed Apr 30, 2014 12:49 pm
by stu
Neosoft Support wrote:The code used in the examples is SQL so it should be similar for mySql. DBPro's dbpExecSQL action simply passes the SQL code on to the database engine which executes it. In most cases, however it's done in mySql should also work with dbpExecSQL.


Got it, thanks a lot!

Re: Late records Query

PostPosted: Wed Apr 30, 2014 12:59 pm
by DaveJ
Hi Stu,

I use a mysql database and use the following statement to select records between two dates.
dbpExecSQL "mydb" "SELECT * FROM parts |WHERE job_no=[#34][job][#34] AND created BETWEEN [#34][Date1] 00:00:00[#34] AND [#34][Date2] 23:59:59[#34];" ""

Please note that Mysql uses a date format of YYYY/MM/DD

I use this statement to find parts for a particular job that have been booked out between two dates.

Any help?

Dave J