SQL statement not returning information

[trezraven]

I have created a form using Word 2007 that queries an Access database. The problem is my SQL statement isn't working. For some reason, my entire SQL statement is being skipped and my error message is coming up. After I click ok for the error message I get my search is complete message. However, nothing is being returned. I have used a similar statement with another form and the only thing that changed was the name of the fields and the view I was pulling the information from.

Private Sub btnGetData_Click()
Dim conn                As New ADODB.Connection
Dim rs                  As New ADODB.Recordset
Dim lngConnectionState  As Long
Dim strSQL              As String
Dim ntable              As Table

'*****Set up the connection to the database*****
conn.ConnectionString = "Provider=MSDAORA; Data Source=TSD1; User ID=omitted for security; Password=omitted for security"

'*****Open the connection to the database*****
conn.Open
Set rs = New ADODB.Recordset

'*****Check the state of the database*****
lngConnectionState = conn.State

'*****Set the datasource*****
strSQL = "Select Distinct Appellant, Appellee, Opinion_Date, CaseNo, Rehearing_Filed_Date, Rehearing_Granted_Date, Rehearing_Denied_Date, Date_Opinion_Release " & _
         "From CMS.V_Macro4westform " & _
         "Where Opinion_Date between to_date('" & IIf(IsNull(FirstDCAOpinion.txtStart.Value), #1/1/100#, FirstDCAOpinion.txtStart.Value) & "', 'mm/dd/yyyy') and to_date('" & IIf(IsNull(FirstDCAOpinion.txtEnd.Value), #12/31/9999#, FirstDCAOpinion.txtEnd.Value) & "', 'mm/dd/yyyy')" & _
         "Or CaseNo Like '" & IIf(IsNull(FirstDCAOpinion.txtCaseNumber.Value), "*", FirstDCAOpinion.txtCaseNumber.Value) & "'" & _
         "Order by Appellant "
            
'*****Open the recordset*****
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

'*****Get the data if not end of the recordset*****
If rs.EOF Then
MsgBox "No information in the database! Please verify your case number or opinion date.", vbCritical, "ERROR!"
End If

rs.MoveFirst
If Not rs.EOF Then
    Do Until rs.EOF
FirstDCAOpinion.txtAppellant = rs.Fields("Appellant").Value & " "
FirstDCAOpinion.txtAppellee = rs.Fields("Appellee").Value & " "
FirstDCAOpinion.txtCaseNumber = rs.Fields("CaseNo").Value & " "
FirstDCAOpinion.txtOpinionDate = rs.Fields("Opinion_Date").Value & " "
FirstDCAOpinion.txtPubReleased = rs.Fields("Date_Opinion_Release").Value & " "
FirstDCAOpinion.txtRehearingDenied = rs.Fields("Rehearing_Denied_Date").Value & " "
FirstDCAOpinion.txtRehearingFiled = rs.Fields("Rehearing_Filed_Date").Value & " "
FirstDCAOpinion.txtRehearingGranted = rs.Fields("Rehearing_Granted_Date").Value & " "
FirstDCAOpinion.txtDate = rs.Fields("Date_Opinion_Release").Value & " "
FirstDCAOpinion.txtStart.Value = " "
FirstDCAOpinion.txtEnd.Value = " "

[COLOR=red]***omitted other code for formatting my table***[/color]

Selection.EndKey Unit:=wdStory
        Selection.TypeParagraph
        Selection.TypeParagraph
        rs.MoveNext
End With
Loop
End If

Set ntable = Nothing

rs.Close
conn.Close

'*****Search complete message*****
MsgBox "The seach is complete.", vbOKOnly
End Sub

I guess I just need a new set of eyes to look over my code. Any assistance will be greatly appreciated.

Thanks!!!

 

[trezraven]

Does it matter that the strSQL works in another view where the date is stored as 5/2/2006 as opposed to being stored as 02/01/2007 in the veiw I am having the problem with?

 

[jrbarnett]

I've not had a chance to look at Word 2007 yet, but here are a few pointers:

1. If you run that SQL statement from the query window (create a new query, SQL view, paste the code), does it return data?
If yes, then the problem is in your code. If no, then the problem is in the VBA code.

2. You seem to connect through a data source called TSDA1, but this seems to be using an Oracle provider (also partly hinted at by the to_date function in the SQL statement).
If connecting to an Access database, then you need not use the Oracle provider, and can connect straight to an Access database.

3. At the top of the function, you have the line:
Dim rs As New ADODB.Recordset
yet you also have
Set rs = New ADODB.Recordset

Either this latter one can be removed or the word "New" can be removed from the first line.

Hope that this gives you a few hints to get started.

John

 

[pfetzer]

DAO/ Older versions of the Jet database engine use the asterix as the wild card character, however Jet 4, SQL Server and Oracle databases use the percentage character. Can you try

IIf(IsNull(FirstDCAOpinion.txtCaseNumber.Value), "%",

instead of

IIf(IsNull(FirstDCAOpinion.txtCaseNumber.Value), "*",

 

[mikej336]

I would also add

debug.print strSQL

before the

rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

then verify your sql by doing a copy and paste your query in a query window and run it that way.

Uncle Mike