Source language: Translate to:

Late records Query

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Late records Query

Postby stu » Sun Apr 27, 2014 9:11 am

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.
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Late records Query

Postby petski » Sun Apr 27, 2014 7:29 pm

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));
petski
 
Posts: 11
Joined: Tue Oct 25, 2011 7:15 pm

Re: Late records Query

Postby stu » Mon Apr 28, 2014 11:27 am

Hello! thanks for your answer, unfortunately im using mysql so i cant manage to make it work... Im still looking for a solution, thanks.
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Late records Query

Postby Neosoft Support » Tue Apr 29, 2014 9:37 am

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
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Late records Query

Postby stu » Tue Apr 29, 2014 12:36 pm

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.
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Late records Query

Postby Neosoft Support » Wed Apr 30, 2014 10:11 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Late records Query

Postby stu » Wed Apr 30, 2014 12:49 pm

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!
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Late records Query

Postby DaveJ » Wed Apr 30, 2014 12:59 pm

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
DaveJ
 
Posts: 90
Joined: Tue Dec 30, 2008 11:06 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest