ADO VBA Select Where Problem

[dmoore]

Hi there. I am using Excel to query an Access db and have run into trouble. I use an InputBox to get a ReportID from the user and then in my sql statement use the SELECT WHERE the ReportID = the ReportID in the table ... without success. I am thinking it may have something to do with fact that result from InputBox is a string whereas field in the table is a number .. so they are not recognized as the same thing. I tried Cstr() on the field but that didn't work either. Has anyone ran into this before and found a solution? Thank you for your thoughts.

[RobDog888]

Welcome to the Forums.

Could you post your query's sql?

Are you getting any errors?

The syntax should be like ...

SELECT * FROM Table1 WHERE ReportID = '" & txtReportID & "'"

Where txtReportID is the variable that gets the value entered in from the InputBox.

HTH

[dmoore]

Thank you for your reply. Below is the code. I have made the change you suggested but as before I get my headings ReportID and Symbol posted to the Excel Worksheet but no data. After a flip through several books in Chapters, I am thinking there might be an issue because of the UNION??

Anyhow, any insight you might have is greatly appreciated.
Thank you.

Public Sub QueryForAssetList()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim wkb As Workbook
Dim wksQ1 As Worksheet
Dim wksQ2 As Worksheet
Dim wksQ3 As Worksheet
Dim sReportID As String
Dim iReportID As Integer
Dim lReportID As Long
Dim ReportID As Variant

' Associate Worksheet Objects with the Worksheet Name that
' will hold the Query Results

Set wksQ1 = Worksheets("Query1Results")
Set wksQ2 = Worksheets("Query2Results")
Set wksQ3 = Worksheets("Query3Results")

' Create the connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\XMPlatform 5.0.mdb"

' Request ReportID# from user
On Error Resume Next
'Here the type parameter 2 is a text(string)
'0=formula,1=number,4=logicalvalue,8=cellorrange,16=errorvalue,64=array
sReportID = Application.InputBox(prompt:="Enter Report ID#", Type:=1)
If sReportID = "" Then
MsgBox "XM Request Cancelled"
Exit Sub
Else
iReportID = CInt(sReportID)
lReportID = CLng(iReportID)
End If

' Create the SQL statement
szSQL = "SELECT ReportID, Symbol, Description, CouponRate, " & _
"MaturityDate, Quantity, BookValue, MarketValue, TaxableAccount " & _
"FROM tblRequestSpecificsEquitiesAndFunds " & _
"WHERE sReportID = '" & ReportID & "'" & _
"UNION SELECT ReportID, Symbol, Description, CouponRate, " & _
"MaturityDate, Quantity, BookValue, MarketValue, TaxableAccount " & _
"FROM tblRequestSpecificsOTC " & _
"WHERE sReportID = '" & ReportID & "'"""


' Create the Recordset object and run the query
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

'rsData.Filter = "ReportID LIKE 'lReportID'" >> this did not work

' Make sure we got records back
If Not rsData.EOF Then
' Dump the contents of the recordset onto the worksheet.
wksQ1.Range("A2").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Add headers to the worksheet
With wksQ1.Range("A1:B1")
.Value = Array("ReportID", "Symbol")
.Font.Bold = True
End With
' Fit column widths to the data.
wksQ1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Close the recordset if it is still open.
If CBool(rsData.State And adStateOpen) Then rsData.Close
Set rsData = Nothing

End Sub

[RobDog888]

First, the filter should be like this.

rsData.Filter = "ReportID LIKE '" & lReportID & "'"

Assuming that the lReportID contains a wild card character, but I dont think so since you
declare it as a long. If thats the case then you need to use like this (either a % or a * as the wild card char).

rsData.Filter = "ReportID LIKE '" & CStr(lReportID) & "%'"

For the query not returning records, step through your code checking the
values for your vars and if your rs gets populated with records.

HTH

[NeedSomeAnswers]

Did you not say that the 'ReportID' in the table is a number ???

if this is the case then shouldn't the syntax be (nicking robdogg's example here)

SELECT * FROM Table1 WHERE ReportID = " & CInt(txtReportID) & ""

With no single quotes around it, and converting your compare field (in your case the result from your inputbox) to an Integer !

[RobDog888]

That would be correct, but he is trying to do a like filter. You can not append a "%" or
a "*" to a long so thats why I kept it as a string.

[dmoore]

Hello again. I tried both solutions, unsuccessfully at first. I am very new at this. I did manage to use "NeedSomeAnswers" suggestion (...CINT()...) which successfully extracted records from one of the tables according to my input box. THANK YOU! But when I tried to apply that WHERE statement again with a UNION it didn't pull in any records. I am trying to combine two tables with similar fields so I will end up with one list (& I do need two tables because one has a txtDesc that is entered while the other has a txtDesc that is built by the user though a form)

Here's is the code again .. with the help from both of you, it works!
I may run into a performance issue down the road .. but hopefully a real programmer will be doing this then. Thank you so much.

Public Sub QueryForAssetList()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim wkb As Workbook
Dim wksQ1 As Worksheet
Dim wksQ2 As Worksheet
Dim wksQ3 As Worksheet
Dim sReportID As String
Dim iReportID As Integer

' Associate Worksheet Objects with the Worksheet Name that
' will hold the Query Results

Set wksQ1 = Worksheets("Query1Results")
Set wksQ2 = Worksheets("Query2Results")
Set wksQ3 = Worksheets("Query3Results")

' Create the connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\XMPlatform 5.0.mdb"

' Request ReportID# from user
On Error Resume Next
'Here the type parameter 2 is a text(string)
'0=formula,1=number,4=logicalvalue,8=cellorrange,16=errorvalue,64=array
sReportID = Application.InputBox(prompt:="Enter Report ID#", Type:=1)
If sReportID = "" Then
MsgBox "XM Request Cancelled"
Exit Sub
Else
iReportID = CInt(sReportID)
End If

' Create the SQL statement to merge securities data from both tables
szSQL = "SELECT ReportID, Symbol, Description, CouponRate, " & _
"MaturityDate, Quantity, BookValue, MarketValue, TaxableAccount " & _
"FROM tblRequestSpecificsEquitiesAndFunds " & _
"UNION SELECT ReportID, Symbol, Description, CouponRate, " & _
"MaturityDate, Quantity, BookValue, MarketValue, TaxableAccount " & _
"FROM tblRequestSpecificsOTC"


' Create the Recordset object and run the query
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

'Filter szSQL results so that only ReportID from InputBox are returned.
rsData.Filter = "ReportID LIKE " & CInt(sReportID) & ""

' Make sure we got records back
If Not rsData.EOF Then
' Dump the contents of the recordset onto the worksheet.
wksQ1.Range("A2").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Add headers to the worksheet
With wksQ1.Range("A1:B1")
.Value = Array("ReportID", "Symbol")
.Font.Bold = True
End With
' Fit column widths to the data.
wksQ1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Close the recordset if it is still open.
If CBool(rsData.State And adStateOpen) Then rsData.Close
Set rsData = Nothing

End Sub

[RobDog888]

You can either add the where clause to the select union statement or in the filter.

You need to use the wild card character to actually do a like query.

rsData.Filter = "ReportID LIKE " & CInt(sReportID) & "%"