By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
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
  1. SELECT sum(final)*3.14*25.4 AS new
  2. FROM (SELECT [Weld Type], [DWR Date], [Weld Flag], IIf(CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1)))<CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1))),CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1))),CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1)))) AS Final FROM WeldData GROUP BY [Weld Type], [DWR Date], [Weld Flag], IIf(CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1)))<CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1))),CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1))),CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1)))))  AS a
  3. 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 20 '09 #1
Share this Question
Share on Google+
6 Replies


DonRayner
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
  1. Public Function MaterialSize(Material1 As String, Material2 As String) As Integer
  2. Material1 = Left(Material1, InStr(1, Material1, """", 1) - 1)
  3. Material2 = Left(Material2, InStr(1, Material2, """", 1) - 1)
  4.  
  5. If Material1 < Material2 Then
  6.     MaterialSize = Material1
  7. Else
  8.     MaterialSize = Material2
  9. End If
  10.  
  11. End Function
And Changing your SQL to this

Expand|Select|Wrap|Line Numbers
  1. SELECT sum(final)*3.14*25.4 AS new
  2. 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
  3. 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

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

NeoPa
Expert Mod 15k+
P: 31,418
FYI: I would change your WHERE clause as follows too :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([Weld Type] Like '*B*')
  2.   AND (Month([DWR Date])=Month(Date())-1)
  3.   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

NeoPa
Expert Mod 15k+
P: 31,418
My pleasure.

Welcome to Bytes!
Feb 23 '09 #7

Post your reply

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