VB doCmd.openreport where clause gives prompt

[mrvertrix]

I have a Visual Basic 6 App that opens an access opbject and prints a predefined report :-

Set acApp = GetObject(SAMPLE_DB_PATH, "Access.Application")
acApp.Visible = True
With acApp.DoCmd
.OpenReport "CustomerOrders, acViewNormal

This works fine. But when i try to specify a where clause :-

.OpenReport strReportName, acViewNormal, , "CustomerXOrder.CollectionDate = '24/12/2005'"

I get a prompt from Access asking for a value of CustomerXOrder.CollectionDate.

The SQL where clause is properly structured, as i have inserted it into the SQL statement of the report to generate the right results.

Can someone please tell me how to pass the where clause to the report without the prompt requesting the value i am trying to pass.

many thanks in advance

 

[richary]

I think you need '#' around the date, not single quotes.

 

[mrvertrix]

I tired this,

"(((CustomerXOrder.CollectionDate)=#24/12/2005#))"

and it didn't work.

I meant to say that the CollectionDate fieldtype is a string, so It should be a stright String comparison, no jiggery pokery with Dates and date conversion.

Thanks for your reply, any other ideas?

 

[richary]

The SQL where clause is properly structured, as i have inserted it into the SQL statement of the report to generate the right results.

Do you mean that you placed it in the Filter property for the report? If so, I don't have any ideas.

If not, try putting CustomerXOrder.CollectionDate = '24/12/2005' into the filter property and open it within (a) access and (b) VB6

 

[richary]

The SQL where clause is properly structured, as i have inserted it into the SQL statement of the report to generate the right results.

Do you mean that you placed it in the Filter property for the report? If so, I don't have any ideas.

If not, try putting CustomerXOrder.CollectionDate = '24/12/2005' into the filter property and open it within (a) access and (b) VB6

 

[mrvertrix]

What i did was i changed the recordsource of the report from

SELECT Customer.OrderName, Orders.Meat, Orders.Product, Orders.Quantity, Orders.Lbs, Orders.Oz, Orders.OrderId FROM Orders INNER JOIN (Customer INNER JOIN CustomerXOrder ON Customer.CustomerId=CustomerXOrder.CustomerId) ON Orders.OrderId=CustomerXOrder.OrderID;

to

SELECT Customer.OrderName, Orders.Meat, Orders.Product, Orders.Quantity, Orders.Lbs, Orders.Oz, Orders.OrderId FROM Orders INNER JOIN (Customer INNER JOIN CustomerXOrder ON Customer.CustomerId=CustomerXOrder.CustomerId) ON Orders.OrderId=CustomerXOrder.OrderID WHERE CustomerXOrder.CollectionDate = '24/12/2005'

which selected the records correctly

I did what you suggested and tried the string CustomerXOrder.CollectionDate = '24/12/2005' in the filter field of the report properties (obviously using the SQL query without a WHERE clause)

and that gave me a prompt box as well.

I suspect that the doCmd.printReport is designed to be used by forms in access, and is setup to handle parameter strings formatted by internal access forms, rather than OLE object calls from VB.

I'll keep working on it. Its probably something dead simple, and knowing Microsoft, totally undocumented.

 

[richary]

I suspect that the doCmd.printReport is designed to be used by forms in access, and is setup to handle parameter strings formatted by internal access forms, rather than OLE object calls from VB.

Possibly, but I don't think so because when you run report using the second SQL string, Access shows a prompt box as well.

Are you sure about the date/string data type thing?

My only other piece of advice (if you're not already doing it) is to run the DoCmd line from within the native access VBA editor, rather than trying from VB6 - at least that will take out VB6 as a possible cause for error.
You could also try temporarily filtering on a different field. If that works you know its related to CustomerXOrder.CollectionDate or its criteria and can concentrate on that area of the problem.

 

[mrvertrix]

FINALLY FIXED, need to use table aliases

Thanks for all your help. I did what you said and ran doCmd from a vb module within access. I was still getting the same problem. I tried filtering on different columns. Some worked without prompting, some didn't.

Interestingly I found the solution by accident. To make my sql neater I used table alias names. Hey Presto, it works all of a sudden.
so the filter "CollectionXOrder.Collectiondate = '24/12/2005'" throws a prompt whereas "X.Collectiondate = '24/12/2005'" works fine.

(obviously I had to set up the alias names in the report source SQL)

So thanks once again to richary, and a big hats off to microsoft on a totally weird and flaky program.