creating temporary select queries

[tc3of4]

in access xp
tried using docmd.runsql sqlstring
sqlstring is a valid statement but access keeps giving me error message runsql requiers valid sql statement

i.e.
SELECT Report1_MAY0102_025148.Period, Report1_MAY0102_025148.BN, Report1_MAY0102_025148.Name, Report1_MAY0102_025148.TransType, Report1_MAY0102_025148.Amount, Report1_MAY0102_025148.GrantAmount FROM Report1_MAY0102_025148 WHERE (Report1_MAY0102_025148.Period = 38)

can anybody see anything wrong with this or give me another solution

tried using adodb.command
executed with no problems but didn't show the result...

thanks,
tc3of4

-----

other than that, is there an equivalent to using querydef.execute in Access XP?

[This message has been edited by tc3of4 (edited 05-02-2002).]

 

[tc3of4]

with the above
I binded the sql statement to a string

dim sqlstring as string
sqlstring = "sql statement above"
docmd.runsql sqlstring



[This message has been edited by tc3of4 (edited 05-02-2002).]

 

[David R]

How about
SELECT Period, BN, Name, TransType, Amount, GrantAmount FROM Report1_MAY0102_025148 WHERE Period = 38;

Otherwise, my first inclination is to ask if Report1_MAY0102_025148 is a Report or a table/query.

 

[tc3of4]

I tried it that way but no luck...

 

[David R]

Ooookay...

Since you didn't post any variables in the example, I'm assuming you were giving us an example of one way the sql can be built. If there ARE no variables and that is how it looks in the code, and it works as a query (I know it does, I just tried it), then why not save it as a query and run the query instead of building a new SQL statement every time?

My guess is something's wonky with your quote marks in the variables though. Post your code here and someone can help you debug it.

 

[tc3of4]

okay here it is...

Private Sub RunDynamicQuery()
Dim sPeriod As String
Dim sTransType As String
Dim sStatus As String
Dim sRevFlag As String
Dim sTransOrigin As String
Dim sBN As String

Dim sReport As String
Dim sqlString As String

sPeriod = txtPeriod
sTransType = txtTransactionType
sStatus = txtStatus
sRevFlag = txtReversalFlag
sTransOrigin = txtTransactionOrigin
sBN = txtBusinessNumber

sqlString = "SELECT " & cmbTableFile & ".Period, " & cmbTableFile & ".BN, " & cmbTableFile & ".Name, "

txtReportToRun.SetFocus
sReport = txtReportToRun.Text
' Concatenate the rows to be shown
Select Case sReport
Case "Report1"
sqlString = sqlString & cmbTableFile & ".TransType, " & cmbTableFile & ".Amount, " & cmbTableFile & ".GrantAmount "
Case "Report2"
sqlString = sqlString & cmbTableFile & ".TransType, " & cmbTableFile & ".Status, " & cmbTableFile & ".ReversalFlag, " & cmbTableFile & ".BnCount "
Case "Report3"
sqlString = sqlString & cmbTableFile & ".TransType, " & cmbTableFile & ".Status, " & cmbTableFile & ".BnCount, " & cmbTableFile & ".Contribution, " & cmbTableFile & ".GrantAmount, " & cmbTableFile & ".EAPAmount, " & cmbTableFile & ".EAPGrantAmount, " & cmbTableFile & ".PSEAmount "
Case "Report4"
sqlString = sqlString & cmbTableFile & ".ErrCount "
Case "Report5"
sqlString = sqlString & cmbTableFile & ".TransType, " & cmbTableFile & ".BnCount "
Case "Report6"
sqlString = sqlString & cmbTableFile & ".RecordCount "
Case "Report7A"
sqlString = sqlString & cmbTableFile & ".TransOrigin, " & cmbTableFile & ".TransType, " & cmbTableFile & ".ReversalFlag, " & cmbTableFile & ".BnCount "
Case "Report7B"
sqlString = sqlString & cmbTableFile & ".TransOrigin, " & cmbTableFile & ".ReversalFlag, " & cmbTableFile & ".BnCount "
Case "Report8"
sqlString = sqlString & cmbTableFile & ".Amount "
Case "Report9"
sqlString = sqlString & cmbTableFile & ".Amount "
End Select

sqlString = sqlString & "FROM " & cmbTableFile & _
" WHERE (" & cmbTableFile & ".Period = " & sPeriod & ")"

' Concatenate the And Clause
If txtTransactionType.Enabled Then
txtTransactionType.SetFocus
If txtTransactionType <> "*" Then
sqlString = sqlString & " AND " & cmbTableFile & ".TransType = '" & txtTransactionType.Text & "'"
End If
End If
If txtStatus.Enabled Then
txtStatus.SetFocus
If txtStatus <> "*" Then
sqlString = sqlString & " AND " & cmbTableFile & ".Status = '" & txtStatus.Text & "'"
End If
End If
If txtReversalFlag.Enabled Then
txtReversalFlag.SetFocus
If txtReversalFlag <> "*" Then
sqlString = sqlString & " AND " & cmbTableFile & ".ReversalFlag = '" & txtReversalFlag.Text & "'"
End If
End If
If txtTransactionOrigin.Enabled Then
txtTransactionOrigin.SetFocus
If txtTransactionOrigin <> "*" Then
sqlString = sqlString & " AND " & cmbTableFile & ".TransOrigin = '" & txtTransactionOrigin.Text & "'"
End If
End If
If txtBusinessNumber.Enabled Then
txtBusinessNumber.SetFocus
If txtBusinessNumber <> "*" Then
sqlString = sqlString & " AND " & cmbTableFile & ".BN = '" & txtBusinessNumber.Text & "'"
End If
End If

'Dim cnn As New ADODB.Connection
'Dim cmd As ADODB.Command
'Set cnn = CurrentProject.Connection
'Set cmd = New ADODB.Command
'With cmd
' .CommandText = sqlString
' .ActiveConnection = cnn
' .Execute
'End With
'Set cmd = Nothing
'Set cnn = Nothing
Debug.Print sqlString
DoCmd.RunSQL sqlString
End Sub

--> sqlstring will result into the string you see from the first post --> because of the criterias I chose

thanks for looking at the code david
tc3of4


The funny thing is when I copy the output from the debug (the sql string) and paste it in a new query (using the sql) it runs... but for some reason running dynamic queries through code is not working...
it is not recognizing the sql statement
-------
error i received
[Run-Time Error '2342'
A RunSQL action requires an argument consisting of an SQL statement.]

[This message has been edited by tc3of4 (edited 05-02-2002).]

 

[David R]

If it runs when you paste it into a query, how about saving it as DynQuery and running it with DoCmd.OpenQuery "DynQuery"? Jack Cowley showed me how to do this recently, and it might solve your issue, since I don't know jack (no pun intended) about RunSQL: http://www.access-programmers.co.uk/ubb/Forum3/HTML/003504.html

 

[tc3of4]

thanks david,
guess i'm going to have to use ADO and DAO together on this one

tc3of4