PHP PDO query to MSAccess is not working correctly

Question

So I am accessing an MSAccess database using PDO (ODBC). I am trying to get all records from the current week NOT the past seven days. Here is my query:

SELECT * 
FROM tblBilling 
WHERE ((
      Year(DateOfService) = Year(Date()) 
  And DatePart("ww", DateOfService, 0) = DatePart("ww", Date(), 0)
))
ORDER BY DateOfService, TimeOfService

Obviously this works in MSAccess, but when using it from the web app I get the following error:

SQLSTATE[07002]: COUNT field incorrect: 
    -3010 [Microsoft][ODBC Microsoft Access Driver] 
    Too few parameters. Expected 1. 
    (SQLExecute[-3010] at ext\pdo_odbc\odbc_stmt.c:254)

I can't seem to figure this out. I must be overlooking something and my brain is now turning to mush. I know if I remove DataPart it will run the query and not return an error, but then it will not do what I need it to do.

Thanks for all the help!

Extra code as requested:

try
{
    $pdo = new PDO('odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq='.DB_NAME.';Uid=');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $myPDO = $pdo->prepare($query);
    $myPDO->execute(); 

    $result = $myPDO->fetchAll();
} catch(PDOException $e)
{
    echo $e->getMessage();
}

Can you show us your code? It's the only way people will be able to diagnose your problem. — user895378
– user895378, Commented Dec 7, 2011 at 3:23
I don't think you need those parentheticals around the WHERE statement. — Jared Farrish
– Jared Farrish, Commented Dec 7, 2011 at 4:36

Vhalik · Accepted Answer · 2011-12-07 16:12:31Z

1

For anyone wanting to know. If you are running queries against an MSAccess database the query syntax is not going to be the same from what you are use to. This is how I got it to work. I changed the double quotes around ww to single quotes.

SELECT * 
FROM tblBilling 
WHERE ((Year(DateOfService) = Year(Date()) 
AND DatePart(\'ww\', DateOfService, 0) = DatePart(\'ww\', Date(), 0)))
ORDER BY DateOfService, TimeOfService

answered Dec 7, 2011 at 16:12

Vhalik

114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pynner · Accepted Answer · 2011-12-07 14:41:21Z

0

It appears that the Datepart section of your query is not being recognized as function by your ADODB connection (it thinks it is a parameter).

This doesn't help you with your exact problem.... but here is an alternate way to get where you want to go.

I have in the past solved issues of this nature by creating a calendar table (for financial calendars which are somewhat aribitrary from a logic standpoint). This is an idea I got from Data Analysis Using SQL and Excel

The table looks something like this.

Calendar table sample

You can easily put 50 years worth of calendar days without causing any type of preformance issue. You can then get your date buckets as required by joining to the calendar table.

answered Dec 7, 2011 at 14:41

Pynner

1,01710 silver badges22 bronze badges

Collectives™ on Stack Overflow

PHP PDO query to MSAccess is not working correctly

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related