2. PHPRunner General questions
  3. MS Access Database Error
This topic is locked

MS Access Database Error

 		3/24/2008 5:36:39 AM
PHPRunner General questions
S
 swanside author

Hello.
I enter this code on a MySQL database and all is ok, but when I run it on a MS Access database I get this error.

Cannot retrive colums information. Please modify SQL query and try again.

Error message:

SQL Error State:42000?i

[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression`(SELECT Labsubtotal0.175)`., Native Error Code: FFFFF3E4, ODBC Error: [Microsoft][ODBC Microsoft Access driver} Syntax error in query expression `(SELECT Labsubtotal0.175)`.


SELECT

Job_No,

IFNULL((SELECT sum(Paying_Rate*Working_Hrs) FROM Labour WHERE Job_No = Job.Job_No),0) AS Labsubtotal,

IFNULL((SELECT sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)) FROM Material WHERE Job_No = Job.Job_No),0) AS Matsubtotal,

(SELECT Labsubtotal*0.175) AS Labvat,

(SELECT Matsubtotal*0.175) AS Matvat,

(SELECT Matsubtotal + Labsubtotal) AS subtotal,

(SELECT Labsubtotal*0.175 + Matsubtotal*0.175 ) AS vat,

(SELECT Labsubtotal*1.175 + Matsubtotal*1.175) AS TOTAL,

Job.Order_Date,

Job.File_No,

Job.Contract,

Job.Order_Site_Address,

Job.Job_Description,

Job.Invoice_Tax_Date,

Job.CustomerRef,

Job.Customer_Name,

Customer.Billing_Address

FROM Job

LEFT JOIN Customer

ON(Job.Customer_Name=Customer.Customer_Name)


Any ideas please.
Cheers

Paul.
J
 		Jane 3/24/2008

Paul,
check MySQL documentation:

http://dev.mysql.com/doc/refman/5.0/en/index.html
For example you need to replace SELECT Labsubtotal with calculated field directly.
Sergey Kornilov admin 3/24/2008

Instead of

(SELECT Labsubtotal*0.175) AS Labvat,


use

Labsubtotal*0.175 AS Labvat,
S
 		swanside author 3/25/2008

Thanks Jane and Sergey
I have done all that, I am now working on the IFNULL fields as they are coming up with this error.

Error essage:

SQL Error State:42000U4A [Microsoft}{ODBC Microsoft Access Driver] Undefined function `IFNULL` in expression., Native Error Code:fffff3e2, ODBC Error: [Microsoft][ODBC Microsoft Access Driver] Undefined function `IFNULL` in expression.


So its off to google again.
Thanks again forall your help.

Paul.
Sergey Kornilov admin 3/25/2008

IFNULL doesn't exist in MS Access. Check MS Access reference for the closest match.
S
 		swanside author 3/26/2008

I tried this code in the SQL edit in version 4.2 and when I press next, I get a popup box with PHPRunner in the top corner saying

PHPRunner has encounted a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.
Please tell Microsoft about this problem

We have created an error report that you can send to us. We will treat this report as confidential and anonymous
To see what data this error report contails, click here


select Job_No,

ISNULL Paying_Rate*Working_Hrs from labour where Job_No = job.Job_No as Labsubtotal,

ISNULL Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)from material where Job_No = job.Job_No as Matsubtotal,

Labsubtotal*0.175 as Labvat,

Matsubtotal*0.175 as Matvat,

Matsubtotal + Labsubtotal as subtotal,

Labsubtotal*0.175 + Matsubtotal*0.175 as vat,

Labsubtotal*1.175 + Matsubtotal*1.175 as TOTAL,

job.`Order_Date`,

job.`File_No`,

job.`Contract`,

job.`Order_Site_Address`,

job.`Job_Description`,

job.`Invoice_Tax_Date`,

job.`CustomerRef`,

job.`Customer_Name`,

customer.Billing_Address

From `job`

left join customer

on(job.Customer_Name=customer.Customer_Name)


Any ideas why this happens please?
Thanks

Paul.
Alexey admin 3/26/2008

Paul,
it's hard to tell what's wrong there without seeing the actual files.

Please zip and send to support@xlinesoft.com your PHPRunner project file and the database.

I'll find what's wrong there opening your project on my test box.
S
 		swanside author 3/26/2008

Paul,

it's hard to tell what's wrong there without seeing the actual files.

Please zip and send to support@xlinesoft.com your PHPRunner project file and the database.

I'll find what's wrong there opening your project on my test box.


Cheers
I have sent you two zip files,one is my project the other is the database file.
Thanks

Paul
S
 		swanside author 3/27/2008

I am still having trouble trying to get this into MS Access format.
I think the IFNULL in Accessis ISNULL but I am missing something.
This is my MySQL query.

SELECT

Job_No,

IFNULL((SELECT sum(Paying_Rate*Working_Hrs) FROM Labour WHERE Job_No = Job.Job_No),0) AS Labsubtotal,

IFNULL((SELECT sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)) FROM Material WHERE Job_No = Job.Job_No),0) AS Matsubtotal,


This is my MS Access query

SELECT

Job_No,

ISNULL((sum(Paying_Rate*Working_Hrs) FROM Labour WHERE Job_No = Job.Job_No),0) AS Labsubtotal,

ISNULL((sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)) FROM Material WHERE Job_No = Job.Job_No),0) AS Matsubtotal,


ANy help please??
Thanks

Paul
S
 		swanside author 3/31/2008

Is there any programs for converting a MySQL Statement for SQL Databases into SQL Statements for MSAccess Databases.

I am struggling with the IFNULL part?
Thanks

Paul.
Sergey Kornilov admin 3/31/2008

Paul,
I think you can fine some at RentACoder or at Elance who will convert all your queries for $10-20.