#Name? Error, How to query data and run report 2

[EXlhell]

Hi All,

I'm tired of Excel and constantly updating links in workbooks, so I'm linking tables to our SQL server in hopes of querying data to generate reports. After failed attempts trying to recreate examples from the FAQ section, I'm reaching out for help. Ultimately, I want to query a handful of joined tables based on parameters selected in my combo boxes and then generate a report, without using the built in wizards. For right now, I've simplified my select statement to select from only one table.

With the below code, I see the "Name?" error. I tried setting QueryDefs and that didn't work (obviously missing something). Then, I thought I would set the report RecordSource = to the strSQL variable and received the "Name?" error. Can someone help me understand what I'm missing and why the below code wouldn't work?

Additionally, I've added txt boxes to the report and set their respective control sources, in properties table in design view of the report, to the field names in the table that I'm querying. For example: =[UMONTH]

Private Sub cmd_RR_RE_Click()

Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT * FROM dbo_TOTALS_RS "

If Not (Me.cboDate.Value = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If

strWhere = strWhere & " UMONTH = '" & Me.cboDate.Value & "'"
End If

If Not (Me.cboBook.Value = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If

strWhere = strWhere & " IBOOK = '" & Me.cboBook.Value & "'"
End If

strSQL = strSQL & strWhere

Debug.Print strSQL

Report_RE_Schedule.RecordSource = strSQL
DoCmd.OpenReport "RE_Schedule", acViewPreview

End Sub

 

[dhookom]

I would leave the record source of the report as SELECT * FROM dbo_TOTALS_RS. You can't change the record source of a form or report unless the form or report is open.

This is typically how I manage filtering reports.

Private Sub cmd_RR_RE_Click()
	Dim strWhere As String
	strWhere = " 1 = 1 "
	If Not (Me.cboDate.Value = vbNullString) Then
	    'assumes UMONTH is a text field
	    strWhere = strWhere & " AND UMONTH = '" & Me.cboDate & "'"
	End If
	If Not (Me.cboBook.Value = vbNullString) Then
	    'assumes IBOOK is a text field
	    strWhere = strWhere & " AND IBOOK = '" & Me.cboBook & "'"
	End If
	Debug.Print strWhere
	DoCmd.OpenReport "RE_Schedule", acViewPreview, , strWhere
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[EXlhell]

Duane,

Thanks for the quick response. I really appreciate you helping me out. For some reason I get a data type mismatch error on the below line of code. The record source of the report has been set to SELECT * From dbo_TOTAL_RS, I see the report's filter property loading correctly, and Filter On Load, Allow Filters are both enabled. Is there something else I'm missing?

DoCmd.OpenReport "RE_Schedule", acViewPreview, , strWhere

 

[lameid]

Linking to SQL Server"...

Lets take two steps back for a moment. You CAN link tables in Access and use them natively as if they are Access. This usually comes with a performance price.

Duane's example is good for this. However, when working with SQL data, I would propose instead to use a SQL Pass Thru query or SPT query (query that connects directly to the ODBC data source or SQL Server in your case and then sends a Native SQL command to the server). In this scenario you would make the SPT query object in advance with connection string and a demo SQL statement and use this as the record source for your report. Then on your form you use code to change the the SQL of the query and then run the report. This is more involved but cuts down on round trips to the Server. SPT queries are well documented so I'll let you search for an article on that. The below code assumes you have made a SPT Query named "qrySPT RE_Schedule" and based your report on it.

I just typed this in so there might be an error to fix and most certainly some capitalization will be fixed once you paste it.

Private Sub cmd_RR_RE_Click()
     dim db as DAO.Database
     dim qry as DAO.Querydef
     dim strSQL as string
     Dim strWhere As String

     Set db = currentdb()
     Set qry = db.querydefs("qrySPT RE_Schedule")

     strSQL = "SELECT * FROM dbo.TOTALS_RS " 


     strWhere = ""
'Stealing Duanes code here...
	If Not (Me.cboDate.Value = vbNullString) Then
	    'assumes UMONTH is a text field
	    strWhere = strWhere & " AND UMONTH = '" & Me.cboDate & "'"
	End If
	If Not (Me.cboBook.Value = vbNullString) Then
	    'assumes IBOOK is a text field
	    strWhere = strWhere & " AND IBOOK = '" & Me.cboBook & "'"
	End If
'departing back
     If strWhere <> "" Then
          strSQL = strSQL & "Where " & strWhere
     End if
     qry.SQL = strSQL & ";" 'Technically T-SQL does not require a semi-colon but not requiring may become deprecated
     qry.close
     set qry = nothing
     set db = nothing
     DoCmd.OpenReport "RE_Schedule", acViewPreview
End sub

 

[dhookom]

I totally agree with using a pass-through query if your only data in the report if from SQL Server. There is a function at faq701-7433 that I use to change the SQL property of saved queries. It is particularly useful with p-t queries.

Lameid's suggestion will increase the performance tremendously especially as the table grows.

If you are getting a type mismatch is is probably because the code I provided assumes your fields are both text. If you don't use a pass-through and UMONTH is actually a date value, try:

' 
	If Not (Me.cboDate.Value = vbNullString) Then
	    [COLOR=#4E9A06]'assumes UMONTH is a [COLOR=#EF2929][b]date[/b][/color] field[/color]
	    strWhere = strWhere & " AND UMONTH = #" & Me.cboDate & "# "
	End If

Duane
Hook'D on Access
MS Access MVP

 

[EXlhell]

Dhookom/Lameid,

Thank you both for your help. Both methods work great. I went with Lameid's suggestion because my select statement is more complex than select all from one table. I'm going to start a new thread about join types and unions because I'm getting lost in the syntax. Below are the two methods that worked.

Dhookom's:
Private Sub cmd_RR_RE_Click()
Dim strWhere As String
strWhere = " 1 = 1 "
If Not (Me.cboDate.Value = vbNullString) Then
'assumes UMONTH is a date field
strWhere = strWhere & " AND UMONTH = #" & Me.cboDate & "#"
End If
If Not (Me.cboBook.Value = vbNullString) Then
'assumes IBOOK isn't text field, if txt precede " with single quote ' similar to # above
strWhere = strWhere & " AND IBOOK = " & Me.cboBook & ""
End If
If Not (Me.cboProp.Value = vbNullString) Then
'assumes IBOOK isn't text field, if txt precede " with single quote similar to # above
strWhere = strWhere & " AND HPPTY = " & Me.cboProp & ""
End If
Debug.Print strWhere
DoCmd.OpenReport "RE_Schedule", acViewPreview, , strWhere
End Sub

Lameid's:

Private Sub cmd_RR_RE_Click()

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim strSQL As String
Dim strWhere As String

Set db = CurrentDb()
Set qry = db.QueryDefs("RE_Schedule")

strSQL = "SELECT dbo_ACCT1_RS.SCODE, dbo_ACCT1_RS.SDESC, dbo_TOTAL_RS.HPPTY, dbo_ATTRIBUTES_RS.SPROPNAME, " & _
"dbo_TOTAL_RS.UMONTH, dbo_TOTAL_RS.SMTD, dbo_TOTAL_RS.IBOOK, dbo_ATTRIBUTES_RS.SCODE, dbo_TOTAL_RS.SBEGIN, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '30000000' And '31999999',[SBEGIN]+[SMTD],0)) AS INV_CAP, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '25000000' And '25002000',[SBEGIN]+[SMTD],0)) AS LOAN_BAL, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '11000000' And '11200000',[SBEGIN]+[SMTD],0)) AS REHAB_B_RES, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '11400000' And '11800000',[SBEGIN]+[SMTD],0)) AS TAX_B_RES, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '10201100' And '10211200',[SBEGIN]+[SMTD],0)) AS REHAB_P_RES, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE = '10201150',[SBEGIN]+[SMTD],0)) AS TAX_P_RES "


strSQL = strSQL & "FROM dbo_ATTRIBUTES_RS INNER JOIN (dbo_ACCT1_RS INNER JOIN dbo_TOTAL_RS ON dbo_ACCT1_RS.HMY = dbo_TOTAL_RS.HACCT) " & _
"ON (dbo_ATTRIBUTES_RS.HMY = dbo_TOTAL_RS.HPPTY)"

strSQL = strSQL & "GROUP BY dbo_ACCT1_RS.SCODE, dbo_ACCT1_RS.SDESC, dbo_TOTAL_RS.HPPTY, dbo_ATTRIBUTES_RS.SPROPNAME, dbo_TOTAL_RS.UMONTH, " & _
"dbo_TOTAL_RS.SMTD, dbo_TOTAL_RS.IBOOK, dbo_ATTRIBUTES_RS.SCODE, dbo_TOTAL_RS.SBEGIN, [SBEGIN]+[SMTD] "


strWhere = ""
'Stealing Duanes code here...
If Not (Me.cboDate.Value = vbNullString) Then
UMONTH is a date field, remeber to add AND in first where clause
strWhere = strWhere & " dbo_TOTAL_RS.UMONTH = #" & Me.cboDate & "#"
End If
If Not (Me.cboBook.Value = vbNullString) Then
IBOOK is a number field
strWhere = strWhere & " AND dbo_TOTAL_RS.IBOOK = " & Me.cboBook & ""
End If
If Not (Me.cboProp.Value = vbNullString) Then
'assumes IBOOK isn't text field, if txt precede " with single quote similar to # above
strWhere = strWhere & " AND dbo_TOTAL_RS.HPPTY = " & Me.cboProp & ""
End If
If Not (Me.cboDate.Value = vbNullString) Then
'assumes text is a date field
strWhere = strWhere & " AND F_Name = '" & Me.cboFunds & "'"
End If
'departing back
If strWhere <> "" Then
strSQL = strSQL & " HAVING" & strWhere
End If
Debug.Print strSQL
qry.SQL = strSQL & ";"
qry.Close
Set qry = Nothing
Set db = Nothing
DoCmd.OpenReport "Copy OF RE_Schedule", acViewPreview

End Sub