By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

Using a query driven by VBA/Forms to make a chart?

P: 56
Hi,

I've made a piece of VBA that uses a combination of combolists and listboxes to run a query. I just can't think of a way to make this query generate a chart based on its results.

Here is my code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Dim db As DAO.Database
  5. Dim qdf As DAO.QueryDef
  6. Dim varItem As Variant
  7. Dim strCriteria As String
  8. Dim strSQL As String
  9.  
  10. Sub Command10_Click()
  11.  
  12. Set db = CurrentDb()
  13. strCriteria = ""
  14.  
  15. Set qdf = db.QueryDefs("Rebates Query Net")
  16.  
  17. For Each varItem In Me!cboDates.ItemsSelected
  18.    strCriteria = strCriteria & "(Rebates.Date = " & MakeUSDate(Me!cboDates.ItemData(varItem)) & ") OR "
  19. Next varItem
  20.  
  21. strCriteria = Left(strCriteria, Len(strCriteria) - 3)
  22.  
  23. strSQL = "SELECT Rebates.Name, Rebates.[" & cboData & "], Rebates.Date, Categories.Category" _
  24. & " FROM Rebates INNER JOIN Categories ON Rebates.Category = Categories.CatCode " _
  25.        & " WHERE " & strCriteria & ";"
  26.  
  27. Debug.Print strCriteria
  28. Debug.Print strSQL
  29.  
  30. qdf.SQL = strSQL
  31.  
  32. strSQL = ""
  33. DoCmd.OpenQuery "Rebates Query Net"
  34.  
  35. Set db = Nothing
  36. Set qdf = Nothing
  37.  
  38. End Sub
  39.  
  40. Function MakeUSDate(DateIn As Variant) As String
  41.  
  42.     ' Do nothing if the value is not a date.
  43.     If Not IsDate(DateIn) Then Exit Function
  44.  
  45.     ' Convert the date to a U.S. Date format.
  46.     MakeUSDate = "#" & Month(DateIn) & "/" & Day(DateIn) & "/" & Year(DateIn) & "#"
  47. End Function
  48.  
  49.  
What it basically is asking the user to do is to pick:

1) A category
2) Date(s)
3) One of 6 sets of data to return

Once this is all done I want a chart showing the results (which would be x axis of companies, y of with one of the 6 sets of cboData as the data to populate the chart).

Just can't quite figure how to get this to work, mainly thanks to those 6 sets of data.
May 9 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 218
Hi
Maybe use Excel (OK if you can handle the output side of things).
HTH
Steve
May 9 '07 #2

P: 56
I would do, but the sets of data are going to be imported each month, plus I want to be able to control the input and deletion of information... removing the possibility of people loading things twice, deleting stuff they shouldn't do etc.
May 9 '07 #3

P: 56
FYI, I managed to get the query to be driven by my selections:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub Command10_Click()
  3.  
  4. Set db = CurrentDb()
  5. strCriteria = ""
  6.  
  7. Set qdf = db.QueryDefs("Rebates Query Net")
  8.  
  9. For Each varItem In Me!cboDates.ItemsSelected
  10.    strCriteria = strCriteria & "(Rebates.Date = " & MakeUSDate(Me!cboDates.ItemData(varItem)) & ") OR "
  11. Next varItem
  12.  
  13. WhereStat = " WHERE " & "Rebates.[" & cboData & "] >0 AND "
  14.  
  15. If strCriteria <> "" Then
  16.     strCriteria = "(" & Left(strCriteria, Len(strCriteria) - 3)
  17. End If
  18.  
  19. If cboCategory = "All" Then
  20.     strCategory = ");"
  21. ElseIf cboCategory <> "All" And strCriteria = "" Then
  22.     strCategory = " Categories.Category = '" & cboCategory & "';"
  23. ElseIf cboCategory <> "All" Then
  24.     strCategory = ") AND Categories.Category = '" & cboCategory & "';"
  25. End If
  26.  
  27. If strCategory = ");" And strCriteria = "" Then
  28.     WhereStat = ""
  29.     strCategory = ";"
  30. End If
  31.  
  32. strSQL = "SELECT Rebates.Name, Rebates.[" & cboData & "], Rebates.Date, Categories.Category" _
  33. & " FROM Rebates INNER JOIN Categories ON Rebates.Category = Categories.CatCode " _
  34.        & WhereStat & strCriteria & strCategory
  35.  
  36. Debug.Print strCriteria
  37. Debug.Print strSQL
  38.  
  39. qdf.SQL = strSQL
  40.  
  41. strSQL = ""
  42. DoCmd.OpenQuery "Rebates Query Net"
  43.  
  44. Set db = Nothing
  45. Set qdf = Nothing
  46.  
  47. End Sub
  48.  
  49. Function MakeUSDate(DateIn As Variant) As String
  50.  
  51.     ' Do nothing if the value is not a date.
  52.     If Not IsDate(DateIn) Then Exit Function
  53.  
  54.     ' Convert the date to a U.S. Date format.
  55.     MakeUSDate = "#" & Month(DateIn) & "/" & Day(DateIn) & "/" & Year(DateIn) & "#"
  56. End Function
  57.  
  58.  
If you check the bit where the SQL statement is pieced together, you can see that it asks for the result of the combobox cboData, which will give one of my six data selections to populate the chart with.

Also lots of careful piecing together of the WHERE statement, hence the large amount of If Thens present. Works a treat for my purposes though.

All that remains is to have the query display as a PivotChart as opposed to a Datasheet view. Not quite sure how to do this, it may be something to do with using the DoCmd.OpenView ... but I will need to do some reading on how to use this.
May 10 '07 #4

Post your reply

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