Neep help For The Query

[Marc Robillard]

Hi All, If I run that into a new query in Access All goes good,

SELECT BillDetails.Bill_id, Bill.Bill_Date, Orders.Ord_id, Orders.Ord_Date,
Bill.Bill_DepositUsed, Bill.Bill_TransportUsed, Orders.TaxesGroup_id,
Devise.Devise_Symbol,
Format(Switch(orders.taxesGroup_id=1,[TotaltpsTvq],orders.taxesGroup_id=2,[TotalTva],orders.taxesGroup_id=3,[TotalNoTaxes],orders.taxesGroup_id=4,[TotalTPS]),"Standard")
AS GrandTotal, BillPayments.TotalPayment, TaxesGroup.Taxes_LongDesc_fr AS
Taxes, Customer.Cst_Name

FROM TaxesGroup INNER JOIN ((Devise INNER JOIN (Customer INNER JOIN Orders
ON Customer.cst_id=Orders.Cst_id) ON Devise.Devise_id=Orders.Devise_id)
INNER JOIN ((((((Bill INNER JOIN BillTotalCanQue ON
Bill.Bill_id=BillTotalCanQue.Bill_id) INNER JOIN BillTotalTVA ON
Bill.Bill_id=BillTotalTVA.Bill_id) INNER JOIN BillTotalNoTaxe ON
Bill.Bill_id=BillTotalNoTaxe.Bill_id) INNER JOIN BilltotalTPS ON
Bill.Bill_id=BilltotalTPS.Bill_id) INNER JOIN BillPayments ON
Bill.Bill_id=BillPayments.Bill_id) INNER JOIN BillDetails ON
Bill.Bill_id=BillDetails.Bill_id) ON Orders.Ord_id=Bill.Ord_id) ON
TaxesGroup.TaxesGroup_ID=Orders.TaxesGroup_id

WHERE (Orders.cst_id = 1)

GROUP BY BillDetails.Bill_id, Bill.Bill_Date, Orders.Ord_id,
Orders.Ord_Date, Bill.Bill_DepositUsed, Bill.Bill_TransportUsed,
Orders.TaxesGroup_id, Devise.Devise_Symbol,
Format(Switch(orders.taxesGroup_id=1,[TotaltpsTvq],orders.taxesGroup_id=2,[TotalTva],orders.taxesGroup_id=3,[TotalNoTaxes],orders.taxesGroup_id=4,[TotalTPS]),"Standard"),
BillPayments.TotalPayment, TaxesGroup.Taxes_LongDesc_fr, Customer.Cst_Name,
BillTotalCanQue.TotaltpsTvq, BillTotalTVA.TotalTva,
BillTotalNoTaxe.TotalNoTaxes, BilltotalTPS.TotalTPS;

But I use VB6 with ADO : here is the messages error: using exactly the same
query.


Run-time error '-2147217904 (80040e10)':
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1.

I simply don't get it.

 

[James A. Fortune]

Hi All, If I run that into a new query in Access All goes good,

SELECT BillDetails.Bill_id, Bill.Bill_Date, Orders.Ord_id, Orders.Ord_Date,
Bill.Bill_DepositUsed, Bill.Bill_TransportUsed, Orders.TaxesGroup_id,
Devise.Devise_Symbol,
Format(Switch(orders.taxesGroup_id=1,[TotaltpsTvq],orders.taxesGroup_id=2,[TotalTva],orders.taxesGroup_id=3,[TotalNoTaxes],orders.taxesGroup_id=4,[TotalTPS]),"Standard")
AS GrandTotal, BillPayments.TotalPayment, TaxesGroup.Taxes_LongDesc_fr AS
Taxes, Customer.Cst_Name

FROM TaxesGroup INNER JOIN ((Devise INNER JOIN (Customer INNER JOIN Orders
ON Customer.cst_id=Orders.Cst_id) ON Devise.Devise_id=Orders.Devise_id)
INNER JOIN ((((((Bill INNER JOIN BillTotalCanQue ON
Bill.Bill_id=BillTotalCanQue.Bill_id) INNER JOIN BillTotalTVA ON
Bill.Bill_id=BillTotalTVA.Bill_id) INNER JOIN BillTotalNoTaxe ON
Bill.Bill_id=BillTotalNoTaxe.Bill_id) INNER JOIN BilltotalTPS ON
Bill.Bill_id=BilltotalTPS.Bill_id) INNER JOIN BillPayments ON
Bill.Bill_id=BillPayments.Bill_id) INNER JOIN BillDetails ON
Bill.Bill_id=BillDetails.Bill_id) ON Orders.Ord_id=Bill.Ord_id) ON
TaxesGroup.TaxesGroup_ID=Orders.TaxesGroup_id

WHERE (Orders.cst_id = 1)

