Percent Syntax not working...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Actually, I switched it to just Sum. This is what I have now:

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

My Work Center and Percent fields on the frmAvg, where this is supposed to
be displayed, is coming up with blank fields.
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Roger Carlson said:
DSUM requires two arguments: a field name and a table name. I think you
just want SUM.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Seren said:
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?
Click to expand...
Click to expand...
 
The way to debug this is to put the following command after building the
string:

Debug.Print STRSQL2

and put a breakpoint on the line following it. Your SQL statement will
appear in the Immediate Window just as Access will try to execute it. From
this, you can past it into a new query to see what it is doing.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Seren said:
Actually, I switched it to just Sum. This is what I have now:

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

My Work Center and Percent fields on the frmAvg, where this is supposed to
be displayed, is coming up with blank fields.
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Roger Carlson said:
DSUM requires two arguments: a field name and a table name. I think you
just want SUM.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Seren said:
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
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
Debug.Print STRSQL2

and put a breakpoint on the line following it.
Click to expand...

or else

debug.assert vbYes=msgbox(strsql2, vbYesNo, "Is this okay?")


All the best

Tim F
 
Back
Top