I have created a simple Unbound search form based on a 'Bookings' table. There are 4 comboboxes that the user can choose to search on, 'Client_ID', 'BookingType', 'FundingArea' and 'ChargeTo'. I have created a query that uses the following criteria;
Like "*" & [Forms].[frmLBT_db_Tool].[cmbName] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbBookingType] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbFundingArea] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbChargeTo] & "*"
Using this criteria any of the comboboxes can be left blank and the results will display the chosen combobox used, or if 2 comboboxes were used the results would be further filtered.
The problem I have is that if a name was chosen (cmbName - Client_ID) and the Client_ID was 2, then all Client_ID records which have a 2 in them are returned, 2, 12, 20-29,32,42,52 etc...
Is there a way to overcome this, or am I going about it all wrong?
I have a table called Coded_Data with one particular field called Codes. This field list a number and a respective code to identify what type of code it is. For instance 123456789-AD...123456789-ADS. I am wanting to create to two columns. One listing all work orders coded with AD and the other withADS. When I use the Like condition with % it returns both AD and ADS in same column. I understand why because the "%AD" returns anything that starts with AD so this is why I get both codes. I only want to get the the AD codes in one column and ADS in the other. I have duplicated the table twice since I am trying to retrieve data from the same field and table but display in 2 separate columns. The duplicated table has a different alias. The problem is in the where clause.
where (((coded_data.codes) LIKE "%AD")) or ((coded_data_1.codes) LIKE "%ADS");
Is it possible to update vlues in a table using wildcards. I want to use a query to search for certain records that meet a certain criteria and modify these values like changing particular dates in the date field to new values.
I have a table with a string field for a street. It might have data such as:
Bleeker Street or Bleeker St.
I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?
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'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had [Forms]![MAIN]![Text440] refering to the unbound text box on the form already mentioned.
This worked fine, but the following: Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as: Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
Can we have two tables linked to one search query? I've some data that is confidential and woul like to have them in two different table. When I search for it using query form, only the relevant table results will be shown?
Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.
I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.
I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.
I have a parameter query in which consists of a search statement as below:-
Search: [Model] & " " & [FiscalYear]
It works very well. I am wonder if there is any options available to affect the search results. As far as I understand, when I enter the key word into the pop up box, it will search the fields sequentially and populate the result. Can I enter different key word for each field? If so, what is the syntax for entering the key words.
I have built a search form to feed information to a query. The form uses combo boxes tied to table values, and all have wild cards built into them so if the user leaves the combo box they get all the records. I also have to text boxes representing start date and end date. I would like to allow the user to leave these blank and get all there as well. I have been looking through my one Access book, as well as searched all over the internet, but I cant seem to find the way to do this. My filter criteria for the text based combo boxes are like this:
Code: Like "*" & [Forms]![ReportDesignF]![Company] & "*"
My filter criteria for the Start and End Dates looks like this:
Code: Between [Forms]![ReportDesignF]![StartDate] And [Forms]![ReportDesignF]![EndDate]+#11:59:59 PM#
In this case of the user leaves the date values blank, the query returns nothing. I would like to return all dates if that is the case. I am assuming it is my lack of knowledge of wild cards and how they work with date values.
I have made a database for work and is fully functional, but theres one thing I want to add but cant get my head around how to do it.
I have created a Form called 'Filtered Search', on the form it has multiple combo boxes for 'Auditors' 'Area' 'Status' and 2 text boxes for date range.
I want to be able to set what filters I want, and for the query to ignore any fields with no information selected/inputted (i.e. I want to see all records raised by "Mr Smith" (Auditor) that are still 'Active' (Status) in all areas at any time).
Names of items;
Table = 'Incidents' Form = 'Filtered Search' Report = 'Filtered Report' Auditor = 'Combo7' Status = 'Combo156' Area = 'Combo5' Date Range From = 'Text161' Date Range To = 'Text163'
As the post title says, is it possible to force a query to start its table record search at the end of the table and go backwards? The table I'm searching has hundreds of thousands of records and I want to check if any new records have a field with a value that has already been imported into the table. The duplicates would most likely occur near the end of the table and not the beginning, so I see no reason to waste cycles searching records from the very first record in the table.
How do I design a query to return a result in a wildcard format? So that I could enter a part of a name, and it returns all the names that include that part of name?
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below
I have created a query that finds information from 'all information' using
Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"
This is working on all text boxes, It only half works on the combo box's when I use
Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"
If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex".
hi guys.. i want to include a search form in my database.. here is my current SQL statement:
SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd FROM tblCompany WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));
but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.
I'm making a student report card. Teachers enter student data via a bound form. I have a text field that teachers enter their class which brings up student names. They fill oput the form accordingly.
I have 6 classes of grade 7. 7A, 7B, 7C and so on.
If a teacher enters something weird like (7 (space) A) the query bound to the form will return nill results because it's looking for something that doesn't exist. I need an error message to appear which says no records will be returned because you haven't entered the correct parameter value. (Because they have entered 7spaceA rather than just 7A.
I could just use a combo and this would solve all my problems. But it's not always practical. I was thinking I could use a wild card where if the first character was a number then at least some values would be returned. There is method to my madness... I actually want to apply the same code elsewhere when running reports.
Is there a way I can enforce some kind of input mask in an event proceedure that can do this??
I have data of type "number" shown in the drop-down combo-box in a form. It has some codes in the event of "After Update".
My problem is that i can't type in something like 13* in the combo-box in order to get anything starts from 13. It says "The value you entered isn't valid".
The reason is that i want user to be able to use the wildcard. I know that i can do this with text. I'm not allowed to change the structure of the table in the database.
This combo-box shows IDs from one of the exisiting tables. That IDs is of type "autonumber" in the table.
