SQL syntax error after edit

C

Chintsapete

Registered User.
Local time
Tomorrow, 01:31
Joined
Jun 15, 2012
Messages
137
Hi

I can't figure out why the first code is working and after adding a few lines it gives me a syntax error. It highlights the AS ETI. I usually work on the query grid, but I read somewhere there is a limit to the length of expression. So I ended up editing the expression in SQL view, but somehow doesn't work.

Working before editing:
Code: 
SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))) AS ETI, DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
FROM ETIGross INNER JOIN ([Salaries YTD] INNER JOIN [ETI Filter] ON [Salaries YTD].[Emp#] = [ETI Filter].[Emp#]) ON ETIGross.[Emp#] = [Salaries YTD].[Emp#]
WHERE ((([Salaries YTD].Date) Between [Forms]![PeriodSelector]![FromDate] And [Forms]![PeriodSelector]![ToDate]))
GROUP BY [Salaries YTD].[Emp#], [ETI Filter].ETI1, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))), DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6);

Not working after adding lines:
Code: 
   SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked,
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=500,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.25),
 IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,
 IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=500,500,
 IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=500,500-(0.25*([SumOfGross]-4000,
 IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000)))))))) AS ETI,
 DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
FROM ETIGross INNER JOIN ([Salaries YTD] INNER JOIN [ETI Filter] ON [Salaries YTD].[Emp#] = [ETI Filter].[Emp#]) ON ETIGross.[Emp#] = [Salaries YTD].[Emp#]
WHERE ((([Salaries YTD].Date) Between [Forms]![PeriodSelector]![FromDate] And [Forms]![PeriodSelector]![ToDate]))
GROUP BY [Salaries YTD].[Emp#], [ETI Filter].ETI1, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))), DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6);
Thanks for any help
Pete
 
Readable code is maintainable code

Code: 
   SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked,
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000
    ,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate]
                                              ,[Forms]![PeriodSelector]![ToDate],6)*5)*0.5)
    ,IIf([SumOfGross] Between 0 And 2000 And [ETI1]=500
        ,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate]
                                                  ,[Forms]![PeriodSelector]![ToDate],6)*5)*0.25)
        ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000
            ,1000
            ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=500
                ,500
                ,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=500
                    ,500-(0.25*([SumOfGross]-4000
                    ,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000
                        ,1000-(0.5*([SumOfGross]-4000))
                        )
                    )
            )
        )
    )) AS ETI
 , DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks
 , Sum([Salaries YTD].Gross) AS SumOfGross1
FROM ETIGross 
INNER JOIN ([Salaries YTD] 
INNER JOIN [ETI Filter] ON [Salaries YTD].[Emp#] = [ETI Filter].[Emp#]) 
                        ON ETIGross.[Emp#] = [Salaries YTD].[Emp#]
WHERE [Salaries YTD].Date Between [Forms]![PeriodSelector]![FromDate] And [Forms]![PeriodSelector]![ToDate]
GROUP BY [Salaries YTD].[Emp#]
       , [ETI Filter].ETI1
       , IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))), DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6);

Looks to me like your bracketing is off....

Though personally this is getting a bit too involved for an IIF, I would probably push this into a function.
 
I could not even begin to see the error in this behemoth.

Moreover you are setting up the query with hard coded values that could change in the future. A much better design would place those values in a table the query would not need to be edited if circumstances changed.
 
Hi

:D:D I feel the same with it getting to involved. Thanks for showing how to cleaning up the code. The bracketing was off your right, now I have another error. I never learned to use functions. I guess I will have to rethink the whole story to simplify the query.
Thanks
Pete
 
