Expression Too Complex Error

[jessicaeatworld]

Hi,
I'm using Access 2003 on Windows XP.

My Error:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I'm guessing that one of these calcs is the problem:

([AVG Per Store]/[Enter Day of Month For Pace])*31 AS [Pace Per Market], ([Sum Of SumOfGP]/[Enter Day of Month For Pace])*31 AS [Pace Per Gross Profit], ([Sum Of SumOfSELLING]/[Enter Day of Month For Pace])*31 AS [Pace Per Gross Sales], [Pace Per Gross Profit]/[CountOfmbrc_name] AS [Pace Per Store Gross Profit]
 

Help?

[theaybaras]

Hi,
I'm using Access 2003 on Windows XP.

My Error:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I'm guessing that one of these calcs is the problem:

([AVG Per Store]/[Enter Day of Month For Pace])*31 AS [Pace Per Market], ([Sum Of SumOfGP]/[Enter Day of Month For Pace])*31 AS [Pace Per Gross Profit], ([Sum Of SumOfSELLING]/[Enter Day of Month For Pace])*31 AS [Pace Per Gross Sales], [Pace Per Gross Profit]/[CountOfmbrc_name] AS [Pace Per Store Gross Profit]
 

Help?

after your last comma, try adding () as in the rest of your code

([Pace Per Gross Profit]/[CountOfmbrc_name]) AS [Pace Per Store Gross Profit]

rather than just

[Pace Per Gross Profit]/[CountOfmbrc_name] AS [Pace Per Store Gross Profit]

[jessicaeatworld]

Thank you for help but unfortuantely, I still received the same error.

[jessicaeatworld]

after your last comma, try adding () as in the rest of your code

([Pace Per Gross Profit]/[CountOfmbrc_name]) AS [Pace Per Store Gross Profit]

rather than just

[Pace Per Gross Profit]/[CountOfmbrc_name] AS [Pace Per Store Gross Profit]

I followed your reasoning and was successful. The problem has been resolved. Thanks!

[theaybaras]

Great!

theAybaras

[bahenol]

Hi,
I'm using Access 2003 on Windows XP.im newbie and try to using the query

My Error:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I'm guessing that one of these calcs is the problem:

Code: ( text )
SELECT Tbl_Rincian.IdSPD, Tbl_Rincian.NoUrut, Tbl_Rincian.NoKode, Tbl_Rincian.NmBrg, Tbl_Rincian.JmlPsn, Tbl_Rincian.Satuan, Tbl_Rincian.Ket, Tbl_SPD.KetSpd, Tbl_Customer.Contact, Tbl_Customer.Prus, Tbl_Customer.Kota, Tbl_PO.TglPO, Tbl_SPD.TglSPD, Tbl_PO.NoPO, Tbl_SPD.NoSPD, Tbl_Rincian.Diskon, Tbl_Rincian.Ppn, [Hargasat]*[jmlpsn] AS TotalHrg, Sum(IIf([PPN]=True,([jmlpsn]*[hargasat]/1.1)*((100-[diskon])/100),([jmlpsn]*[hargasat])*((100-[diskon])/100))) AS Netto, Tbl_Rincian.HargaSat, Tbl_Customer.IdCust, Tbl_PIC.idPic, Tbl_PIC.NmPerson
FROM ((Tbl_Customer INNER JOIN Tbl_PO ON Tbl_Customer.IdCust = Tbl_PO.idCust) INNER JOIN (Tbl_PIC INNER JOIN Tbl_SPD ON Tbl_PIC.idPic = Tbl_SPD.idPIC) ON Tbl_PO.NoPO = Tbl_SPD.idPO) INNER JOIN Tbl_Rincian ON Tbl_SPD.NoSPD = Tbl_Rincian.IdSPD
GROUP BY Tbl_Rincian.IdSPD, Tbl_Rincian.NoUrut, Tbl_Rincian.NoKode, Tbl_Rincian.NmBrg, Tbl_Rincian.JmlPsn, Tbl_Rincian.Satuan, Tbl_Rincian.Ket, Tbl_SPD.KetSpd, Tbl_Customer.Contact, Tbl_Customer.Prus, Tbl_Customer.Kota, Tbl_PO.TglPO, Tbl_SPD.TglSPD, Tbl_PO.NoPO, Tbl_SPD.NoSPD, Tbl_Rincian.Diskon, Tbl_Rincian.Ppn, [Hargasat]*[jmlpsn], Tbl_Rincian.HargaSat, Tbl_Customer.IdCust, Tbl_PIC.idPic, Tbl_PIC.NmPerson;

