Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

define output field in jet sql 1

Status
Not open for further replies.
LadyLin

LadyLin

MIS
Dec 17, 2003
41
US
I have a sql statement to gather statistics from an Access table:

Code: 
Select  Top 10 yymm,sum(ScrapAero) As AeroScrap,sum(ScrapVendor) as VendorScrap, sum(CompletedQty) as Completed ,(100*(sum(ScrapAero)+sum(ScrapVendor)))/sum(CompletedQty) as [Scrap%]  from MonthlyCompletedNMCARsbyPart N  where yymm > '2008/12' and yymm < '2009/07'      Group by yymm  Order by sum(CompletedQty) Desc
I attach the record set to a chart, I
I want the Scrap% to be a number with 1 decimal position.
I am at a loss on how to do this, I've tried CAST and CONVERT but haven't had any success, can anyone help?
 
Sort by date Sort by votes
If you don't mind it being a string you can use FORMAT()

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
I did try Format (the%field,"0.00") but when the chart displays I get a blank chart. I have the recordset also linked to a grid so I can see the data and the data is good.
 
Upvote 0 Downvote
Have you had a look at the FormatPercent() function?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
I tried
Code: 
Select  Top 10 yymm,sum(ScrapAero) As AeroScrap,sum(ScrapVendor) as VendorScrap, sum(CompletedQty) as Completed , formatpercent((100*(sum(ScrapAero)+sum(ScrapVendor)))/sum(CompletedQty)) as [Scrap%]  from MonthlyCompletedNMCARsbyPart N  where yymm > '2008/12' and yymm < '2009/07'      Group by yymm  Order by sum(CompletedQty) Desc
and get the error undefined function 'formatpercent'. Is that not allowed in a SQL statement?
 
Upvote 0 Downvote
Is this based on a SQLServer table rather than an access table?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
No this is an access table and because I use it as the data source for a MSChart control, I think the % needs to be a number format. I'm going to try the CONVERT function again.
 
Upvote 0 Downvote
What version of Access are you using?

The reason I ask is that CAST and CONVERT aren't valid Access functions (whereas FormatPercent is) at least up until the 2002 SP3 that I'm using.

They are however, valid SQL Server functions (where FormatPercent is not)...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
The database is in Access 2000, but I'm running the query in Visual Basic. This is my connection to the database
Code: 
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0" & _
   ";Data Source=X:\xxx\NMCAR_R2.mdb;Jet OLEDB:Database Password=xxxx"
 
Upvote 0 Downvote
Could you show us a bit more of your code, including where the query is built, how it is called and results assigned to the recordset etc. please?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
Here's the code

Code: 
Dim oRS_Select As Recordset
Set oRS_Select = New Recordset
This is tied to a form that gives the user a lot of selection. this is the resulting sql string for one such
running...

Debug.Print strSQL
strSQL = Select yymm,sum(ScrapAero) As AeroScrap,sum(ScrapVendor) as VendorScrap, sum(CompletedQty) as Completed ,(100*(sum(ScrapAero)+sum(ScrapVendor)))/sum(CompletedQty) as [Scrap%] from MonthlyCompletedNMCARsbyPart N where yymm > '2008/12' and yymm <= '2009/07' and ucase(n.ProductCode) in ('BLUE') and n.ResourceArea in ('HARD') Group by yymm Order by yymm

Code: 
'  ****************************************
'            adLockReadOnly is what I usually use but I tried adOpenKeyset to try and update the disconnected recordset but that didn't work either
'
' cursor location is adUseClient when connection is opened
'************
 oRS_Select.Open strSQL, _
            goConn, _
            adOpenKeyset, _
            adLockOptimistic, _
            adCmdText
   If oRS_Select.RecordCount = 0 Then
      MsgBox "No data"
      Call cmdReset_Click
      GoTo Endit
   End If
   Set oRS_Select.ActiveConnection = Nothing
   'goConn.Close
   oRS_Select.MoveFirst

   Set fgOpen.DataSource = oRS_Select
   ' this recordset is also a grid so I can see the data
   ' then I link it to the chart
 With MSChtTotals
      .ShowLegend = True
      .Top = 500
      .Left = 100
      .Height = 7500
      .Width = 12000
      Set .DataSource = oRS_Select
      
   End With
' I also format the chart - I did not include that code
 Endit:
   oRS_Select.Close
   Set oRS_Select = Nothing
 
Upvote 0 Downvote
Having looked at (and tested) it, I'd go with the ROUND() function. I've tested the return datatype of [Scrap%] as is and it's adDouble (as expected), using ROUND() will keep that datatype, and MSChart will work fine with it (or at the very least it does on my machine).

You could use it like:
Code: 
round((100*(sum(ScrapAero)+sum(ScrapVendor)))/sum(CompletedQty),1)

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
Thanks so much, My chart looks good now. Something odd though, the datagrid column with the percent data has the decimal with more than 1 digit. Any thoughts as to why?
 
Upvote 0 Downvote
I have one more question, you said you tested the return datatype of [Scrap%], how do you do that? I was wondering what the datatype was myself but didn't know how to deterimine it. Thanks again. Here's another star.[bigsmile]
 
Upvote 0 Downvote
You can use the Type property of the Field object, for example:
Code: 
Debug.Print oRS_Select.Field("Scrap%").Type
This will return an integer, which can be looked up in your object browser (F2), select ADODB from the combobox in the top left (the one that says <All Libraries>), then in the classes list on the left hand side select DataTypeEnum and on the right a list of the datatypes is displayed. If you click on one you can see the value. For example, clicking on adDouble will show you:
Code: 
Const adDouble = 5
    Member of ADODB.DataTypeEnum
Another way to find out the value is to use the immediate window (Ctrl+G), if you type something like
Code: 
?adDate
then hit return it will display the constant value for that enumeration.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
Oh, and where are my manners, thanks for the star [smile]

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Update SQL's varbinary(max) field from VB6
Replies
4
Views
407
Andrzejek
Andrzejek
OCM
  • Question
Filters in access forms
Replies
2
Views
306
OCM
OCM
sal21
  • Locked
  • Question
FORMAT output of Debug.Print .responseText (from api rest)
Replies
4
Views
613
2009luca
2009luca
S
  • Question
Replace the chart in the array and substitute the name in textboxt multiline(Vb6)
Replies
0
Views
166
satriano
S
2009luca
  • Locked
  • Question
put value in web dropdown
Replies
0
Views
227
2009luca
2009luca

Part and Inventory Search

Sponsor

Back
Top