Advertisement

03.03.2008 at 02:30PM PST, ID: 23211079
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.0

Issue Passing Query Results to Dynamic Report function

Asked by Tom_wbi in SQL Query Syntax, MySQL Server, Access Coding/Macros

Tags: ,

I have an MS Access tabe in which all of the data types are text (this is necessary and cannot be changed).  My issue is that I believe, because of the fact that all table data is text, I need to do some conversions for my query to work properly.  In addition to this, my query operates  off of dates and needs to get passed to a function that creates a dynamic report.

I believe I have two issues:
1) Certain Access functions do not operate correctly on text data types so I need to change these, but even though I know the conversion functions like CDbl, CDate - I don't know how to get these into my Query
2) I believe I also may be having an issue passing the Query to my function that creates the dynamic report (but this may resolve itself once #1 is fixed.

Here is an example of the Table Format I am using (All fields are text and must remain this way)->
AxysCode  TransactionCode  SecuritySymbol  TransactionDate  Quantity  TransactionAmount
----------------------------------------------------------------------------------------------------------------
alex1so2            lo                $cash               02142008             0                2800
alex1so2            lo                $cash               02162008             0                2200

Here is an emaple of my code:

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:

Private Sub GetDistributionInfo_Click()
 
Dim ctl As Control
Dim sSQL As String
Dim strSQL As String
Dim sWhereClause As String
Dim stDocName As String
 
'Get Form Values
AxysCode = Me.AxysCodesList.Value
MsgBox (AxysCode)
StartDate = Me.StartDate.Value
EndDate = Me.EndDate.Value
 
'Since all values are text, we need to convert their data type so we can perform operations on them
StartDate = CDate(StartDate)
EndDate = CDate(EndDate)
 
 
'Next two lines are just for debugging purposes
MsgBox (StartDate)
MsgBox (EndDate)
 
'Assign first part of SELECT statement to a variable for use later on
sSQL = "SELECT Sum(dbo_TransactionTable.TransactionAmount) As SumOfNewTranAmount, dbo_TransactionTable.AxysCode FROM dbo_TransactionTable "
 
'Initialize the Where Clause variable
sWhereClause = "WHERE ((dbo_TransactionTable.TransactionCode)='lo') AND ((dbo_TransactionTable.TransactionDate) Between (" & StartDate & ") And (" & EndDate & ") ) "
 
sGroupClause = "GROUP BY dbo_TransactionTable.AxysCode"
 
'Set the form's Recordsource (NOTE: The below statement will be expanded later to include different criteria based on selected values
strSQL = sSQL & sWhereClause & sGroupClause
 
MsgBox (strSQL)
 
Me.RecordSource = strSQL
       
'Call Function to Create Dynamic Report
CreateDynamicReport (strSQL)
 
End Sub
 
---------------------------------------------------------------
 
Function CreateDynamicReport(strSQL As String)
Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report
 
     MsgBox ("In Function")
     
     MsgBox (strSQL)
     MsgBox (AxysCode)
 
     'set the title
     title = "Title for the Report"
      
   
     ' initialise position variables
     lngLeft = 0
     lngTop = 0
   
     'Create the report
     Set rpt = CreateReport
    
     ' set properties of the Report
     With rpt
         .Width = 8500
         .RecordSource = strSQL
         .Caption = title
     End With
    
     ' Open SQL query as a recordset
     Set db = CurrentDb
     Set rs = db.OpenRecordset(strSQL)
     
     
     ' Create Label Title
     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
     acPageHeader, , "Title", 0, 0)
     lblNew.FontBold = True
     lblNew.FontSize = 12
     lblNew.SizeToFit
    
     ' Create corresponding label and text box controls for each field.
     For Each fld In rs.Fields
    
         ' Create new text box control and size to fit data.
         MsgBox (fld.Name)
         'MsgBox (fld.Value)
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
         acDetail, , fld.Name, lngLeft + 1500, lngTop)
         txtNew.SizeToFit
        
         ' Create new label control and size to fit data.
         Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
         txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
         lblNew.SizeToFit
 
         ' Increment top value for next control
         lngTop = lngTop + txtNew.Height + 25
     Next
    
     ' Create datestamp in Footer
     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
     acPageFooter, , Now(), 0, 0)
    
     ' Create page numbering on footer
     Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
     acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
     txtNew.SizeToFit
    
     ' Open new report.
     DoCmd.OpenReport rpt.Name, acViewPreview
 
     'reset all objects
     rs.Close
     Set rs = Nothing
     Set rpt = Nothing
     Set db = Nothing
  
End Function
________________________________________________________
 
Even though my Query is being constructed correcly (as I can see my MSgBoxing it before and within my function, it is not returning any values and the result is that the fileds I am populating my Dynamic Repoort with are blank.
 
Again, I believe my issue wis with the data types in the query itself, although there may be additonal issues once this is resolved.
 
Please help!
[+][-]03.04.2008 at 01:19AM PST, ID: 21039346

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Query Syntax, MySQL Server, Access Coding/Macros
Tags: MS Access/VBA, Unsure
Sign Up Now!
Solution Provided By: aanymg
Participating Experts: 1
Solution Grade: A
 
 
[+][-]03.04.2008 at 06:56AM PST, ID: 21041488

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]03.04.2008 at 07:00AM PST, ID: 21041545

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]03.04.2008 at 07:11AM PST, ID: 21041664

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-42 / EE_QW_2_20070628