Solved

Access query - 'enter parameter value'

Posted on 2014-10-15
17
334 Views
Last Modified: 2014-10-15
Dear experts - I am trying to create a query where the user may enter one of several criteria. The problem is that each of these criteria hang off a different table.
Here is the query graphically - I add the DISTINCT keyword, since there are multiple related records in several of the tables:


query
NOW - I am creating a string, which, for example, includes this:


If Nz(Me!cboProject_ID, 0) <> 0 Then
     strFilter = strFilter & " And projects.project_ID =" & Me!cboProject_ID
End If


NOTE that there is no field in the results called 'project_ID' - I am actually able to run the query as a standalone and it produces perfect results:
SELECT DISTINCT Vendors.Vendor_ID, Vendors.VendorCode, Vendors.CompanyName, Vendors.Forename, Vendors.Surname, Vendors.Country_ID, Vendors.Tel, Vendors.Cell, Vendors.Email, Vendors.Status, Vendors.Agency, Vendors.LinguisticVendor, Vendors.MedicalVendor, Vendors.Eval_Alert, Vendors.LanguageSource_ID, Vendors.LanguageTarget_ID, Vendors.Language_Details, Vendors.ScreenInitialEval, Vendors.ScreenBy, stripaccents(Nz([vendors].[companyname],"")) AS strippedco, stripaccents(Nz([vendors.forename],"")) AS strippedfirst, stripaccents(Nz([vendors.middlename],"")) AS strippedmiddle, stripaccents(Nz([vendors.surname],"")) AS strippedlast, PICK_Countries.abbreviation AS homecountry, source.abbrev AS source, target.abbrev AS target, source.language_ID AS SourceLangOnly_ID, target.language_ID AS TargetLangOnly_ID, IIf([EVAL_ALERT]=1,"RED FLAG",IIf([eval_alert]=2,"AMBER","")) AS Alert, Switch([Vendors].[status]=1,"Active",[Vendors].[status]=2,"Inactive",[Vendors].[status]=3,"Decommissioned (Inactive)") AS StatusText, GetSubjects([Vendors].[Vendor_ID]) AS Subjects, GetActions([Vendors].[Vendor_ID]) AS Actions, qryVendorActionCount_Last6mos.countofexpense_ID AS CountOfExpense_ID
FROM Projects RIGHT JOIN (Project_Parts RIGHT JOIN (((((Vendors LEFT JOIN PICK_Countries ON Vendors.Country_ID = PICK_Countries.ID) LEFT JOIN qryVendorActionCount_Last6mos ON Vendors.Vendor_ID = qryVendorActionCount_Last6mos.Person_ID) LEFT JOIN LanguagePlusLocale_byID AS source ON Vendors.LanguageSource_ID = source.LanguagePlusLocale_ID) LEFT JOIN LanguagePlusLocale_byID AS target ON Vendors.LanguageTarget_ID = target.LanguagePlusLocale_ID) LEFT JOIN Project_Lines_Expenses ON Vendors.Vendor_ID = Project_Lines_Expenses.Person_ID) ON Project_Parts.Part_ID = Project_Lines_Expenses.Part_ID) ON Projects.Project_ID = Project_Parts.Project_ID
WHERE (((Projects.Project_ID)=2000));

Open in new window


Now, I run the above query WITHIN THE FORM, and I get a ENTER PARAMETER  ('projects.project_ID') error.

error message
Any guidance is greatly appreciated.
0
Comment
Question by:terpsichore
  • 7
  • 6
  • 4
17 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381809
The query itself looks OK.

Therefore, my best guess is that one of the earlier queries to which this is linked uses "projects.project_ID" incorrect.

One of the reasons I say this is that the above screenshot uses "projects.project_ID" (lower case), whereas your query says "Projects.Project_ID" (upper case).

Try opening each of the queries on which this depends. This is especially true if any of the earlier queries are crossqueries.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40381825
I would be inclined to agree with Phillip, to start your investigation with those other queries.  However, you state:

"Now, I run the above query WITHIN THE FORM, and I get a ENTER PARAMETER  ('projects.project_ID') error."

How are you running this query "within the form"?  Are you attempting to open a recordset?  Have you reformatted the query as a sql string?
0
 

Author Comment

by:terpsichore
ID: 40381843
in the VBA, I assemble a string - when I output the total string in the immediate window, it displays:

SELECT qryVendorDashboard1.* FROM qryVendorDashboard1  WHERE [LinguisticVendor]=True And [vendors].[Status]=1 And [projects].[project_ID] =4437 ;


which looks ok to me -
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381851
Not to me it doesn't - you don't have the projects table in the FROM section of the string query.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40381953
does "qryVendorDashboard1" contain a parameter definition (either in the Criteria of the query itself, or a defined parameter)?

You indicate when you run it from the query designer it works fine, which implies that it asks you for that parameter.  If you remove that parameter from the query itself, then it should run fine.
0
 

Author Comment

by:terpsichore
ID: 40382025
1) [for Mr. Burton] The Projects table IS in the underlying query - see the definition in the original question - this query is called qryVendorDashboard1.
2) [for Mr. Rye] The query does not contain a parameter definition; also, when i run it from the query designer it doesn't ask me for that, i had put it in manually into there query (unchecking the checkbox so it is only 'where' and not output as a field).
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40382046
You said:

in the VBA, I assemble a string - when I output the total string in the immediate window, it displays:

SELECT qryVendorDashboard1.* FROM qryVendorDashboard1  WHERE [LinguisticVendor]=True And [vendors].[Status]=1 And [projects].[project_ID] =4437
;

qryVendorDashboard1 does not include in the SELECT statement the field [projects].[project_ID]; the only place it is contained in is in the FROM and WHERE. Therefore, qryVendorDashboard1 does not include as part of the output [projects].[project_ID].