GROUP BY BillDetails.Bill_id, Bill.Bill_Date, Orders.Ord_id,
Orders.Ord_Date, Bill.Bill_DepositUsed, Bill.Bill_TransportUsed,
Orders.TaxesGroup_id, Devise.Devise_Symbol,
Format(Switch(orders.taxesGroup_id=1,[TotaltpsTvq],orders.taxesGroup_id=2,[TotalTva],orders.taxesGroup_id=3,[TotalNoTaxes],orders.taxesGroup_id=4,[TotalTPS]),"Standard"),
BillPayments.TotalPayment, TaxesGroup.Taxes_LongDesc_fr, Customer.Cst_Name,
BillTotalCanQue.TotaltpsTvq, BillTotalTVA.TotalTva,
BillTotalNoTaxe.TotalNoTaxes, BilltotalTPS.TotalTPS;

But I use VB6 with ADO : here is the messages error: using exactly the same
query.


Run-time error '-2147217904 (80040e10)':
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1.

I simply don't get it.

Here are some shots in the dark:

In your situation I would try using single quotes instead of double
quotes around the word "Standard." Failing that, I would make a simple
test query that uses the Format() function. Failing that, I would make
a simple test query that uses the Switch() function. If the single
quotes work, it means that VB6 read the first double quote around
"Standard" as the end of the string. That's not really likely given the
error message. Since the Switch() function is part of SQL and Format()
is not, not finding Format() would cause SQL to think that it's a
parameter rather than a function. I had a related problem when Office
2K3 was first installed on some machines. Before the first SP came out,
Access 2K3 was so buggy that we had to uninstall Access from Office 2K3
and install A97. That caused the Format() function to error behind
forms and reports on a couple of machines in spite of the fact that none
of the References were "missing." The following module function
(a.k.a., User Defined Function) would run in a query though:

Public Function NewFormat(varIn As Variant, strFormat) As String
NewFormat = Format(varIn, strFormat)
End Function

The following thread discusses a similar problem:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/ccb1c075306a7c7f

Albert Kallal provides a Microsoft KB link here:

http://groups.google.com/group/comp.databases.ms-access/msg/7e467105f4e00c33

From that it appears that parts of Jet usually cause the problem. "But
wait," you say, "I'm not using Jet! The Format() function exists in VB
too." MichKa points out that Jet gets used in places you sometimes
don't expect:

http://groups.google.com/group/comp.databases.ms-access/msg/9f8be2064bb41123

That's my best guess for now.

James A. Fortune
(e-mail address removed)

In:

http://groups.google.com/group/comp.databases.ms-access/msg/bb57379910733499

I said:

"Maybe it's like speculating that GM and Ford will merge when one of
them goes bankrupt. It's possible, but it's anyone's guess how
probable. Although, it's fun to think about a Ford Camaro or a GM
Mustang ."

I no longer think that GM and Ford will merge. That speculation was
based on the historical use of the automotive industry for wartime
industrial capacity. Yet the U.S. didn't want to get abused by auto
industry greed in order to keep that domestic capacity in place either.
It's considered bad form to outsource wartime production at all if at
all possible, much less to possible allies of potential enemies. It was
ironic that Daimler, a German corporation, bought (ransacked?) a company
that indirectly caused so much damage to Germany via its wartime
production. Daimler, to their credit, did bring DaimlerChrysler back to
a position where it could be sold. Now that Daimler has sold Chrysler
to Cerberus Capital Management, L.P., an American company, we're much
less likely, IMO, to see that GM Mustang.

 

[Marc Robillard]

Hi james,
I don't understand all of what you said but I guess I have found my
solution.
I will create a query that will do the switch and format function, then use
it as a Table. into my master query.

The problems I had is when I compare GrandTotal and TotalPayments Since
Payment is entered manually : I got no problems with decimals. but
GrandTotal is a calculated Amount, so 100.12 of payment not equal to
100.11999999999999 that is why I need the format ....

But Thanks anyway you put me on the right Track !

Hi All, If I run that into a new query in Access All goes good,

SELECT BillDetails.Bill_id, Bill.Bill_Date, Orders.Ord_id,
Orders.Ord_Date, Bill.Bill_DepositUsed, Bill.Bill_TransportUsed,
Orders.TaxesGroup_id, Devise.Devise_Symbol,
Format(Switch(orders.taxesGroup_id=1,[TotaltpsTvq],orders.taxesGroup_id=2,[TotalTva],orders.taxesGroup_id=3,[TotalNoTaxes],orders.taxesGroup_id=4,[TotalTPS]),"Standard")
AS GrandTotal, BillPayments.TotalPayment, TaxesGroup.Taxes_LongDesc_fr AS
Taxes, Customer.Cst_Name

FROM TaxesGroup INNER JOIN ((Devise INNER JOIN (Customer INNER JOIN
Orders ON Customer.cst_id=Orders.Cst_id) ON
Devise.Devise_id=Orders.Devise_id) INNER JOIN ((((((Bill INNER JOIN
BillTotalCanQue ON Bill.Bill_id=BillTotalCanQue.Bill_id) INNER JOIN
BillTotalTVA ON Bill.Bill_id=BillTotalTVA.Bill_id) INNER JOIN
BillTotalNoTaxe ON Bill.Bill_id=BillTotalNoTaxe.Bill_id) INNER JOIN
BilltotalTPS ON Bill.Bill_id=BilltotalTPS.Bill_id) INNER JOIN
BillPayments ON Bill.Bill_id=BillPayments.Bill_id) INNER JOIN BillDetails
ON Bill.Bill_id=BillDetails.Bill_id) ON Orders.Ord_id=Bill.Ord_id) ON
TaxesGroup.TaxesGroup_ID=Orders.TaxesGroup_id

