format % in query

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

Guest

I'm trying to format a number as a percent in a query. The select statement below works fine in access but not in VBA

SQLstmt = "SELECT xxx, FORMAT(sum(yyy), 'percent') FROM myTable WHERE myCondition

What am I doing wrong?
 
SQLstmt = "SELECT xxx, FORMAT(sum(yyy), 'percent') FROM myTable WHERE
myCondition"


What am I doing wrong?
Click to expand...

1) The presence of the SUM() function means it is a grouping query, but you
don't have a GROUP BY clause.

2) The Format expression expects a string delimited by quotes "" rather
than primes '' -- I am not completely sure if this is the cause of your
error but I would change it to "... FORMAT(SUM(yyy), ""percent"") FROM..."

3) Not an error, but it would help if you provided an alias for the
converted number as in FORMAT(...) AS PerCentOfYyy

Since you don't say what your error is, then it's not possible to give any
more precise help. The other thing to bear in mind is that
FORMAT(37,"percent") = 3700.00% which may or may not be what you want. An
alternative is to make it a simple formatting job as in FORMAT(37,"0\%")
which gives the expected "37%". Oh, and do remember that the result in
either case will be a text value, so don't try to multiply anything by it.

Hope that helps


Tim F
 
Tim,

Thanks so much...the quotes did it. Items 1 and 3 were already in the code, sorry for not being more precise.

Thanks again,

Bob
 
Back
Top