In any case, even if it did, what you are doing would not make sense, because in qryVendorDashboard1 you want only those rows where (Projects.Project_ID)=2000, and now you want [projects].[project_ID] =4437. That would be zero rows.
0
 

Author Comment

by:terpsichore
ID: 40382063
1) Sorry, I had that where clause in the query temporarily. the query reads like this:


SELECT DISTINCT Vendors.Vendor_ID, Vendors.VendorCode, Vendors.CompanyName, Vendors.Forename, Vendors.Surname, Vendors.Country_ID, Vendors.Tel, Vendors.Cell, Vendors.Email, Vendors.Status, Vendors.Agency, Vendors.LinguisticVendor, Vendors.MedicalVendor, Vendors.Eval_Alert, Vendors.LanguageSource_ID, Vendors.LanguageTarget_ID, Vendors.Language_Details, Vendors.ScreenInitialEval, Vendors.ScreenBy, stripaccents(Nz([vendors].[companyname],"")) AS strippedco, stripaccents(Nz([vendors.forename],"")) AS strippedfirst, stripaccents(Nz([vendors.middlename],"")) AS strippedmiddle, stripaccents(Nz([vendors.surname],"")) AS strippedlast, PICK_Countries.abbreviation AS homecountry, source.abbrev AS source, target.abbrev AS target, source.language_ID AS SourceLangOnly_ID, target.language_ID AS TargetLangOnly_ID, IIf([EVAL_ALERT]=1,"RED FLAG",IIf([eval_alert]=2,"AMBER","")) AS Alert, Switch([Vendors].[status]=1,"Active",[Vendors].[status]=2,"Inactive",[Vendors].[status]=3,"Decommissioned (Inactive)") AS StatusText, GetSubjects([Vendors].[Vendor_ID]) AS Subjects, GetActions([Vendors].[Vendor_ID]) AS Actions, qryVendorActionCount_Last6mos.countofexpense_ID AS CountOfExpense_ID
FROM Projects RIGHT JOIN (Project_Parts RIGHT JOIN (((((Vendors LEFT JOIN PICK_Countries ON Vendors.Country_ID = PICK_Countries.ID) LEFT JOIN qryVendorActionCount_Last6mos ON Vendors.Vendor_ID = qryVendorActionCount_Last6mos.Person_ID) LEFT JOIN LanguagePlusLocale_byID AS source ON Vendors.LanguageSource_ID = source.LanguagePlusLocale_ID) LEFT JOIN LanguagePlusLocale_byID AS target ON Vendors.LanguageTarget_ID = target.LanguagePlusLocale_ID) LEFT JOIN Project_Lines_Expenses ON Vendors.Vendor_ID = Project_Lines_Expenses.Person_ID) ON Project_Parts.Part_ID = Project_Lines_Expenses.Part_ID) ON Projects.Project_ID = Project_Parts.Project_ID;

Open in new window


2) More importantly, it was my understanding that the field project_id did NOT have to be in the output - and I have tested this independently of the VBA code and it seems to work just fine.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40382068
It does not need to be in the output, unless you refer to it later (which you are doing), or you have a JOIN to it later.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40382080
As you indicated here, you "unchecked" the Project_ID field so that it would not display in the query.

In order to use that parameter in your VBA SQL statement, the query must include that field.
0
 

Author Comment

by:terpsichore
ID: 40382138
I guess I'm still confused - the joins are defined in the query, and that includes the table projects. If i can run a query using a where clause but not including it as an output field, I guess I'm confused as to why this wouldn't work programmatically using VBA...
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40382148
It's nothing to do about whether you can do it programmatically using VBA.

You are using the Project_ID field in Query1. It works in Query1. You happen to be using it in the FROM and WHERE, but not the SELECT. Therefore, Query1 does not output the Project_ID field.

You then develop Query2. You expect Query2 to use Query1. You expect Query2 to see the Project_ID field from Query1. However, Query1 does not output the Project_ID field. Therefore, Query2 cannot see it in Query1.

It would be exactly the same if you were writing a new query or doing it in VBA.
0
 

Author Comment

by:terpsichore
ID: 40382240
I'm still confused - it's the same query, isn't it? the query I pasted directly above qryVendorDashboard1 is the same query i'm invoking in VBA. That query has the projects table in the linkage; it doesn't output the project_ID field, that's correct.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40382263
It's not the same query.

Let's try this another way:

Projects.Project_ID goes into Query1 (it is in the FROM).
Projects.Project_ID does not go out of Query1 (is it not in the SELECT).
Therefore, Query2 cannot reference Projects.Project_ID from Query1, because it does not go out of Query1.
0
 

Author Comment

by:terpsichore
ID: 40382321
ok - maybe that is the best solution - instead of using a defined query, I will simply put the whole SQL into the VBA, that way there is no issue.
Thanks - this has led me to a simple solution.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40382352
@terpsichore,

You need to understand this concept!

When you create a saved query, in order to use that query as the source of another query, and set a criteria at run time, you must include all of the fields in the saved query that will be referenced in the criteria you create at run time.

However, depending on what you want to do with that SQL string you created at run time, you might be able to save the query (query1) as a parameter query, where you include the parameter [Enter Project ID] as a defined parameter, and set the criteria of that query just like you did when you ran it from the query grid:

WHERE [Projects].Project_ID = [Enter Project ID]

Then, in your code, if you wanted to create a recordset based on that query, you would do something like:

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim rs as DAO.Recordset

Set db = currentdb
set qdf = db.querydefs("yourQueryName")
qdf.Parameters("Enter Project ID") = 4437

set rs = qdf.openrecordset
0
 

Author Comment

by:terpsichore
ID: 40382531
much appreciated Dale!!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now