WHERE (Orders.cst_id = 1)

GROUP BY BillDetails.Bill_id, Bill.Bill_Date, Orders.Ord_id,
Orders.Ord_Date, Bill.Bill_DepositUsed, Bill.Bill_TransportUsed,
Orders.TaxesGroup_id, Devise.Devise_Symbol,
Format(Switch(orders.taxesGroup_id=1,[TotaltpsTvq],orders.taxesGroup_id=2,[TotalTva],orders.taxesGroup_id=3,[TotalNoTaxes],orders.taxesGroup_id=4,[TotalTPS]),"Standard"),
BillPayments.TotalPayment, TaxesGroup.Taxes_LongDesc_fr,
Customer.Cst_Name, BillTotalCanQue.TotaltpsTvq, BillTotalTVA.TotalTva,
BillTotalNoTaxe.TotalNoTaxes, BilltotalTPS.TotalTPS;

But I use VB6 with ADO : here is the messages error: using exactly the
same query.


Run-time error '-2147217904 (80040e10)':
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1.

I simply don't get it.

Here are some shots in the dark:

In your situation I would try using single quotes instead of double quotes
around the word "Standard." Failing that, I would make a simple test
query that uses the Format() function. Failing that, I would make a
simple test query that uses the Switch() function. If the single quotes
work, it means that VB6 read the first double quote around "Standard" as
the end of the string. That's not really likely given the error message.
Since the Switch() function is part of SQL and Format() is not, not
finding Format() would cause SQL to think that it's a parameter rather
than a function. I had a related problem when Office 2K3 was first
installed on some machines. Before the first SP came out, Access 2K3 was
so buggy that we had to uninstall Access from Office 2K3 and install A97.
That caused the Format() function to error behind forms and reports on a
couple of machines in spite of the fact that none of the References were
"missing." The following module function (a.k.a., User Defined Function)
would run in a query though:

Public Function NewFormat(varIn As Variant, strFormat) As String
NewFormat = Format(varIn, strFormat)
End Function

The following thread discusses a similar problem:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/ccb1c075306a7c7f

Albert Kallal provides a Microsoft KB link here:

http://groups.google.com/group/comp.databases.ms-access/msg/7e467105f4e00c33

From that it appears that parts of Jet usually cause the problem. "But
wait," you say, "I'm not using Jet! The Format() function exists in VB
too." MichKa points out that Jet gets used in places you sometimes don't
expect:

http://groups.google.com/group/comp.databases.ms-access/msg/9f8be2064bb41123

That's my best guess for now.

James A. Fortune
(e-mail address removed)

In:

http://groups.google.com/group/comp.databases.ms-access/msg/bb57379910733499

I said:

"Maybe it's like speculating that GM and Ford will merge when one of them
goes bankrupt. It's possible, but it's anyone's guess how probable.
Although, it's fun to think about a Ford Camaro or a GM Mustang ."

I no longer think that GM and Ford will merge. That speculation was based
on the historical use of the automotive industry for wartime industrial
capacity. Yet the U.S. didn't want to get abused by auto industry greed
in order to keep that domestic capacity in place either. It's considered
bad form to outsource wartime production at all if at all possible, much
less to possible allies of potential enemies. It was ironic that Daimler,
a German corporation, bought (ransacked?) a company that indirectly caused
so much damage to Germany via its wartime production. Daimler, to their
credit, did bring DaimlerChrysler back to a position where it could be
sold. Now that Daimler has sold Chrysler to Cerberus Capital Management,
L.P., an American company, we're much less likely, IMO, to see that GM
Mustang.

 

[James A. Fortune]

Hi james,
I don't understand all of what you said but I guess I have found my
solution.
I will create a query that will do the switch and format function, then use
it as a Table. into my master query.

The problems I had is when I compare GrandTotal and TotalPayments Since
Payment is entered manually : I got no problems with decimals. but
GrandTotal is a calculated Amount, so 100.12 of payment not equal to
100.11999999999999 that is why I need the format ....

But Thanks anyway you put me on the right Track !

I'm sorry for being recondite. Your initial post indicated you required
more understanding so I elaborated, perhaps too much. I'm glad you
found a solution. BTW, comparing floating point values can also be done
using the Abs() function:

Abs(100.11999999999 - 100.12) < 0.005

Hmmm...

IIf(Abs(Nz(GrandTotal, 0) - Nz(TotalPayments, 0)) < 0.015, "Paid in
Full", "Remaining Balance: $" & CStr(Nz(GrandTotal, 0) -
Nz(TotalPayments, 0))) As PaymentStatus

I guess you'd still need the Format() function [or Round()] .

James A. Fortune
(e-mail address removed)