425,829 Members | 673 Online
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

# Sum Query reports invalid procedure call

 P: 3 the query is as follow and is based on a big table called WeldData. There are 2 materials being welded and each material can have different sizes. so i have to pick the smallest size, unfortunately the diameter field is in text (ie one enters it like 2"), so i have to take running sum of the filtered values to derive at the Total. Please help me where is the error ?? Many Thanks in Advance. Posted below is the copy of the query. Expand|Select|Wrap|Line Numbers SELECT sum(final)*3.14*25.4 AS new FROM (SELECT [Weld Type], [DWR Date], [Weld Flag], IIf(CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1)))
6 Replies

 Expert 100+ P: 489 I moved your Conversion from string to number and if statements out to a public Function. Expand|Select|Wrap|Line Numbers Public Function MaterialSize(Material1 As String, Material2 As String) As Integer Material1 = Left(Material1, InStr(1, Material1, """", 1) - 1) Material2 = Left(Material2, InStr(1, Material2, """", 1) - 1)   If Material1 < Material2 Then     MaterialSize = Material1 Else     MaterialSize = Material2 End If   End Function And Changing your SQL to this Expand|Select|Wrap|Line Numbers SELECT sum(final)*3.14*25.4 AS new FROM (SELECT [Weld Type], [DWR Date], [Weld Flag], MaterialSize([Material Diameter 1],[Material Diameter 2]) AS Final FROM WeldData GROUP BY [Weld Type], [DWR Date], [Weld Flag], MaterialSize([Material Diameter 1],[Material Diameter 2])) AS a WHERE [Weld Type] Like '*B*' And (Month([DWR Date])=Month(Now())-1) And ([Weld Flag] like "Production Joint" or [Weld Flag] like "New Joint" or [Weld Flag] like "Tracer Joint");  Feb 21 '09 #2

 P: 3 It is a much better solution. Many Thanks !! DonRayner Feb 21 '09 #3

 Expert 100+ P: 489 You're quite welcome. Good luck with your project. Feb 21 '09 #4

 Expert Mod 15k+ P: 31,418 FYI: I would change your WHERE clause as follows too : Expand|Select|Wrap|Line Numbers WHERE ([Weld Type] Like '*B*')   AND (Month([DWR Date])=Month(Date())-1)   AND ([Weld Flag] In('Production Joint', 'New Joint', 'Tracer Joint'); The effect will be the same, but it's tidier and more flexible. Feb 22 '09 #5

 P: 3 Many Thanks for the advice. it is cleaner code. Feb 23 '09 #6

 Expert Mod 15k+ P: 31,418 My pleasure. Welcome to Bytes! Feb 23 '09 #7