There is an invalid use of the . (dot) or ! operator or invalid parentheses

[wind54surfer]

Hi everyone,

I am a newbie of sorts and in need of help.

When I open a form in A2010 I get the error, but it works flawless in A2003 (is an mdb). In debug I found the problem in 2 lines (--->)

I can't figure out what is wrong, I searched the net to no avail, nothing similar to my problem.

This is the code (On Current):

Case 1
--->'Me!txtActualComm = IIf([Total Perform] = 0, 0, (((AllTotalQry.Form!PerfTot * 0.8 * 0.85 / [txtGST]) * 0.1) + ([PriceSold] / [txtGST]) - (AllTotalQry.Form!PerfTot * 0.8 * 0.85 / [txtGST])) - (Nz(AllTotalQry.Form!NoComTot) / [txtGST] * 0.8 * 0.85 * 0.1))
Me!txtOption = "Performance"
--->'Me!txtProjEarn = (IIf([chkbkCondo] = (-1), ([PriceSold] / [txtGST]) - (IIf([Total Perform] = 0, (0), ([Total Cost Query subform].Form!PerfCostTotal + [Total Cost Query subform].Form!InstCostTotal + (Nz([ExtrasEst]))))), ([PriceSold] / [txtGST]) - (IIf([Total Perform] = 0, (0), ([Total Cost Query subform].Form!PerfCostTotal + [Total Cost Query subform].Form!InstCostTotal + [txtActualComm] + (Nz([ExtrasEst])))))))
Me!txtPerfPercent.Visible = True
Me!txtCNCPercent.Visible = False
Me!txtValPercent.Visible = False

Case 2
Me!txtActualComm = IIf([Perform CNC] = 0, 0, (([Perform CNC] * 0.8 / [txtGST] * 0.85) * 0.05) + ([PriceSold] / [txtGST]) - ([Perform CNC] * 0.8 / [txtGST] * 0.85))
Me!txtOption = "Perform CNC"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), ([PriceSold] / [txtGST]) - (IIf([Perform CNC] = 0, (0), ([Total Cost Query subform].Form!PerfCostTotal))), ([PriceSold] / [txtGST]) - (IIf([Perform CNC] = 0, (0), ([Total Cost Query subform].Form!PerfCostTotal + [txtActualComm])))))
Me!txtCNCPercent.Visible = True
Me!txtPerfPercent.Visible = False
Me!txtValPercent.Visible = False

Case 3
Me!txtActualComm = IIf([Value Total] = 0, 0, (((AllTotalQry.Form!ValueTot * 0.8 * 0.85 / [txtGST]) * 0.08) + ([PriceSold] / [txtGST]) - (AllTotalQry.Form!ValueTot * 0.8 * 0.85 / [txtGST])) - (Nz(AllTotalQry.Form!NoComTot) / [txtGST] * 0.8 * 0.85 * 0.08))
Me!txtOption = "Value"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), ([PriceSold] / [txtGST]) - (IIf([Value Total] = 0, (0), ([Total Cost Query subform].Form!ValCostTotal + [Total Cost Query subform].Form!InstCostTotal + (Nz([ExtrasEst]))))), ([PriceSold] / [txtGST]) - (IIf([Value Total] = 0, (0), ([Total Cost Query subform].Form!ValCostTotal + [Total Cost Query subform].Form!InstCostTotal + [txtActualComm] + (Nz([ExtrasEst])))))))
Me!txtValPercent.Visible = True
Me!txtPerfPercent.Visible = False
Me!txtCNCPercent.Visible = False

Case 4
Me!txtActualComm = IIf([Value CNC] = 0, 0, (([Value CNC] * 0.8 / [txtGST] * 0.85) * 0.05) + ([PriceSold] / [txtGST]) - ([Value CNC] * 0.8 / [txtGST] * 0.85))
Me!txtOption = "Value CNC"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), ([PriceSold] / [txtGST]) - (IIf([Value CNC] = 0, (0), ([Total Cost Query subform].Form!ValCostTotal))), ([PriceSold] / [txtGST]) - (IIf([Value CNC] = 0, (0), ([Total Cost Query subform].Form!ValCostTotal + [txtActualComm])))))
Me!txtCNCPercent.Visible = True
Me!txtPerfPercent.Visible = False
Me!txtValPercent.Visible = False
==============================================

Any help is greatly appreciated.

 

[NickHa]

I assume the error occurs on the two lines you have indicated with '-->' prefix? These are heavily nested, so I suggest you do the following:
1. Structure the code so that you have better visibility of the embedded expressions; something like this:

Me!txtActualComm = IIf([Total Perform] = 0, 0, _
                                            (((AllTotalQry.Form!PerfTot * 0.8 * 0.85 / [txtGST]) * 0.1) + _
                                             ([PriceSold] / [txtGST]) - _
                                             (AllTotalQry.Form!PerfTot * 0.8 * 0.85 / [txtGST])) - _
                                             (Nz(AllTotalQry.Form!NoComTot) / [txtGST] * 0.8 * 0.85 * 0.1))

... where the False part of the expression is broken down to its constituent parts.
2. Remove each complex element of the expression in turn until you find the area where the error occurs, e.g.

