Select Statement

[nhtraven]

Hi,

This is what i am trying to do. I have a form that i am setting up to run a query by command button, depending on the fields chosen in the form.

i need to use fields from two tables in this form. In the sql code, I used a string variable to hold the table name then insert it in select statement. I may be way off on this i am still a newbie in sql.

the code reads:

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Dim tblname1 As String
Dim tblname2 As String

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query&quot

On Error GoTo 0

where = Null

'InputData Table info

where = where & " and [tblInputData].[ParcelNo] = ' " + Me![ParcelNo] + "'"
tblname1 = tblInputData

' many more fields, each with the tblname1 / tblname2 stating which table is used

If Left(Me![ParcelNo], 1) = "*" Or Right(Me![ParcelNo], 1) = "*" Then

where = where & " AND [tblInputData].[ParcelNo] like ' " + Me![ParcelNo] + "'"

Else
where = where & " AND [tblInputData].[Parcelno] = ' " + Me![ParcelNo] + "'"
End If
tblname1 = tblInputData


' many more numeric ranges with same set up


' I cannot get this statement correct >> i need to add the tblname variables and am not sure how to go about it????
MsgBox "Select * from" tblname1 & ("where" + Mid(where, 6) & ";&quot

'Set QD = db.CreateQueryDef ("Dynamic_Query&quot, select * from" tblname1 & (" where " + Mid(where, 6) & ";&quot)


DoCmd.OpenQuery "Dynamic_Query"


End Sub


WIth the last bit of code from the Message box on is not compiling correctly

the error is expected end of statement

ANy ideas????
THanks in advance

 

[ToeShot]

I am not sure what your acheivments are so I adjusted the code to what I saw. If I am wrong I am sorry.

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim strWhere As String
Dim tblname1 As String
Dim tblname2 As String

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query&quot

On Error GoTo 0


'Step one set your table variables
tblname1 = Me.{Name of field you are using}
tblname2 = Me.{Name of field you are using}

'Step two set your where variale
If (Left(Me![ParcelNo], 1) = "*" Or Right(Me![ParcelNo], 1) = "*&quot Then
strWhere = "WHERE " & [tblInputData].[ParcelNo] & " Like '" & Me![ParcelNo] & "';"
Else
strWhere = "WHERE " & [tblInputData].[Parcelno] & " = '" & Me![ParcelNo] & "';"
End If

'Step three Construct the sql statement
If tblname1 <> &quot;&quot; Then
Set QD = &quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & strWhere
Else
Set QD = &quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname2 & &quot; &quot; & strWhere
End If

'Step Four run the querydef
DoCmd.OpenQuery &quot;Dynamic_Query&quot;

End Sub

As for the step three I am not sure if you are trying to get the user to type it in from a pop up box or not
if you are trying to get the user input you might try this

Dim tblname As String

tblname = InputBox(&quot;What table do you want to use for this query?&quot;,&quot;Select table&quot;,{Table Name})

If (tblname= &quot;&quot Or (vbCancel = True) Then
MsgBox &quot;No Table Selected. Action Canceled&quot;,,&quot;Action Canceled&quot;
Exit Sub
End If

Set QD = &quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname & &quot; &quot; & strWhere

Add The Dim statement with your other Dim statements
and put the rest of the code in step three to replace what is there.

HTH

If you need more help email ToeShot@Hotmail.com

 

[nhtraven]

Thanks ToeShot,

I am not sure which of the questions i have posted you'll see first, this is the second one i am answering back to you.

my question is this. If i make a blank Query and base my form on that, would that be better than this?

and how do i get all of the fields to return after query is run ie, in the form, new popupform with results, I am not sure.

Thanks

 

[nhtraven]

i tried your code and get an error message that says expected end of sentence. i have worked with this for hours and cannot find the answers in FAQ, or Keyword search. Or online SQL tutorials. lol microsoft.com, and access help.

the cursor goes to the first comma after dynamic query

If tblname1 <> &quot;&quot; Then
Set QD = &quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & strWhere
Else
Set QD = &quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname2 & &quot; &quot; & strWhere
End If
THanks for help = )

 

[ToeShot]

Sorry I forgot the Apprethisies

If tblname1 <> &quot;&quot; Then
Set QD = (&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & strWhere)
Else
Set QD = (&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname2 & &quot; &quot; & strWhere)
End If


What I would do is first creat a query that contains the tables and fields I need to return the results.

Then I would create a form with that query as the record source

Next determine what fields are going to be needed to be input by the user. Once that is done I would change thos fields to a ComboBox so there will be no typing errors. Then go back to the query and in the criteria section of the query point to the ComboBoxes like so: [Forms]![Form Name]![ComboBox Name]

Note this will only work if all the fields you point to in your query, actually contain data.

If you need more help maybe you could zip the your db and send it to me and I could make it for you then you could see what I am talking about.

HTH