OK - I'm kinda new to all this stuff (as you're about to find out from my question).
I've got a page with a search form, where visitors enter a search term. That page uses GET to pass the variable to the results page. The SQL on the results page is pretty simple, it just takes the variable from the URL and filters for records:
WHERE columnname CONTAINS
Request.QueryString("passedvariable")
So that all works fine - except that it does not allow for complex searches. If someone searches for 'fish' then that works fine. If someone searches for 'fish and food' then the search looks for "fish and food" somewhere in the record, rather than looking for both of those terms in a record.
Now, I'm guessing that there's no simple solution for who to get this to work, but I'm hoping that someone can help me out and at least point me in the right direction. I'd be happy to post additional code if you need it - but I don't even know what else to post!
Hi there, I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.
I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.
Hi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields... Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again. Or, by asking if I can search for field values querying by field name, am I any clearer?
I would like to take information from a table and combine all the like terms. For example, I have a list of items, and some appear multiple times. I would like access to group the items with the same UPC together and give me a running dollar total insted of viewing the same UPC item 3 times. Is this possible?
Hi, I currently have an employee database - all info is currently in one table. I am wanting to create a search box in my form which allows me to search for multiple bits of information. ie. I would like to be able to type in either a first name, surname, full name, or employee id number (for example), press enter, and for it to display the results (either by going to the first that it finds, and me then clicking find next, or by displaying all found in an index so that you double click the one that you want - i am not too fussed... the main issue is getting the search box to look in multiple fields).
I currently have the following code:
Private Sub cmdFind_Click()
DoCmd.ShowAllRecords DoCmd.GoToControl ("Surname") DoCmd.FindRecord Me!txtIDFind 'If FindRecord = True Then 'Exit Sub 'End If DoCmd.GoToControl ("EIN") DoCmd.FindRecord Me!txtIDFind 'If FindRecord = True Then 'Exit Sub 'End If DoCmd.GoToControl ("forename") DoCmd.FindRecord Me!txtIDFind
End Sub
(ignore the notes - they were trial and error for something else).
Currently, when i press find, this code will find the first of anything - ie i time in Frank and the first Frank will appear (unless someones surname begins with Frank). What i can't seem to do is get it to progress to the next record if i press a find next button.
I have also tried the following code, but it only works if i put in an EIN number:
Dim SearchStr2 As String SearchStr = "[PROTECHNIC_NUMBER] = " & "'" & Forms![FRMPATIENT]![Frm_ICP_Select].Form![Protechnic_Number] & "'" _ And "[ICP_Code]" = Forms![FRMPATIENT]![Frm_ICP_Select].Form![ICP_Code] DoCmd.OpenForm "FRMASSESMENTHEAD", acNormal Forms!FRMASSESMENTHEAD.Filter = SearchStr Forms!FRMASSESMENTHEAD.FilterOn = True
If the button is clicked it should open the FRMASSESMENTHEAD form where the Protechnic Number (which is a text field) and the ICP Code(which is numeric) is the same as the ones on the current subform.
It works great if just using one of the criteria but when i put the and command in and then add the second criteria i get an erroer message saying
Run-time error '13':
Type mismatch
hope this makes sense and can anyone help :confused:
Is there a way to search multiple tables for a record. I have 4 tables, each containing a different product. In each table are lists of serial numbers (primary key) and other information (ship_to, sales_order_number,etc.) for the product. I want to be able to enter a serial number and display the information related to that serial number. Not all fields are the same for each table. Is this possible? I would greatly appreciate any help!!
Ok folks I have what is proving to be a doozy for me.
I have a multiple table database related fields in each one are: Procedure Description Payable Amount
I am trying to find a way to search particular tables for particular Procedures. My output must include fields: Procedure, Description, Payable Amount. I have tried a form but I would have to included way too many text boxes to accompish what I am looking for. If possible I would like to have three text boxes if a form is possible. I would start the search and my output would end up in the text boxes below: Procedure, Description, Payable Amount
With of course some sort of Identifier of the table that was selected to search in.
Thanks to the the Smart Questions tips, I'm skipping this:
PLEASE HELP ME OMIGOD I'M GONNA DIE! ACCESS IS ALL BUGS! IS MY MUTHABORD CRAKT? PLEASE EVERYBODY COME TO MY HOUSE NOW AND SAVE MY AZZZ!
but proceeding with something worse:
Where is there a table of Access terms defined in English?
I've got a primitive database set up, on potential customers. 2 huge tables, a query, and a form that's worked for inputting background info and contact reports.
Problem: my user interface is in German. I'm trying to educate myself out of trouble fast, which I need to do in my native English. Abfrage = Query seems intuitive enough, but even that hurt.
I don't even have the vocabulary for searching halfway intelligently in English.
One key thing I'm trying to figure out is how to get data entered from the form as checked boxes (each referring back to one of ca 25 yes/no columns in a table) to spit out on a printed page as words separated by commas, like
End markets: gravymakers, applecart drivers, psychiatrists
To find where this is already addressed, I have to figure out what it's all called. Searching in German almost split my skull. I've already written the content in German, I've been screamed at in German, and when the co. refuses to pay for work they sabotaged, I'm going to have a long court process in German. 'nuff whining though ...
I have three tables that are named different, but have the same column names, but different data in them. The data in each table is linked by a master table that has a primary key.
What I need is a way to make a form with a box where I can search on a value that could be in any of the three tables and then the associted information from the site will display in the above boxes.
(Site Master Table contains all site information and the primary key) (The three tables are sales order information. Stage Only table, Install table, and srop ship table)
i have created a form for my database where i intend to give the user the option of searhing my more than one criteria but not all. i have used a system of combo boxes for the user to enter thier choises leaving the combobox blank for any criteria they dont know. the user then clicks th button to open the query
in the query i have set the criteria for the fields to equall the related combo bo in the form.
however if the comboboy is left blank (0 or null) the query looks for a record where that field is also null. as a result it wont return any records.
an example
on the table tblFault there is the record
FaultID; 7 Computer id; 2 Name; c Room-ID; it1 Date Of Fault; 05/06/07
on the form the user enters the following values into the combobox
FaultID; 7 ComputerID; Nme; c Room-ID; it1 Date of Fault 05/06/07
the query returns no records insead of the record above. this is because it is looking for a record in the table where the Computer-ID field contains a null value.
is ther a way to ignore the chriteria if the combobox contains a null value????
i have tried to use vb to convert all null values to be "Like "*"" but it keeps returning a type missmatch error but still changes to combo box value to ; Like .
Not sure where to put this, but as I want the information to be shown in form view, I guess this is the bext place to start.
In breif, I have created a database, in which infomation on machinery is held.
The problem I am having (atm), is I have to create a function, which I want to be presented in form form, where by each item can be searched by it's RequestID (unique) or the customer who owns it, so that its current location can be attached (or viewed).
I'm not sure whether i've worded this well, so to sumerise:
I need a form with 3 fields, RequestID, Customer and Location.
Where by I input the RequestID, which brings up the Customer and Location, or input Customer which brings up the RequestID and Location (obviously there will be mulitple results from Customer search).
Again, I hope I haven't confused anyone because it's pretty simple what i'm trying to say, but....
I am in the process of creating a database in access to search for a persons name. However, the results will give many ansers. For example if I search for the srname 'jones' I get many names. I eed then to be able to select one of these jones's with the information relavent information.
It has many years since I have done this although i did have quite a good knowledge at the time. Plesae can you make any suggestions and answers as basic as possible !
Many thanks in advance http://forums.aspfree.com/newthread.php?do=newthread&f=18# Mad
I have around twenty shipment tables for different days and one main store check table.In the shipment tables i have bill numbers that correspond with shipping id's and in the store table i only have bill numbers.
I would like to check if the bill number in the store table appears in all 20 tables.after doing that i want the corresponding shipping ID to appear in the store table. Is this possible?
Im trying to set up a combobox on my Customer Contact Information form, where you can enter a phone/fax/cell/pager or any misc number, and it will bring up the appropriate customer.
Is it possible to make a query that will show the different phone number fields as one field?
Thank you so much for your help, and please let me know if I can clarify anything.
I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.
The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.Finally I want the user to be notified if they are trying to exceed the lot size in the query.My code is as follows:
Code: Public Function RemoveFirstChar(RemFstChar As String) As String Dim TempString As String TempString = RemFstChar If Left(RemFstChar, 1) = "0" Then If Len(RemFstChar) > 1 Then TempString = Right(RemFstChar, Len(RemFstChar) - 1) End If End If
I'm working on a Word mail merge feature that links to an Access Database and would like to know if you can insert specific fields into the mail merge to refine the terms? example below:
<<Customer Name, [field 2]>> - Or something to that effect.
^The mail merge would know where to obtain the customer name from anyway, but will it select information from the field that is being referenced?
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID Employee Name Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7 ....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
I have a database and I made a query to search records. The query worked perfectly, but now the only problem is that if a search has an ID number that is bigger than 239, the results will not be displayed. The query will be empty! :S
Hi, i am designing a database but i need to do a query which will search for a specific word and bring up all the details regarding this this. An example is i need to search for the drug panacur to see which animal use this treatment, i then want it to bring the name and address oof the client as well as any other details. I just do not know how to search for a specific word. please help!! Thanks
I have a form that is based on a query with many fields. As an example there is a field called "Medium" which contains values such as "Oil on canvas", "Oil on wood" etc.
At the moment I have a filter which works fine but this doesn't allow me to search all records which have the word "Oil" as part of the Medium.
I would like to be able to search all fields on the form. I have looked at a few exmaples on the forums but could not find one that applies to my scenario.
I'm after something quite complicated this time, so i'm going to do my best to explain it in the offchance that someone can help me out. I'm not sure that what i want is even possible, although i think it should be; i just dont know how to achieve it.
I have three entities; Table1, Query1, Form1.
Form1 and Query1 have both been created using Table1 with some basic data. Form1 will have three text boxes titled: Broker; Audit ID; Audit Date Beneath these it will then have a search button.
The idea is that i will type "BrokerName" into the "Broker" field on the form, and using Query1, the search button will retrieve all lines of data that have "BrokerName" in the "Broker" field in the query. So it's like using the [Enter Broker Name] criteria in the query except the criteria is entered into a Form text box rather than a pop-up window. Does that make sense?
The remaining two fields, Audit ID and Audit Date, will need to ADD the data entered into those boxes on the form, in to the query. I *think* it needs to add the data in to the query before "BrokerName" is used to select the records with "BrokerName" in them.
After the records have been found using the "BrokerName" criteria, they would then be inserted as Subform1 on Form1. I figured i would need a refresh button for this to work effectively. I have already made a refresh button, but if anyone knows of a way to refresh it automatically after selecting the records i need, then that'd be great.
So i have the basic setup ready to go, i just dont know how to make Query1 use the criteria entered on Form1 to search specific data.
I really hope that makes sense, i'm not very good at explaining. Any help is hugely appreciated!
I am currently working through my A2 (final year of high school in the UK) ICT coursework, in which I am building an Access database system for a small wedding car hire company.
My current problem is I would like to (not even sure if it's possible but I assume it is) have a form that searches a query for a date that the customer has entered.
If there is no date in the query that matches the one that the customer has entered, it means that their chosen date is available. If there is a match however it means that the date is unavailable.
I am trying to create a query with a parameter on it, but on a date box, but only searching for a specific month, without regards to the year. So what I want is for me to run the query, a pop up box asks "which month?", I then put in Jan for example, and the results will show all records with the month Jan regardless of the day or year.
Is this possible? If so - whats the code I would use?
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