Probably needs more thought, but a quick thingy gets me to this function
Code: 
Public Function fnETI1(SumOfGross as double
                      , ETI1 as integer
                      , daysWorked as integer
                      , FromDate   as date
                      , to_date    as date
                      ) as double
    dim myFactor as double
    dim Mydaysinperiod as integer
    myfactor = 0.5
    if ETI1 = 500 then myFactor = 0.25
    if sumofgross < 0 then 
         fnETI1 = 0
    elseif sumofgross < 2000 then 
         Mydaysinperiod = DateDiff("ww",FromDate,[ToDate],6)
         fnETI1 = [SumOfGross]*([DaysWorked]/(mydaysinperiod*5)* myFactor)
    elseif sumofgross < 4000 then 
         fnETI1 = ETI1
    elseif sumofgross < 6000 then 
         fnETI1 = ETI1 - (myfactor*([SumOfGross]-4000))
    else
         fnETI1= -1
    endif
End Function

Which you should be able to call from within your query like so:
fneti1([SumOfGross], [ETI1],[daysworked],[Forms]![PeriodSelector]![FromDate], [Forms]![PeriodSelector]![ToDate])

P.S. What is your other error?
 
Hi Namliam

Thanks for that module, didn't get a chance yet to try.

Error: You tried to execute a query which doesn't include the specified expression etc.
I'm pretty sure I got the bracketing right at the end. Corrected code below.
Code: 
   SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked,
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000
    ,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate]
                                              ,[Forms]![PeriodSelector]![ToDate],6)*5)*0.5)
    ,IIf([SumOfGross] Between 0 And 2000 And [ETI1]=500
        ,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate]
                                                  ,[Forms]![PeriodSelector]![ToDate],6)*5)*0.25)
        ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000
            ,1000
            ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=500
                ,500
                ,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=500
                    ,500-(0.25*([SumOfGross]-4000))
                    ,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000
                        ,1000-(0.5*([SumOfGross]-4000))
								
                    			
	)))))) AS ETI
 , DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks
 , Sum([Salaries YTD].Gross) AS SumOfGross1
FROM ETIGross 
INNER JOIN ([Salaries YTD] 
INNER JOIN [ETI Filter] ON [Salaries YTD].[Emp#] = [ETI Filter].[Emp#]) 
                        ON ETIGross.[Emp#] = [Salaries YTD].[Emp#]
WHERE [Salaries YTD].Date Between [Forms]![PeriodSelector]![FromDate] And [Forms]![PeriodSelector]![ToDate]
GROUP BY [Salaries YTD].[Emp#]
       , [ETI Filter].ETI1
       , IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5)
	   ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000
			,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))), DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)
 
Did you copy the IIF to the group by clause???

p.s. should also indent your closing brackets to make sure they allign (therefor your bracket count is correct)
Code: 
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000
    ,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate]
                                              ,[Forms]![PeriodSelector]![ToDate],6)*5)*0.5)
    ,IIf([SumOfGross] Between 0 And 2000 And [ETI1]=500
        ,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate]
                                                  ,[Forms]![PeriodSelector]![ToDate],6)*5)*0.25)
        ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000
            ,1000
            ,IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=500
                ,500
                ,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=500
                    ,500-(0.25*([SumOfGross]-4000))
                    ,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000
                        ,1000-(0.5*([SumOfGross]-4000))
                        )
                    )
                )
            )
        )
    ) AS ETI

P.P.S. not having an ELSE on the last IIF is a general bad idea btw
 
No that was converted by access from query grid to SQL view like that.

I tried the module you made, but there is an error. But it got me onto an idea with the factors you used in it. I'm busy trying to work my way around using factors like you did but in a query, which I understand much better.

Thanks once more for your help it's inspiring as usual.
 