Help...

[FishVal]

Hi,
I'm using Access 2003 on Windows XP.im newbie and try to using the query

My Error:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I'm guessing that one of these calcs is the problem:

Code: ( text )
SELECT Tbl_Rincian.IdSPD, Tbl_Rincian.NoUrut, Tbl_Rincian.NoKode, Tbl_Rincian.NmBrg, Tbl_Rincian.JmlPsn, Tbl_Rincian.Satuan, Tbl_Rincian.Ket, Tbl_SPD.KetSpd, Tbl_Customer.Contact, Tbl_Customer.Prus, Tbl_Customer.Kota, Tbl_PO.TglPO, Tbl_SPD.TglSPD, Tbl_PO.NoPO, Tbl_SPD.NoSPD, Tbl_Rincian.Diskon, Tbl_Rincian.Ppn, [Hargasat]*[jmlpsn] AS TotalHrg, Sum(IIf([PPN]=True,([jmlpsn]*[hargasat]/1.1)*((100-[diskon])/100),([jmlpsn]*[hargasat])*((100-[diskon])/100))) AS Netto, Tbl_Rincian.HargaSat, Tbl_Customer.IdCust, Tbl_PIC.idPic, Tbl_PIC.NmPerson
FROM ((Tbl_Customer INNER JOIN Tbl_PO ON Tbl_Customer.IdCust = Tbl_PO.idCust) INNER JOIN (Tbl_PIC INNER JOIN Tbl_SPD ON Tbl_PIC.idPic = Tbl_SPD.idPIC) ON Tbl_PO.NoPO = Tbl_SPD.idPO) INNER JOIN Tbl_Rincian ON Tbl_SPD.NoSPD = Tbl_Rincian.IdSPD
GROUP BY Tbl_Rincian.IdSPD, Tbl_Rincian.NoUrut, Tbl_Rincian.NoKode, Tbl_Rincian.NmBrg, Tbl_Rincian.JmlPsn, Tbl_Rincian.Satuan, Tbl_Rincian.Ket, Tbl_SPD.KetSpd, Tbl_Customer.Contact, Tbl_Customer.Prus, Tbl_Customer.Kota, Tbl_PO.TglPO, Tbl_SPD.TglSPD, Tbl_PO.NoPO, Tbl_SPD.NoSPD, Tbl_Rincian.Diskon, Tbl_Rincian.Ppn, [Hargasat]*[jmlpsn], Tbl_Rincian.HargaSat, Tbl_Customer.IdCust, Tbl_PIC.idPic, Tbl_PIC.NmPerson;

Help...

Hi, there.

In math expressions Truth = -1, False = 0. Try to use this to get rid of IIf().
e.g., instesd of

Sum(IIf([PPN]=True,([jmlpsn]*[hargasat]/1.1)*((100-[diskon])/100),([jmlpsn]*[hargasat])*((100-[diskon])/100))) AS Netto
 

Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto
 

[bahenol]

thank you for your respond...


I try to use the formula... in some case working properly..but if the hargasat value more than 8 digits number... the error message displayed


I use the LONG INTEGER for all number datatype for hargasat,jmlpsn and diskon, I need more solution to resolve the problem... thank you

[bahenol]

I already changed FieldSize hargasat from LONG INTEGER to DOUBLE , its working... and the problem solved...
thanks

[FishVal]

I already changed FieldSize hargasat from LONG INTEGER to DOUBLE , its working... and the problem solved...
thanks

You are welcome.

Regards,
Fish