Queries :: Adding Test Field To Different Table And Try Different Query Using Like Parameter
Mar 20, 2013
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.
my issue is i have multilble text box in my form & based on change in one of this text box i need the code to compare between data in the form & table & returm Msg if it is not matching. attached screen FYI.
i look in the internet but i could not figuer out the VBA code since i do not know VBA. what comes to my mind to to use select case.
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.
I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:
Code:
Select .*, [here the new field] From MyTable1 Union Select .*, [here the new field] Form MyTable2
The Union statement isn't really relevant, just for completion's sake.
I have a query all set up and now I have to add one field from another table in it. I am looking for a date which has the criteria Now() - Last Movement Date. Last Movement Date is the column I am taking from the other table which I just added which is the ZLX02 table. When I run the query, everything but the Last Movement Date shows up. What can I do to get the Last Movement Date to show? Check out the attached pics.
I have a parameter that I need to get a table contents between dates. In the query:
Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]
When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables
I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.
There is a table with many columns. I want to add many rows to this table. The easiest way is to write a query to do that. But most of the columns have validation criteria, lookup tables, default values, nulls not accepted condition and what not. Where as my new records to be added have only the values in 4 columns and the rest are null. Is there a parameter that can be used to bypass all these controls and add the records? You are going to say "You will destroy the integrity and consistency of the database".
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.
I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.
So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.
My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:
The amount sent on time / the total amount * 100
But is there anyway to add this into a 4th column displayed next to these figures?
Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?
I have a make table query created from a linked to a table. i want to add field to the make table query that are not fields on the table. How do i insert a created field? Hopefully this is a basic question.....
I have a query which looks up records from a table, now what I would like to do is append the word All Projects to this query - is it possible, and if so how?
Help...I am new to Access and am trying to get a query completed to run a query on contract end dates. My goal is to be able to run the query on a date such as 10/2007 and have it pull all contract end dates that would term for the month of October, 2007. I have been pulling my hair out trying to build expressions and sifting through my access books to no avail.
I've got a fields called rev code that contain the following values:
field name: 110 131 250 255 258
field value: 7.49 6 11.25 12.11 78
I'm writing a query that pulls from the first two digits of the rev code and need to round off to the nearest dollar so in my query I'll have a column 11 with a value of 7, a column 13 with a value of 6, then I need to take columns 250 255 and 255 add the values together and round off so I get a column 25 with a value of 101.
I'd like to know how to add a blank filed (memo), called "comments" that is not pulled in from any existing table, in an existing maketable query which is getting it's fields from different linked tables , so that the destination table that comes out of this query, has all those data along with an extra column for me to add comments.
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.
I 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 am trying to set up a create table query using parameter called Year. For each year I input , I need to create a new table with the suffix of the year. for example, if i input year 2011, i want to create a table called budget_2011, if i input year 2012, i want to create a table called budget-2012...
I am designing queries to return averages for quality test data.
I have this query that functions as I want it too [URL] .....
It returns the averages of all the values received for different tests for a lot number (the lot number criteria should be filled out as well)
When I want the query to be more specific and average only certain box numbers in the lot (that start with the prefix PB") the query does not return an average for box numbers starting with PB but splits them up, showing an average for PB1, PB2 instead of combining the data for those boxes into a single unified average ...
Hey all, I'm trying to design a Test / Survey database.
Please look at the .jpg attached or download the sample database.
I have looked at some previous survey/tests design posted on this forum and had a good table design based off of a Pat Hartman suggestion. Now i am wanting more out of the database and need more advanced options.
Here is what i am trying to do. When a user decides to make a new question for a test/survey they will have to choose what type of question the question will be. For example say the user wants to make a answer grid question. They would choose Answer Grid from the tblquestionstypes combobox. Then they would have the option of choosing a type of answer grid (Dual Scale, Rating Choice, Ranking Choice) from the tblQuestionOptionTypes combo box. Say the user chose Ranking Choice then they would be presented with a list of options for the Ranking Choice. For example 5 sets of rankings, 4 sets of rankings. 3 sets of rankings. (These are just coming off of the top of my head but i think you know what i mean.)
Question, Is this close to being correct. mind you i have just started this and am just throwing ideas around. Where could i store the correct answer for each question. Sometimes there will be a answer and sometimes there will not be an answer due to the fact that it could be a test or a survey.
Any help or suggestions would be appreciated. Thanks
MS ACCESS 2010,i need to create macro, on insert. which will send mail if is selscted specificed value in combo multivalued list.but, i cant access to that field to test it.
I have an Access form that gathers students test scores on Reading & Writing exams. Depending on those scores, there are five possible classes for them to be assigned.
I have the intervals worked out, and have created a table that contains the min reading, max reading, min writing, max writing, and class they belong in.
The scores are entered into a form as scoreReading & scoreWriting (integer)
I'm trying to figure out the best way to pull and compare the values and return the proper englishPlacement text. Its set up this way because there are multiple tables to pull from, depending on the date of the exam. Each table has different ranges for each Eng1,...,Eng5.
I'm thinking perhaps to iterate through each column and evaluate T/F, then return the englishPlacement value of the correct row. I'd like to keep this stuff in the VBA so that the code to select a table based upon date doesn't break when the intervals inevitably change requiring an additional table.
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.
