Query Input Must Contain At Least One Table Or Query
Aug 22, 2006
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;"
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID UNION SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet) Set db = wrkJet.OpenDatabase("DW", _ dbDriverNoPrompt, True, _ "ODBC;DATABASE=DW;DSN=DW2") 'Set rs1 = db.OpenRecordset(strSQL)
All I'm trying to do is a make table (table name to overwrite: "TBL_5_7_RFCs") query via VBA so I can loop through a set of VARs in a table. Here is my code:
Code:
Public Function test_sql() Dim Conn As ADODB.Connection Dim RS As New ADODB.Recordset Dim MySQL As String Set Conn = CurrentProject.Connection RS.ActiveConnection = Conn
[code]...
I'll add the loop and array once I get this working but I'm stuck.
I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions?
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
- text box for user to enter EMPID, txtEMPID - LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field... - if the user enters an EMPID that is in the TABLE...display "Y" - if the user enters an EMPID that is NOT in the table...display "N" - a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
I have an App that runs a few action queries using:
Code: CurrentDb.Execute "[My Query Name]"
At some point I get this Error: The Microsoft Access database engine cannot find the input table or query <name>. Make sure it exists and that its name is spelled correctly. (Error 3078).The query is there, I can run it from the DB objects window.Queries run using CurrentDb.Execute earlier in the code.
table name : Schedule Field 1 = Vessel code Field 2 = Voyage Field 3 = ETA Field 4 = berthed Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.
Table 1 has information with all clients query 1 has x, y, z being pulled > client used as parameter query 2 has the list of clients
Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.
Code: Private Sub CmdInternalReports_Click() Dim xlApp As Excel.Application Dim rstDetails As DAO.Recordset Dim strTab As String Dim strDir As String Set xlApp = Nothing Set rstDetails = Nothing
[code]...
There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.
PHP Code:
tblClientReport.Distro
Is where I have the report via client name linked the the distro list that should be pulled.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
[Enter oner1, such as m20] Or [Enter oner2, such as m20] Or [Enter oner3, such as m20] Or [Enter oner4, such as m20]
Basically, it pops up one window after another as you input your info.' Then after the four pop ups it will go and retrieve the needed information. It works great for me....
Is there a way for me to take this query script and if I leave one pop up blank it would just go and find what I put in without having to press "enter" for the additional inputs that I did not want... It's a pain when I increase this to 10 pop ups and one need one...
Example :
Lets say I only want to input two and then go find the information. right now I have to continue closing the pop ups until I have cycled through all four and then it will go. I want to eliminate the additional ones.
I'm not a genius at this so please make it as easy as you can if you have any ideas....
I have an InputBox date box created in a query, How would i change the title of the input box from "Enter parameter value" to "Works order Import selection". iS IT POSSIBLE
i got a input query problem ! If input a Product model LU20XT-1V25DM , the report should list all the parts which need to assemble this model , and the problem is there are tables in this database , Finding LU in table 1 , 20 in table 2, Xin table 3, DM in table 10, something like that . so input LU20XT-1V25DM , THE Result should like this : No CODE DESCRIPTION SPEC QTY
Can someone clue me in on this? I have a query that links a bunch of tables how can I get a form to show all these fields and input new data. In the past I could always look at data but never add. Is there anyway to do this. I have added the database just in case in need to see what I was talking about.
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
I am trying to produce a query that will search for multiple records by job id entered into a text box eg 17656,18768,15679.
The example i have found uses the (LIKE '*value*') so if i type in part of a job id (176) it will display all records starting with 176. I would like to enter specific job id numbers seperated by a , and only display these
Is it possible to customise the input form for a query. At present I have to type in a start and end date manually, it would be far easier to use a popup calendar to select dates. I also have another query that requires you to type in a username, it would be preferential to have a drop down list to select a name from.
Are either of these things possible? or will I need to create a form that calls the query and passes the relevant data?
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
I am not a programmer and I don't know VBA/VB. I am using this as a workaround to avoid VBA functions (since I don't know them). I can't seem to find a simple? solution to this. I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
The query below is supposed to accept some characters from the user and bring up a list of records satisfying the criteria. TelephoneAAA is a table with many columns. The query does not give any error message receives input from user runs quietly and does not give any result. I know that the table contains sufficient data. Probably I am missing something obvious but what.
Code: RowSourceQy = " SELECT * from [TelephoneAAA] " & _ "WHERE (([TelephoneAAA].[SOYADI]) Like ((' * ')+ [Word] +(' * '))) OR " & _ "(([TelephoneAAA].[ADI]) Like ((' * ')+[Word]+(' * '))) OR " & _ "(([TelephoneAAA].[ADRES]) Like ((' * ')+[Word]+(' * ')))" & _ "ORDER BY [TelephoneAAA].[SOYADI], [TelephoneAAA].[ADI], [TelephoneAAA].[TEL];"
I am trying to use a combobox to select a value which then activates a query to return results.
Here is some details
I have to tables, one called "Customers" and the other called "Calls". These two tables are linked.
The customers is literally a list of customers with their contact details, but all I am in interested in is the "Company" Field.
The Calls table has a field called "End User" which looks up the company from the Customers Table.
What I am trying to do is create a search by Customer query, furthermore, I would like to do is to create a form with a drop down that looks up from Customers table, select the company and it returns all the records with that company...
What I have done
I have created a query that has the customer and calls tables included, I have dragged down the [Company] from customer table and then all the fields from the Calls table.
Then i created a blank form, inserted a combobox - Combo7, linked the box to the Customers table.
Back to the query, under the [Company] I have put into the criteria the following
Forms!sc!Combo7
Back to the form, selected the combo box, built a macro in the AfterUpdate, to run the query.
Tested this and it does not bring anything back, however if I put into the criteria Like [Please Enter Company Name], then typed the company name, it brings back all the records for that customer.
Am I missing something?, do I need to set the form control to the query, or even the combobox....
We have employee dates of birth in our database. For a birthday gift exchange program, I want to generate a query that returns only the day and the month of the employee birthday (to placate those employees who wish not to disclose their exact age). I'm struggling with making a custom date input mask that will do this.
I have a database that has several tables, each table has account numbers in them. I also have queries set for each table to bring in desired information. Is it possible to set up a form so the user can input an account number and depending on which table the account number is in, that query will run?
