By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

Expression Too Complex Error

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. ([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]
  2.  
Help?
May 31 '07 #1
Share this Question
Share on Google+
9 Replies

theaybaras
P: 52
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:

Expand|Select|Wrap|Line Numbers
  1. ([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]
  2.  
Help?
after your last comma, try adding () as in the rest of your code
Expand|Select|Wrap|Line Numbers
  1. ([Pace Per Gross Profit]/[CountOfmbrc_name]) AS [Pace Per Store Gross Profit]
rather than just
Expand|Select|Wrap|Line Numbers
  1. [Pace Per Gross Profit]/[CountOfmbrc_name] AS [Pace Per Store Gross Profit]
May 31 '07 #2

P: 5
Thank you for help but unfortuantely, I still received the same error.
May 31 '07 #3

P: 5
after your last comma, try adding () as in the rest of your code
Expand|Select|Wrap|Line Numbers
  1. ([Pace Per Gross Profit]/[CountOfmbrc_name]) AS [Pace Per Store Gross Profit]
rather than just
Expand|Select|Wrap|Line Numbers
  1. [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!
May 31 '07 #4

theaybaras
P: 52
Great!

theAybaras
May 31 '07 #5

P: 6
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...
Aug 27 '07 #6

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Sum(IIf([PPN]=True,([jmlpsn]*[hargasat]/1.1)*((100-[diskon])/100),([jmlpsn]*[hargasat])*((100-[diskon])/100))) AS Netto
  2.  
Expand|Select|Wrap|Line Numbers
  1. Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto
  2.  
Aug 27 '07 #7

P: 6
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
Aug 28 '07 #8

P: 6
I already changed FieldSize hargasat from LONG INTEGER to DOUBLE , its working... and the problem solved...
thanks
Aug 28 '07 #9

FishVal
Expert 2.5K+
P: 2,653
I already changed FieldSize hargasat from LONG INTEGER to DOUBLE , its working... and the problem solved...
thanks
You are welcome.

Regards,
Fish
Aug 28 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.