Sorry about the broken function, shouldnt write aircode :(

Code: 
Public Function fnETI1(SumOfGross As Double _
                      , ETI1 As Integer _
                      , daysWorked As Integer _
                      , FromDate As Date _
                      , to_date As Date _
                      ) As Double
    Dim myFactor As Double
    Dim Mydaysinperiod As Integer
    myFactor = 0.5
    If ETI1 = 500 Then myFactor = 0.25
    If SumOfGross < 0 Then
         fnETI1 = 0
    ElseIf SumOfGross < 2000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = [SumOfGross] * ([daysWorked] / (Mydaysinperiod * 5) * myFactor)
    ElseIf SumOfGross < 4000 Then
         fnETI1 = ETI1
    ElseIf SumOfGross < 6000 Then
         fnETI1 = ETI1 - (myFactor * ([SumOfGross] - 4000))
    Else
         fnETI1 = -1
    End If
End Function

Try that on for size
 
Wow amazing of you, thanks a lot.

I got one problem that the results are not what they should be, ergo my formulas were wrong. But I'm convinced the script you made works.
Is there a way to display the data your script works with so I can see where I'm going wrong, eg SumOfGross or SumOfDaysWorked etc? Calling it up on the query grid, didn't work the way I tried it.

I did fiddle with your script a bit but somehow I'm stuck now because I can't see the numbers used for the calculations.
I figured out that the daysworked needed to be changed to SumOfDaysWorked, else it would output 2 lines instead of one. I'm not sure if I made a muddle of your script but it still runs. :D:confused:

Code: 
Public Function fnETI1(SumOfGross As Double _
                      , ETI1 As Integer _
                      , SumOfdaysWorked As Integer _
                      , FromDate As Date _
                      , to_date As Date _
                      ) As Double
    Dim myFactor As Double
    Dim Mydaysinperiod As Integer
    myFactor = 0.5
        If ETI1 = 500 Then myFactor = 0.25
        If ETI1 = 0 Then myFactor = 0
    If SumOfGross < 0 Then
         fnETI1 = 0
    ElseIf SumOfGross < 2000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = SumOfGross * (SumOfdaysWorked / ((Mydaysinperiod) * 5))
    ElseIf SumOfGross < 4000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = SumOfGross * (SumOfdaysWorked / ((Mydaysinperiod) * 5))
         
    ElseIf SumOfGross < 6000 Then
         fnETI1 = ETI1 - (myFactor * (SumOfGross - 4000))
    Else
         fnETI1 = 0
    End If
End Function

Thanks again for all your time.
Pete
 
The names of the variables in the function do not HAVE to match with those of your input... They are simply names inside the function, I just kept them the same so it would be easy for you to reference.

You can i.e. do
Code: 
 public function some(Apples as double, Cakes as string) as string
And call the function
Some(Cakes, Oranges)

Doesnt always make things easier to track but hey...

Easiest way to debug is to add a STOP
Code: 
Public Function fnETI1(SumOfGross As Double _
                      , ETI1 As Integer _
                      , SumOfdaysWorked As Integer _
                      , FromDate As Date _
                      , to_date As Date _
                      ) As Double
    Dim myFactor As Double
    Dim Mydaysinperiod As Integer
STOP
    myFactor = 0.5
        If ETI1 = 500 Then myFactor = 0.25
        If ETI1 = 0 Then myFactor = 0
    If SumOfGross < 0 Then
         fnETI1 = 0
    ElseIf SumOfGross < 2000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = SumOfGross * (SumOfdaysWorked / ((Mydaysinperiod) * 5))
    ElseIf SumOfGross < 4000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = SumOfGross * (SumOfdaysWorked / ((Mydaysinperiod) * 5))
         
    ElseIf SumOfGross < 6000 Then
         fnETI1 = ETI1 - (myFactor * (SumOfGross - 4000))
    Else
         fnETI1 = 0
    End If
End Function

And start from the immediate window the function
Press CTRL + G to open the debug window and type there
Code: 
?fnETI1(2500, 500,10,#10/15/2014#, #11/13/2014#)
And you then "STOP" the code at the stop line automagicaly and press F8 to execute it step by step, line by line so you can see exactly what your function does, why and when. If you just want to execute it to the end after you have stopped press F5.

You can hold your mouse over variables to see their value at that point in time.
After finishing your function, the next line in the debug window will show the outcome of your function, in the above case:
-0,834863917181499

Good luck

PS I did my best to mimic your IIF structure as best I could, however I cant promise I did not make any mistakes or misinterpertations

PPS I hope you do agree such a function is much more readable and maintainable than your IIF
 
Thanks for the tutorial I had an interesting morning trying to figure out how to change the code. I do agree it is easier to read, but it's a big step to learn and hard to find the time doing so. One day.....
I appreciate all the time you put in again.

I will try and see if I get there.
Cheers
 
I might have found what it was in the SQL.
Two open braces and no closes within their parameters scope.

Now I didn't bother to read through it all to see if there has been a solution, but I'm fairly sure this is what doesn't work.

Code: 
   SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked,
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),
 IIf([SumOfGross] Between 0 And 2000 And [ETI1]=500,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.25),
 IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,
 IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=500,500,
 IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=500[COLOR="Red"][B],500-(0.25*([SumOfGross]-4000,[/B][/COLOR]
 IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000)))))))) AS ETI,[COLOR="Red"][/COLOR]
 DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
