Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    30

    Unanswered: Switch from Avg to Percent

    I had a function set up to get the average of a certain field with matching criteria. This was my code:

    STRSQL = "SELECT Avg([" & cboWC & "]) AS Average, '" & cboWkCtr & "' AS WorkCenter, txtTC " & _
    "FROM " & cboCategory & ", tblWCDetails " & _
    " GROUP BY txtTC, cboWkCtr " & _
    "HAVING (((txtTC)='" & cboTC & "'));"

    Now, the user no longer wants the average, she wants the percent scrapped. (# bad/#started). Here is my code:

    STRSQL2 = "SELECT FORMATPERCENT(((DSUM([" & cboWC & "]))/(DSUM(txtQtyStart))/100)) AS Percent, " & _
    "'cboWkCtr' AS WorkCenter, txtTC FROM " & cboCategory & ", tblWCDetails GROUP BY txtTX, " & _
    "cboWkCtr HAVING (((txtTC)='" & cboTC & "'));"

    I think I must be missing a parenth. somewhere or something... The error I'm getting is as follows:

    "Wrong number of arguments used with function in query expression 'FORMATPERCENT(((DSum([txtLaminate]))/(DSum(txtQtyStart))/100))'"

    Any ideas?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Your first DSum() is missing the field name, and your second DSum() is missing the domain arguments. Both are required in both cases.

    Sam

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you're a brave man Sam: the interaction with GROUP BY is going to be exciting even when the DSum() syntax is fixed!

    serendee: why DSum() ?

    Avg() worked for you, you say.
    look at sum()

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    May 2006
    Posts
    30
    Yes, sorry... I have been unavailable to comment the past couple of hours. this is what I've got now:

    STRSQL2 = "SELECT (((SUM([" & cboWC & "]))/(SUM(txtQtyStart)))/100) AS Pct, '" & _
    cboWkCtr & "' AS WorkCenter, txtTC FROM " & cboCategory & ", tblWCDetails GROUP BY txtTC, " & _
    "cboWkCtr HAVING (((txtTC)='" & cboTC & "'));"

    Both Work Center and Percent fields are coming up blank.
    Last edited by serendee; 05-10-06 at 14:46.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ?? what is txtQtyStart
    ...are you summing txt ?

    ?? is cboWkCtr a string value - you are treating it as string with the '

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    May 2006
    Posts
    30
    txtQtyStart is the # of boards started. It is 'txt' because it is a text box, but it is a Number- Long Integer data type.

    cboWkCtr... is a field in tblWCDetails, which is used to populate cboWC depending on the Job Category chosen. For instance, job category "Inner Layer" is chosen, therefore cboWC is populated with ToolScan, Pretreat, Punch, etc. I'm wanting to sum quantity started for the Work Center chosen in cboWC and get the number scrapped...

    Now I'm confusing myself.

  7. #7
    Join Date
    May 2006
    Posts
    30
    Ok, regrouped... yes, cboWkCtr is a string. all that is doing is retrieving the name of the work center so that, in the form that is returned, it will show so that the user knows the info is set for that particular work center... to eliminate "Wait...which one was this for?"

    cboWC is an integer field that has a numeric value for each option.. Each category has its own table, filled with each of the Work Centers. cboWC is dynamic, it grabs the particular work center field in that table that was specified on the form.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    earlier bull$#1t idea deleted!
    glad you are regrouped - i'm falling to pieces: it is redwine-n-tv time

    !!! you can't SQL-sum a textbox on a form. or even if you can, i can't even imagine what the sum of a form-textbox GROUPed BY somefield in a table is supposed to mean.

    so probably the textbox is already a sum of something or other,

    have a go with:

    STRSQL2 = "SELECT (SUM([" & cboWC & "])/" & txtQtyStart & ")/100 AS Pct, '" & _
    cboWkCtr & "' AS WorkCenter, txtTC FROM " & cboCategory & ", tblWCDetails GROUP BY txtTC, " & _
    "cboWkCtr HAVING (((txtTC)='" & cboTC & "'));"


    izy
    Last edited by izyrider; 05-10-06 at 16:43.
    currently using SS 2008R2

  9. #9
    Join Date
    May 2006
    Posts
    30
    Well, I'm trying to retrieve all of the records in a table (that were entered through a text field with a data type of number) that have the tech code selected by the user, add up all of those quantities, and use it in a formula to calculate the percentage of scrapped boards. The info is coming from the table of information that was entered in using a different form. It *is* number-type format.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •