Query Input Must Contain At Least One Table or Query

[Michael J Ross]

Hi,

I'm trying my first query on the fly and am getting the above message when I try and build the query.

Ive posted the SQL below, can anyone tell me where I'm going wrong?

Private Sub Command24_Click()
Dim dbsCurrent As Database
Dim qdf As QueryDef
Dim strSQL As String
Set dbsCurrent = CurrentDb
Set qdf = dbsCurrent.QueryDefs("qrycurrentinvs")



Dim VendId As String

VendId = "Like '" & Me.Text0.Value & " *' "



qdf.SQL = "SELECT dbo_pmt_rqst.[vendor_id], dbo_pmt_rqst.[vendor_loc_code], dbo_pmt_rqst.[pmt_rqst_nbr], dbo_pmt_rqst.[pmt_rqst_date], dbo_pmt_rqst.[payable_entity_id], dbo_pmt_rqst.[ctrl_grp_id], dbo_pmt_rqst.[ctrl_grp_date], Sum(dbo_pmt_rqst_tax.[tax_basis_amt]) AS Net, Sum(dbo_pmt_rqst_tax.[tax_amt]) AS VAT, Sum([tax_basis_amt]+[tax_amt]) AS Gross, dbo_pmt_rqst.[pmt_ref_nbr], dbo_pmt_rqst.[pmt_ref_date] INTO tblinvoices" & _
"FROM dbo_pmt_rqst INNER JOIN dbo_pmt_rqst_tax ON (dbo_pmt_rqst.pmt_rqst_date = dbo_pmt_rqst_tax.pmt_rqst_date) AND (dbo_pmt_rqst.pmt_rqst_nbr = dbo_pmt_rqst_tax.pmt_rqst_nbr) AND (dbo_pmt_rqst.vendor_loc_code = dbo_pmt_rqst_tax.vendor_loc_code) AND (dbo_pmt_rqst.vendor_id = dbo_pmt_rqst_tax.vendor_id)" & _
"WHERE dbo_pmt_rqst.vendor_id" & VendId & _
"GROUP BY dbo_pmt_rqst.vendor_id, dbo_pmt_rqst.vendor_loc_code, dbo_pmt_rqst.pmt_rqst_nbr, dbo_pmt_rqst.pmt_rqst_date, dbo_pmt_rqst.payable_entity_id, dbo_pmt_rqst.ctrl_grp_id, dbo_pmt_rqst.ctrl_grp_date, dbo_pmt_rqst.pmt_ref_nbr, dbo_pmt_rqst.pmt_ref_date, dbo_pmt_rqst.pmt_rqst_date, dbo_pmt_rqst_tax.tax_basis_amt, dbo_pmt_rqst.pmt_ref_date;"

DoCmd.OpenQuery "qrycurrentinvs"


End Sub

Thanks

 

[KeithG]

What is the error message?

 

[grnzbra]

You might want to try

VendId = " Like '" & Me.Text0.Value & " *' "

Note space before the word "Like".

As you have it, your WHERE clause will be

"WHERE dbo_pmt_rqst.vendor_idLike '" & Me.Text0.Value & " *' "

 

[Michael J Ross]

Keith,

The error message i'm getting is "Query Input Must Contain At Least One Table or Query " when i click the button to run the query. On the debug it stops at the line starting qdf.SQL =

Thanks for that grnzbra I shall amend it tomorrow when I get to work and see if it makes any difference.

 

[grnzbra]

you might want to declare a string variable and set it equal to your SQL statement and then set the qdf.SQL equal to this variable. That way you can stop the code before it blows up and take a look at your SQL statement in the immediate window.

 

[pbaldy]

You have the same space problem that grnzbra mentioned in several places. Review the whole thing with that in mind (and the suggestion of examining the string in the immediate window is an excellent one).

 

[grnzbra]

Do you really want the space ahead of the wildcard in the WHERE clause?

 

[Michael J Ross]

Thanks guys, sorting out the spaces has solved it, as there was no space before FROM it wasn't selecting the tables. Also thanks for tthe tip about using the immediate window it helps a lot.