FROM ETIGross INNER JOIN ([Salaries YTD] INNER JOIN [ETI Filter] ON [Salaries YTD].[Emp#] = [ETI Filter].[Emp#]) ON ETIGross.[Emp#] = [Salaries YTD].[Emp#]
WHERE ((([Salaries YTD].Date) Between [Forms]![PeriodSelector]![FromDate] And [Forms]![PeriodSelector]![ToDate]))
GROUP BY [Salaries YTD].[Emp#], [ETI Filter].ETI1, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))), DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6);
 
Thanks blue, though ChintsaPete had allready found it :)

Just me pushing a better solution on him that he doesnt want or need :)
 
Thanks Dan

I did find them as well eventually, but there is another bug in that code. I got involved with Namliam's code and didn't bother to post the corrected code which didn't work anyway.
Thanks for taking the time to look at it.


Namliam

I got back to your original code after running the tests you suggested. I than realized the to_date was blank, looking at it I realized it needs to be ToDate. So your initial code was spot on except SumOfDaysWorked and the date.
Wow, I don't know how you do it, but I do realize what a beginner I still am after all this years trying to database. Your interpretation was/is amazing.
Thanks a million.
 
my code was simply your code put in a more "programmers" way, so take pride in your IIF :)

Allthough debugging the function was probably a whole lot easier than trying to work out what goes wrong in that monster of a nested IIF
 
Hi Namliam
I have one more problem I tried to fix put somehow it doesn't work and I don't know what I'm doing wrong. Essentially, the fnETI can't be more than 1000, so if it's bigger then 1000 it needs to be 1000. I change the code but it doesn't work. If you have another moment can you please have a look at it.

Code: 
Public Function fnETI1(SumOfGross As Double _
                      , ETI1 As Integer _
                      , SumOfDaysWorked As Integer _
                      , FromDate As Date _
                      , ToDate As Date _
                      ) As Double
    Dim myFactor As Double
    Dim Mydaysinperiod As Integer
    Dim ETI As Integer
    myFactor = 0.5
    If ETI1 = 500 Then myFactor = 0.25
    If SumOfGross < 0 Then
         fnETI1 = 0
    ElseIf SumOfGross < 2000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = [SumOfGross] * ([SumOfDaysWorked] / (Mydaysinperiod * 5) * myFactor)
    ElseIf SumOfGross < 4000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = [SumOfGross] * ([SumOfDaysWorked] / (Mydaysinperiod * 5))
    ElseIf fnETI1 > 4000 Then
           fnETI1 = ETI
    ElseIf SumOfGross < 6000 Then
         fnETI1 = ETI1 - (myFactor * ([SumOfGross] - 4000))
    Else
         fnETI1 = -1
    End If
End Function
Thanks
Pete
 
you mean fnETI1, right?

simply add to the end
Code: 
    End If
    if fnETI1 > 6000 then fnETI1 = 6000
End Function
 
Close and yet so far, I keep practising.
That's what I meant, sorry. But perfect it works.
Thanks a lot you're a star.
 
You are going to have problems some day with these hardcoded values.... not tomorrow, not next month... maybe not next year, but some day....
 
You must log in or register to reply here.

Similar threads

S
Calculation in VBA
Replies
1
Views
6,859
shikha77
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom