I have a table which has 8 fields. The task is to design a query which always shows the first two fields (or any other two specific fields) and any one of the remaining 6 fields based on the input from the user. I am using a simple form with combo boxes to get the choices from the user.
View RepliesI want to set parameter in my select query with or function as in detail.i have two form with same field
1-with field A
2-With Field A
now i want to select data for a table .with a parameter select with Field A in form 1 or Field A in Form 2.How can i do it
I have a query with a parameter query but what I need is the information to be passed onto another parameter query automatically.
I have a Field called ITEMID and ITEMIDFK
the criteria for ITEMID is [ENTER Item ID Number]
once entered I need ITEMIDFK to use the same information inserted into the first parameter is this possible?
I'm using Access 2003...I have a query that searches a parts table by description:
Like "*" & [Enter in Part Description to search] & "*"
I need to add another search to this query, I added another field to the parts table call manufacturer. I add this field to the above query and added this parameter to it:
Like [Enter in Manufacturer] & "*"
I then added a manufacturer to one of the fields for test purposes. For some odd reason this doesn't work. If I take out the manufacturer parameter and don't enter in anything into the part description the query returns all the records. When i type a description into the part description field the query returns the proper records. When I add back the manufacturer parameter and enter through both parameters only the one record returns showing the test record instead of all of them. If I put a part description in and enter through the manufacturer parameter no records show when they should.
I've tried adding a test field to a different table and tried a different query using the like parameter. The parameter works in a field already in the table but won't work with the new field I added. I've done compact and repair.
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
Is it possible to have a parameter in the field row of a query? That way, the user could select the field they want to query against without going into the design view of the query.
View 9 Replies View RelatedI have parameters set in a query. This query generates a report. I want to have the parameter field the user enters show in the report. Example: I have Region set up as a parameter. The criteria in the query is Like "*" & [Region] & "*" The problem I'm having is in the report it only returns what the user enters in the parameter prompt.
For Example: If I type south it only shows south rather than south east or south central (which is the whole field), etc. on the report. It shows in the table generated by the query but not on the report. If I leave the parameter prompt blank it brings back all the records like it should but again leaves the "region" field on the report blank.
I have a table that has a date column. I am assigning one group of students a test date of 4/29 and another group 4/30. I don't want access to change the column or ask for a parameter during the query, but no matter what I do, it keeps asking me for a parameter. I just want it to use the data that is already in the column.
I tried changing the column name in Excel to DOT from Date. That didn't work.I also tried changing the column to text, so that it wouldn't be a "date." That didn't work.
I have created a query with the parameter for the Domain field. however on the form the user enters this information via a drop down menu. i was just wondering could the parameter box be set to a drop down box as well to save the user entering in the full Domain field name?
View 6 Replies View RelatedHi All
I have set a parameter in a query on a number field.
When I run the query it lists all entries relating to the number I enter.
Once I have ran the query by either opening the query or clicking on the datasheet view when I am in design view, is there a way that I can re-run the query from the datasheet view with a new parameter without having to either close and re-open the query or go into the design view and click on datasheet view again?
Surely there must be a way.
I'd appreciate anybody's help on this.
I have multiple queries that I need to have filered by the same parameter (Client). There is a list of clients that receive this report (the report has a pre-made template with multiple tabs, hence the multiple queries).
Currently the user of the database has to filter the exported excel file for each client re-save and email basied on the client. The above is done when the user pushes the specified button on a form (which just says run report a on it).
I am trying to figure out how the write a code so that a parameter (or table if that would work better) pulls the first record of the Client query to filter the other queries being run. Then to loop that process with the second record and so on through the Client query until all the clients have had the report run for them.
Attached are the current VBA being used and the current SQL for the first query that needs the parameter value added.
can i set a value in report as parameter in query
View 1 Replies View RelatedIt is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.
For instance you may want Access to return all records that begin with the letter Q.
To do this you need to enter the following expression into the criteria row:
Like [Find records beginning with:] & "*" Or Is Null
The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.
By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.
But how do I search if i want all the records with John anywhere in the field???
I have a query where I want to use a combo box in the parameter. However, its not working.
SELECT Call_Details.Emp_ID, Call_Details.[Call Status]
FROM Call_Details
WHERE (((Call_Details.Emp_ID)=[Forms]![TTY_ID]![TTY_ID]));
I get this in the Parameter Value Dialoge
Forms!TTY_ID!TTY_ID
Should I be entering this in the criteria field at all? Do I have to build an event to make this work?
I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)
here's the problem:
when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.
to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100
when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100
some details:
the average field has this code: Average: CInt(Nz([Grade]))
the query looks like this:
SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])), [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
HAVING (((CInt(Nz([Grade])))<[value]));
I'm on access 2007
I need to apply a parameter to a query based on from combo box. I have added the ALL option to the combobox which that the user would select if they want to run the query to show all records;
E.g. Selecting ALL Countries would be the same as having no parameter or a wild card *
ALL Countries
UK
United States
Asia
Europe
I tried an iif statement like:
IIf([forms]![frmSelectReports].[location]="AllCountries",like"*",[forms]![frmSelectReports].[location])
but it didn't work...
I am using the Multi Search from that's available in the template forum, and currently it's working perfectly. I am trying to add one checkbox (chkIncludeManifests) to the form to do some filtering.
When the box is checked, I should search all records. When the box is unchecked, I should search only records with no manifest number [manifestnum].
I've tried setting up an IIF statement to filter out the values that are empty but it's not working.
In Access 2003 I create a query which for one field was link to a value on a form.
Now I have to duplicate this form and instead to duplicate this query I wanted to use a parameter inside this query for one field and then via VBA call this query and give a value for this parameter.
My query name is: Query_Parameter
Fields inside: "Asset_Name" and "Isin"
Parameter is under the "Isin" field: [Look_Isin]
My VBA code is:
Sub Test_Call_ISIN()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb
'Get the parameter query
[Code] ....
When I run the code nothing happen ?
I have always used parameter forms for inputing query/report criteria. However, they have always been for one query or report. However, all searches I have done for trying to use one form for several queries all I find is a simple response of "Create a form and then direct all the queries to grab the parameter from the form.". Sounds simple enough but I can never get it to work.
I have created a macro that runs 12 Append queries. I have also created a parameter form that has an unbound combo box. The combo box is fed by a query that gathers all the Data Dates for the user to select from. So if there are 24 different data dates then the combo box displays them all.
I tried creating a macro that first opens the form then opens the query, however, it runs the query before I can finish entering the criteria in the form. Then I just get a pop up of "You are about to append 0 records..."I have found several walk-throughs for having a query use a form to grab parameter, but have not been able to find one that shows you how to set up one form to run several queries.
I keep trying to find an answer, then give up and continue manually entering 12 dates for 12 queries when they pop up after running the macro. Now I need to create a macro that runs 24 queries and I don't want to have to go through 24 parameter pop-ups.
I hope someone can help as I am stumped at present. I have created a calculated query using dateadd. I have a table that has date fields, ServiceContractAgreed and ServiceFrequency. The service frequency field holds a value of 3,6,9 or 12 in months. Using dateadd I can add the months to the ServiceContractAgreed field in a field called NextService, ie =IIf(IsNull([ServiceContractAgreed]),"N/A",DateAdd("m",[ServiceFrequency],[ServiceContractAgreed]))
This works fine but what I want to do now is create a parameter query to search the NextService field, using the between operator to search between dates. I've created a new query to do this but get every record back, no matter what dates I put in. It's probably something really simple to do but I'd be really grateful for any advice!
Cheers
Dave
I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.
Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.
Private Sub SingleItemRptB_Click()
If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then
MsgBox "Item not in database. Please check value and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
End If
End Sub
I have a form which users can toggle whether they want to only see entries that has data in a certain field. Previously I have set up a separate query with a hardcoded 'Is Not Null' in the criteria, and set the form to call the different queries based on the status of a toggle button. This time there is 22 queries that need to be modified so I'm hoping there's a better way.
I have a hidden textbox on my form that has value "Is Not Null" or Null based on the position of the toggle button.
In my query for the field criteria I have [Forms]![MainForm].[txtCriteria] where txtCriteria is the textbox previously mentioned.
When I run the query no data is shown.
I have a query that is hard coded with a date range.
SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI,
FROM tblCase
WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#))
ORDER BY tblCase.CaseId;
I would like to create a parameter query that allows me to only enter the year and the query would append the rest of the date range. So for example, if I prompt the user to enter the date and they enter 2014, the query would know that it means Between #4/1/2014# And #3/31/2015# or if I enter 2015, it means Between #4/1/2015# And #3/31/2016#.As well the date would need to go into both fields ReqReceived and Letter_AMPI.
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
View 2 Replies View RelatedI'm having a very simple issue (I think) trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:
[Text0]
A1C556CC3C-TNNN
C010070H13
The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"
Code:
test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
[Forms]![Search]![Text0].Value = test
DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly
In the SQL code I use the IN operator to find the exact value for each record:
FindPartNo sql query:
Code:
SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
FROM Classifications
WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);
The problem here is, the query doesn't return results but if I modifiy the query and I put:
Code:
WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");
... the query returns the correct results.I'm passing the value incorrect into the sql code?
I have a database with a Date of Birth field. I have a query with a field that calculates the age from the Date of Birth (DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")
As a criteria in this field I want to be able to select a minimum age, so >=[please input minimum age]
However the results are bizarre - sometimes it gives the right answer, and sometimes not. It seems to have a particular problem with ages above 10, which show up all the time.