Hello! I am trying to build a search form, based on a table.
Here's the table layout:
Employee Competencies
---------------
EE Name
Competency
Proficiency
Years of Use
Recency of Use
I need to be able to select multiple competencies, and/or associated levels (the rest of the fields). At first, I did a filter by form, but the problem with this is that I can only search "OR's" and I need to be able to search "AND's" as well.
Here's what I want: Basically, a user would select six competencies and associated levels, and the form would open to a report listing each of the matched employees' competency sheets.
I've tried query by form, as explained on the Microsoft Knowledge Base and it doesn't work. I'm going to try to upload a stripped down version to make things clearer. I'm desperate and running out of time. Thank you for any help!
Okay, my file's too big to upload, so hopefully, I've described this well enough. Thanks!
If the user inputs two words (or names in this case) such as "Bloggs Smith", how could I get it to return records with Bloggs & Smith in whatever order they were input?
Example:
Author: Smith, Medin & Rose
User searches: Smith Rose ..........and it returns a hit for Smith, Medin and Rose.
Maybe the Author field would be better not as a text but as a seperate table with a many to many relationship...can someone suggest the best option?
I have a main form with two subforms. One of the subforms in turn has three subforms.
It is a music database. The main form has title information, one subform has the group/artist. The other subform has song information (side, track, title running time) and subforms for performer, composer and conductor.
I am at a loss as to how to efficently navigate back and forth. All of the subforms (except song titles) are datasheet view using combo boxes. For lack of a better way there is a "Done" button after each row. Click the Done button moves the user to the next form.
I have custom Navigation buttons for the main form. The form works fine, it is just a pain to navigate.
I have other issues with it but this is enough for now. I started writing the database in Access 2000 and it is now being used with Access 2003. Any suggestions would be most appreciated!
I have a form with 47 subForms, and Form.Controls.count is 351. The sub forms were originally text boxes, which got changed to ListBoxes because of customer requests, then to subforms because of conditional formatting needs that the customer wanted. Now, when it is open, other forms give sporadic "System Resources Exceeded" errors. I'm pretty sure it is this form that is causing the errors, as it is always open when they occur. (Only consistent factor.) Task Manger - Processes shows that closing this form releases 723 "User Objects", and almost 400 GDI objects.
Is it time to switch to a different development tool? Visual Basic? C++. Is it possible to rewrite the single form as a standalone and keep using the rest of the Access App? (Almost 60,000 loc in 200 objects.)
I've got a query pulling data from a view I created that gives all the data I need. The query gets it's criteria from a form with 10 combo boxes, a radio box with 3 options and 2 date fields for the timeframe to run the report in.
The issue: over half the columns of data that the radio buttons can limit results by have null values but only two of the columns are excluding records where the values in them are null. As any combination of the combo boxes are null I'm using the following format for my criteria per column IIf(IsNull([Forms]![Margin Dialog]![InstallerOption]),[Installer_from_Web],[Forms]![Margin Dialog]![InstallerOption]) That is an example of a column that works, the following is a column that is excluding null values no matter what I do IIf(IsNull([Forms]![Margin Dialog]![AOIOption]),[ProductType],[Forms]![Margin Dialog]![AOIOption]) I am not seeing the error and could use some thoughts.
I have created a FE/BE database which is operating on a network. There is one FE for each person (approx 150) all accessing 1 BE. There are at most, maybe 10 people using it at once. The database has been working well for about 2 months.
My problem is not with data corruption (touch-wood!), but with a complex form.
The Form is split into a Header/Footer, with a Detail section containing a subform. The main form filters records that appear on the subform. The subform is continous, with several conditional formatting cells per record.
Most of the time it works fine, but occasionally, the main form opens, but the subform fails to load/open. All I see is a white page with the main form header displaying correctly along the top 1/3rd of the screen. No error message is displayed. If I print the page using a Report (The subform and Report use the same query) the page prints perfectly with all records showing.
I have seen this problem on several different workstations, only to try again later to see it working (having closed the db, logging off then back on again)
I am at a loss with this one. Is it a problem with Access or the network? I am unable to find anything similar to this problem on the forum. Can anyone shed any light on it?
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
I'm trying to figure out, how to make seach form with one text field, that will accept OR statements. I have one table with fields name, category, and description. Is there any chance to make search like this: User will put "part of the string" OR "part of the string2". Result will be the list of the records that include these parts of the strings in any of the given above fields (name,category,description) I hope i'm clear enough
I have a bound form which is from tblEmployee, I'd like to have a dataset below (like a splitform but not a split as they have limits) so when i search in the box it gives me say all the smiths - i select for example david smith and it displays his information in the form objects above so they can be edited?
I've built a PostgreSQL database for some ecology data and am trying to use MS Access 2010 to make a front-end for it.
I've created a form to display site data; it includes such fields as site ID, site name, county, state, landscape, etc. (There are more, but if I can get the idea down with a couple of these, I should be good to handle the rest). One can click through the form and see 14 pages of site details, which is fine because there are 14 sites.
What I want to do now is make a search form in which one can enter values for site ID, site name, county, etc, and somehow get the pages of the display form that match those values. It's not terribly important to me how that output looks - whether it's a list of matches that allows for clicking on a match and showing just one page of the display form, or whether it's a filtered subset of the display form pages that one still clicks through to view them all.
if there is code to write, where do I type it? Do I start my search form by making a form with the search fields as values, or is there some other way? How do I make those search fields into search boxes rather than display boxes?
I have a form with two textboxes called: txtAptDep, txtAptArr.
On their right side there are two buttons (btn1, btn2) to open a form called frmAirportSearch. It allows to choose (from a listbox) the airport to be filled in those two textboxes.
Now, if I search for an airport pressing btn1, I want the airport to be returned into txtAptDep, while if I open the form by btn2, the airport choosen must be returned in the second textbox.
creating a searching form and to show the results inside the form! It's like a Library type searching. u want to search for some type of monument or so and it shows the results, all the info, photos and that!
I am trying to make a simple inventory control system. This system will only record input of products and output of products. Explanation: This is a catering service company. I buy tomatoes, rice, oinions, Oils, meat, ect. When we buy this products they usaly come in different packages. The rice can come in 100 pound bag, 50 pound bag or just by the pound. The onions can be bought by the bucket, half bucket, dozen, half dozen ect. The oil can be bought by 100 gallon barril, half barril, Gallon, half gallon, pint ect. The meat by the pound.
I am able to make the a simple invoice and PO application, the only problem is that I want to be able to select what type of packaging the product has and the invoice or PO subforms.
I started with a simple product table, I than created a packaging table and a package detail table. This gave me the chance to create a subform on the product form where I can choose what type of packaging this product uses and also tell it how many units the package has.
So far so good. Now I created a Chef table "this is my Chef to whom I give the product to for cooking" I than created an authorization slip table "this is like an invoice table" and ofcourse a authorization detail table "this is where I pick the products that I give the cook" once I print this out the chef will be able to go to the warehouse and retreive the product.
What I want to do in the detail subform is to be able to pick the product and the type of packaging that I am giving them from a dropdown box then it will automaticaly show only the packaging I assinged to that product and give me the units . This way if I pick the type of packaginh name 100 pound it will put 100 units in the quantity field automaticaly.
So basicaly is an invoice form with its detail but on the detail I am able to pick the product and the packaging that I am giving the chef.
Can you guys guide me on the right direction on how to do thsi?
Hi, I was wondering if someone can give me a possible solution to my join below:
select tableA.name From tableA, tableB where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query
Hello , I had incountered with a huge problem in my project and I need your help guys. I have a huge table contains alot of data about many people- I wanted the data will be checked and sent to a query. Here is an example for a little project beacuse I couldn't have the big one. (This example works fine) My big project is pretty the same but after I try to get the query out I get an error that the query is too complex....(It's really too big when you have 20texts to be checked with 9000 fields) If you check the query fields you may see how the OR is getting separted and because every text has OR statement everything is hanged by everything. Someone told me that I can't use query options and mannge it and I should use VB SELECT option - but I can't make it work too. So I can't show you the real example at all beacsue I can't have it to my computer and It will take alot of time to have 9000 different cells ; SO if someone knows what I am talking about and ever encoutered it , I would be really thankful ! (Also - You may see some problems with the OR ("") but I mannged it to work with the BIG project so it doesn't matter) I don't want you to focus the conditions but just the problem itself ...." the query is too complex"...
This one is a pain in the but. I tried everything, but why would it just won't work. It always says the query is to complex. Have included the database, anyone a solution?
I need to perform a query on a database (not designed by me) that is not normalized. In fact it is only one table with numerous fields (many of them Date/Time). I need to query the table based on the date fields only.
Basically the table is used to track when specific functions are completed to determine the time required to perform the individual tasks (accuracy to one day is acceptable) and find the ones which are falling behind. Since several individuals are required to complete each project each step has an average value (based on historical data.)
The following is a short narrative:
1. Step 1 is scheduled for 1/5/05 and the task is not started untill 1/7/05. This is not acceptable. There is a 1 day window. I need to flag this record (via report) if the start date exceeds the schedule date by more than 1 day. If a start date has been entered this record does not need flagged (regardless of the alloted time).
2. Step 2 is based on the amount of time alloted to complete the task once started. If the task takes more than 2 days the project needs to be included in the query for the report. As above, if the date is entered the record is not needed since there is no need to try to determine the when the project will start.
3. Step 3 measures the number of days from the project completion untill the product is sent to the central office. If the time exceeds 2 days the record needs to be included in the dataset. Again, once a date is entered in the received field there is no need to include the record.
There are a couple more steps but they follow the same criteria as the first three.
I have not been able to figure out how to get these requirements entered into the query design view.
The query I'm trying to get is: Who owes me money for the service and How much.
Each car had several treatments in the past and some have missed a payment or I didn't notice and just issued a new order.
I need a query that runs through the entire database, does a summary of all the amount owed to me per Car, and then deducts the total payments made per Car. If the balance is > than Zero,. Show me that car and the bottom line amount.
I need to import information from an XML file that includes a wide array or codes. Each code stipulates which amount is entered into a particular field.
The importation and conversion is not a problem this has been achieved successfully, however the problem exist in the update procedure. Due to the extensive amount of different codes (est. 30) an IIF statement becomes to large and complex to evaluate.
The following statement though being very incorrect it does show an example of what is being attempted.
IIf([ClaimUpdate]![claimtype]="com1" Or "com2",[tblClaims]![CommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="RCM*",[tblClaims]![ReCommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="SCH1",[tblClaims]![SchoolBasedCommAmount],"")
Basically if table [claimupdate]![claimtype] = "com1" then [tblClaims]![Related Field] = amount else leave blank.
Question is there a better way to construct the criteria or should a module or procedure be used instead to evaluate? Or is there a way to construct a nested IIF statement to evaluate a large complex criteria?
Any suggestions greatfully recieved...
If it looks like a problem, acts like a problem it must be a problem!
Hi guys, long time surfer, first time poster here :)
We use a prehistoric Process Plant design software package, and it stores all its information in Oracle 8i.
A report I need to pull takes data from a stack of tables, each with a unique number.
PDTABLE_113 contains a list of models, each model having a unique PARTITION_NO.
Every PARTITION_NO entry has a matching table called PDTABLE_21_XXXX where XXXX is the PARTITION_NO of the model. Each PDTABLE_21_XXXX has a row for every piece of equipment in the relevant model.
PDTABLE_21, for argument sake, has the columns EQUIPMENT_ID, EQUIPMENT_DESCRIPTION, and EQUIPMENT_STATUS.
Is there some way to query the database so I get something that looks kinda like this:
"This expression is typed incorrectly or it is too complex to be evaluated….For example a numeric expression my contain too many complicated elements."
I think the error is as a result of this expression in my query, I have 9 fields with the expression below with different field names:
Like Forms!QBF_Form!Sales & "*" Or Forms!QBF_Form!Sales Is Null
The problem is when I close the query and open it, the expression multiplies to numerous rows and also creates additional fields for the last part of the expression. Is there an expression that I could use that would work okay.
I tried using this expression: Like IIF (Forms!QBF_Form!Sales)="", "*", "*" & Forms!QBF_Form!Sales & "*"
It was working, but is no longer working..not sure why.
Do you have a similar expression that will achieve the same result but create additional criteria.
this shows that I can have multiple entries in a day for a particular serial number.
I need to select ONLY ONE serial number and corresponding data for each day (or week).
I tried to format the date to remove the time and then select the distinct date (so 1 record a day for each serial was displayed), this worked. BUT I could not link it successfully to other tables because I had to format the corresponding match date to avoid a type mismatch thus invalidating what i was doing by selecting the distinct record.
Here is the original query i was using that selected a range of dates (which included multiple dates in a single day with a single serial).
SELECT TBLRemoteUnitInfo.TankSize, TBLRemoteUnitInfo.TankName, * FROM TBLRemoteUnitRequests, TBLRemoteUnitInfo WHERE (TBLRemoteUnitRequests.RemoteFeildUnitID = TBLRemoteUnitInfo.RemoteFeildUnitID) AND (TBLRemoteUnitRequests.SerialNumber=:SerialNumber) AND (TBLRemoteUnitRequests.DateReceived BETWEEN :Datestart AND :Dateend) ORDER BY TBLRemoteUnitRequests.UnitRequestID';
how can i take a sample of the range of dates (ie 1 a day/week or month), i assume i need to create a filtered table via query first then query that table.
Its killing me!
if i am being unclear please let me know and ill try to clarify
I have come to a dead end with my query. Any help would be really appreciated.
I have a query which i have written:
SELECT TblBurnleyWwTw.MetricID FROM TblBurnleyWwTw WHERE (((TblBurnleyWwTw.[Data Source])="OMS")) OR (((TblBurnleyWwTw.TAGFunction)="CHP")) GROUP BY TblBurnleyWwTw.MetricID, TblBurnleyWwTw.[Metric Required], TblBurnleyWwTw.CALCULATIONS, TblBurnleyWwTw.PIPointSource, TblBurnleyWwTw.PILoc1, TblBurnleyWwTw.[High Level KPI] HAVING (((TblBurnleyWwTw.MetricID) Like "130*") AND ((TblBurnleyWwTw.[Metric Required])="-1") AND ((TblBurnleyWwTw.PIPointSource)<>"L"));
This retreives all codes relating to what i want. The problem is, I need this data to try and match within another column of calculations E.g. Data retreived from query I have may be 13001, 13002, 13003. What I would like to do with this data is to lookup in a calculations column if any of the above data matches to bring back the metric ID which is realted to it?
Hope i have stated this clearly enough for anyone to understand???
As i say any help or recommednations for a solution would be really appreciated
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address] FROM [UK Table] WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));
What is wrong? I have attached the database. The form frmNz is what I want to work. I should be able to enter company information into at least one field, allowing me to retrieve the specific company data from the UK Table in the subform.
