SUM between two dates

[danilo]

I’ve just spent a couple of hours on what should be a simple query and require some help.
What I am trying to do is calculate the pay of a person between two dates using an Access database and PHP.

Table name: Job
Field names: JOBPAY, STATUS, EMPLOYEEID, DATE

My current query looks like this and is returning a null value even though dates in between this period appear in the database field DATE.

<?php
$db_connection = OpenDB(); //function to connect to database.

$sum= "SELECT SUM(JOBPAY) FROM Job Where STATUS='Closed' and EMPLOYEEID='Jack' and [DATE] BETWEEN 01/10/2008 AND 30/10/2008";

$result = odbc_exec($db_connection,$sum);
odbc_fetch_into($result, $rows);
$open = $rows[0];

odbc_close_all();

When I remove the date related queries I get a result but with the date related queries I get nothing.

Any help would be greatly appreciated.

 

[pbaldy]

I don't work much with PHP, but Access typically likes dates surrounded by #. Try

$sum= "SELECT SUM(JOBPAY) FROM Job Where STATUS='Closed' and EMPLOYEEID='Jack' and [DATE] BETWEEN #01/10/2008# AND #30/10/2008#";

 

[namliam]

Also note that Access requires dates in SQL to be in US format...
I.e. your 01/10 will be interperted as January 10, not October 1.

Also while I am here anyway... you shouldnt use access reserved words (DATE) as a column name, instead use something like jobdate...

Good hunting!

 

[danilo]

Thank you so much guys now i can finally get this assignment out of the way. Turns out access really does like its dates around #, thanks for that pbaldy.

 

[pbaldy]

No problem; welcome to the site by the way.

 

[danilo]

Thanks again, only wish i came here sooner, would have saved my self many many hours.

 

[Moses76]

I don't work much with PHP, but Access typically likes dates surrounded by #. Try

$sum= "SELECT SUM(JOBPAY) FROM Job Where STATUS='Closed' and EMPLOYEEID='Jack' and [DATE] BETWEEN #01/10/2008# AND #30/10/2008#";

Can I do this using forms with dates in the drop downs ? Please please help.

Thanks.

 

[pbaldy]

If you're using a query, you just refer to the form controls and you don't need the delimiter:

Between Forms!FormName.ControlName And...

In VBA, you concatenate the value and include the delimiter:

strSQL = "SELECT...Between #" & Forms!FormName.ControlName & "# And #"...

 

[Moses76]

If you're using a query, you just refer to the form controls and you don't need the delimiter:

Between Forms!FormName.ControlName And...

In VBA, you concatenate the value and include the delimiter:

strSQL = "SELECT...Between #" & Forms!FormName.ControlName & "# And #"...

I should have said without writting VBA code . I have not written any - ever. Just wanted to include a form and then a query from design view. Thanks for the super quick reply.

 

[pbaldy]

Happy to help.

 

[Moses76]

Is it possible to do it without VBA code . I do not know how to write code.

 

[pbaldy]

The first example would be directly in a query criteria.

 

[Moses76]

The first example would be directly in a query criteria.

Just figured it out while I was checking other stuff - but i am struggling with one last thing - assigning that value to a text box on the form itself. Any ideas ? Promise this is the last question on this thread.

 

[pbaldy]

Simplest is probably a control source of:

=DLookup(...)

with the appropriate arguments.

http://access.mvps.org/access/general/gen0018.htm