Me!txtActualComm = IIf([Total Perform] = 0, 0, _
                                            (((1) + _
                                             ([PriceSold] / [txtGST]) - _
                                             (AllTotalQry.Form!PerfTot * 0.8 * 0.85 / [txtGST])) - _
                                             (Nz(AllTotalQry.Form!NoComTot) / [txtGST] * 0.8 * 0.85 * 0.1))

... where the first term is replaced by a literal 1.
This will eventually show you the specific clause which causes the error, which you can investigate further.
3. Check that the data sources are all present and correct. Is the 'AllTotalQry' form loaded and does its control 'PerfTot' have the correct value (etc.)?

 

[wind54surfer]

Thanks a lot for your response.
Like I said I am not expert and it will take me a while but I will figure it out how to simplify thing like you said.

The one thing I don't get at all, where is the "1" coming from?

Thanks again

 

[NickHa]

The one thing I don't get at all, where is the "1" coming from?

The point is to use a non-calculated value for terms in place of each element of a complex algorithm. You can use any value you like here. In essence, you are retaining the calculation structure, but substituting constants in order to reduce the scope of your debugging.
You could simply eliminate terms from your algorithm until you find the one which is failing, but by doing that you may be changing some other subtle aspect of the algorithm.
When you have found the failing code, you restore all other changes when you fix the fault found.

 

[wind54surfer]

Thanks again.

Working on it when I get home tonight.

 

[wind54surfer]

I did some changes and now the errors happen on 4 lines (with --->).

No idea what is wrong. The only common thing is [txtGST] but there is many instances of [txtGST] below the errors and thay don't trigger errors???

Does anybody have a clue? Please help. Thanks


Dim myHST As Single
Dim myCommission As Single
Dim myCommission2 As Single
Dim myPerfCostTotal As Single
Dim myInstCostTotal As Single
Dim myValueTotal As Single
Dim myValueCostTotal As Single


--->myHST = [PriceSold] / [txtGST]
--->myCommission = (AllTotalQry.Form!PerfTot * 0.8 * 0.85 / [txtGST])
--->myCommission2 = (Nz(AllTotalQry.Form!NoComTot) / [txtGST] * 0.8 * 0.85 * 0.1)
myPerfCostTotal = [Total Cost Query subform].Form!PerfCostTotal
myInstCostTotal = [Total Cost Query subform].Form!InstCostTotal
--->myValueTotal = (AllTotalQry.Form!ValueTot * 0.8 * 0.85 / [txtGST])
myValueCostTotal = ([Total Cost Query subform].Form!ValCostTotal)


'DO NOT make any changes to the formulas below!
Select Case optChoice

Case 1
Me!txtActualComm = IIf([Total Perform] = 0, 0, ((myCommission * 0.1) + myHST - myCommission) - myCommission2)
Me!txtOption = "Performance"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), myHST - (IIf([Total Perform] = 0, (0), (myPerfCostTotal + myInstCostTotal + (Nz([ExtrasEst]))))), myHST - (IIf([Total Perform] = 0, (0), (myPerfCostTotal + myInstCostTotal + [txtActualComm] + (Nz([ExtrasEst])))))))
Me!txtPerfPercent.Visible = True
Me!txtCNCPercent.Visible = False
Me!txtValPercent.Visible = False

Case 2
Me!txtActualComm = IIf([Perform CNC] = 0, 0, (([Perform CNC] * 0.8 / [txtGST] * 0.85) * 0.05) + myHST - ([Perform CNC] * 0.8 / [txtGST] * 0.85))
Me!txtOption = "Perform CNC"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), myHST - (IIf([Perform CNC] = 0, (0), (myPerfCostTotal))), myHST - (IIf([Perform CNC] = 0, (0), (myPerfCostTotal + [txtActualComm])))))
Me!txtCNCPercent.Visible = True
Me!txtPerfPercent.Visible = False
Me!txtValPercent.Visible = False

Case 3
Me!txtActualComm = IIf([Value Total] = 0, 0, ((myValueTotal * 0.08) + myHST - myValueTotal) - (Nz(AllTotalQry.Form!NoComTot) / [txtGST] * 0.8 * 0.85 * 0.08))
Me!txtOption = "Value"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), myHST - (IIf([Value Total] = 0, (0), (myValueCostTotal + myInstCostTotal + (Nz([ExtrasEst]))))), myHST - (IIf([Value Total] = 0, (0), (myValueCostTotal + myInstCostTotal + [txtActualComm] + (Nz([ExtrasEst])))))))
Me!txtValPercent.Visible = True
Me!txtPerfPercent.Visible = False
Me!txtCNCPercent.Visible = False

Case 4
Me!txtActualComm = IIf([Value CNC] = 0, 0, (([Value CNC] * 0.8 / [txtGST] * 0.85) * 0.05) + myHST - ([Value CNC] * 0.8 / [txtGST] * 0.85))
Me!txtOption = "Value CNC"
Me!txtProjEarn = (IIf([chkbkCondo] = (-1), myHST - (IIf([Value CNC] = 0, (0), myValueCostTotal)), myHST - (IIf([Value CNC] = 0, (0), (myValueCostTotal + [txtActualComm])))))
Me!txtCNCPercent.Visible = True
Me!txtPerfPercent.Visible = False
Me!txtValPercent.Visible = False

End Select

 

[wind54surfer]

OK, getting closer, I think.

It is [txtGST] the problem.....but only on A2010 ???
I made the text box visible and it shows #Name, opened the same form in A2003 and work perfectly.

The format of "txtGST" is Standard (if it means anything)

Anyway this text box control is "gnGST" and this are the entries in this form code:

Public gnGST As Single
------------------------------------------
On Current

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34) ' initialize strQuote to the double quotation
'mark character
Set db = CurrentDb()
Set rst = db.OpenRecordset("select GST from tblGST where dfrom<#" & lsDate & "# and dto>#" & lsDate & "#", dbOpenDynaset)

gnGST = Val(Str$(rst!GST & ""))

I don't have the knowledge to find out what is wrong, any help really appreciated.