On the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):
From Date
To Date
Port
Vessel
Voyage
---------------------
The query is as follows:
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR
(dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR
(dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
----------------------
However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. I created almost all the tables (all that I need for now) and made the relationships.
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
Now here is my problem: The User usually searches for the respective family by the Group ID. I implemented this with a search query using the ID number of a search text box. All done and no big problem.
But sometimes the ID number is not known so the user needs to search by name (First and Last Name). I use to different textboxes for this and it works in a similar way like the number search by query (Like "*" & [Forms]![frm_SearchIC]![txt_LName] & "*"). All still good However since most of the clients are actually from arabic speaking countries, converting the names into the Latin alphabet is bound to fail and produce a lot of misspellings. Therefore I added 2 more textboxes and 3 comboboxes for the user to give more information about the client and therefore make it easier to search for the person. I was able to produce a query which gives you the right result if you have ALL information at hand. However, this is not always the case.
1) But I cannot find a way to tell the query that if the a certain textbox or combobox is empty, it shall just "ignore" it and use the information at hand. I tried this in the query by adding in the criteria OR .... Is Null. This is alright for one or two textboxes but for the many I have, it seems to be too many different combinations for the criteria. It just worked with some fields but others always had to be filled in...
2) If no information is given at all, the database should inform the user that he needs to enter at least on field. If nothing is found the user should get a msgBox saying "No IC matches your criteria".
3) The results of the searches should be given out in another form where the user can pick the person from 1-to-many results.
I attached a sample database with sample data and reduced tables, fields, and entries ...
I have a form with an option group that depending on which one is selected, opens a report with a specific query as it's source. Part of the query looks at a listbox on the form. Here is the criteria for a field: [Forms]![frmOnSearch]![tboCollDev] I want to make this criteria OPTIONAL. So if there is nothing selected in the listbox, the query should not look at that criteria. FYI, the value that is returned from the listbox is numeric.
I've had a look around this forum and haven't seen anything really matching my needs, but please feel free to point me in the right direction.
What I am trying to do (using macros & queries - very new to SQL / VBA) is filter by multiple fields, but also allow the user to choose to filter by different combinations of fields - such as searching for first name and suburb, or salesperson and customer source, or first name and salesperson, or suburb and customer source, or three options or one, etc etc.
There will only be a certain number of search fields, probably five or six, but how do I get the query to recognise that if one of the search boxes is blank, it should allow all records in that field and only search by the other criteria, but if all the search boxes are filled in, the results must match all the criteria?
Does this make any sense?
PS - I am planning to use a command button as an "Apply Filter" so that the filter only updates after the user has specified all the options they want.
Is there a simple way to add optional criteria in a query for a subform? Basically, what I'm trying to do is have a number of comboboxes in the parent form that indicate what records are shown in the subform (these records could then be added and/or deleted). I would like to be able to have a combobox such as "Month" that would filter the data shown in the subform to that month. If no month is selected, then the subform would show all months.
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
- text box for user to enter EMPID, txtEMPID - LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field... - if the user enters an EMPID that is in the TABLE...display "Y" - if the user enters an EMPID that is NOT in the table...display "N" - a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
hi i just signed up to this site and need some assisstance from someone
i need to create a form in access 97 that enables me to search for a keyword on a given search area, for example the cd/dvd rom drive for files and folders
I have a search form that uses several comboboxes, textboxes and checkboxes that are used as criteria in a query. You enter in the relevant information, hit search and a report opens based on the filtered query.
What I would like to do is change one of these combo boxes to a listbox and use the multiple selection as the query criteria. I know it's not as simple as just putting the listbox as a criteria in the query, and I've also tried many different variations on the varItem and strWhere code.
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered. - for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june". - This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.
So I'm working on something for housing. Each house has a "Property Reference". This property reference links to all other information on the property.
There's two tables, Referral (For a tenant, with the Property Reference as a foreign key) and PropertyInfo (Holding all the property information)
Basically I want to save users as much input time as possible, so I'd prefer if they could just enter the property reference for a person, and that populates the rest of the table.
I'm currently using DLookup on the main form where it displays the information related to the property reference, obviously it's not actually being saved into any fields.
Will this method be okay if I would want to search the records by the address on the main form?
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code: Private Sub PrintBtn_Click() Dim strCriterion As String Dim strMsg As String, strTitle As String
I currently have a database which contains various information for part returns. Among this information is contained a parts tracking number, VIN Number, and Date Code. I want to have a small dialogue box (Form) where a user can enter a tracking number, (or a date code or VIN if tracking number unavailable) to search for a record containing that tracking number. I have already created a replica of my new part entry form to view parts in read only mode only.how to create small form to search for record based off criteria then open the replica form i have made to that record?
-Two textboxes with client first and last name -Two textboxes with a contact 'email1' and 'email2' for the client -A combobox with a list of templates to use for the email. -A checkbox to include a copy of the statement on the email.
I want the user to be able to press a button which does the following
-Sends an email to both 'email1' and 'email2'. -Attaches a secific report as PDF if the user has selected the checkbox -The body of the email includes a greeting line using the clients name from the record. -The body of the message includes below the greeting line a html email template depending on the users combobox selection.
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
I want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?
Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".
General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.
My department and limited the users to only specific tables based on their need and role. However, users have expressed the need to view past work. I do not want to give them access to the table - I am thinking that there should be a way to create a report that shows the data, but how would I set up a search filter option so they can find the one record that they are looking for?
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
I have a search query (query by form) which is picking up keywords from a form and displaying matching results.
I want to add a tick box to the form, and if this tick box is ticked, I want the search to only include results which have a certain field NOT blank.
ie.
frmSearch will have tick box named "Website"
If "Website" is ticked on the form and the QBF run, the query will only show those records which have information in the field "Website Address" in the table tblPublication.
If the "Website" tick box it NOT ticked, the query will show all records, regardless of having information in the field "website address" or not.
can i do this in the criteria of the query?
I don't really want to do it by having a seperate query which is run by a seperate "Search" button on the form - this would be possible by having a 2nd search button (titled "Search for results with website") run a different query which has the critera that the field "Website Address" is not null.
I dont really want to have a seperate button and query as it makes it a bit messy - would rather the one query look up if the tick box, and if "ticked" then display only those with content in "website address" field, and if not ticked, display all regardless of content of field "website address".
I have one table with 4 fields TYPE CODE REASON DESCRIPTION SHORT DESR When I try to do a query to search on CODE it returns nothing. I don't understand what I'm doing wrong. Would someone please look at this DB and help> Thanks
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
I have the date on my table as 01/02/2006, there are others like this, i'm wondering how i can search for the whole month, something like **/02/2006. i have tried that way and didn;t work.
Cant seem to work this one out. I have quite a complex search form. The underlying query displays the results in a list box on the same form.
So far I have used the following expression for all the fields on my form (whether text or integer values): Like "*" & [Forms]![Frm_FrmSearch]![AssetName] & "*"
This appeared to work correctly. However, now my Asset Management System is storing a number of Equipment Type's. As one of the query criteria is Equipment Type ID it means that selecting PC (1) also displays the details for Printer (11), Scanner (12) etc......
I know why it does this (because these numbers start with a 1 and I am using a like expression). However I cannot seem to get it working.
The equipment type value is present in every record so I dont think I can use =FormValue or FormValue Is Null. I did try: =[Forms]![Frm_FrmSearch]![EquipmentType] Or Like "*" & [Forms]![Frm_FrmSearch]![EquipmentType] & "*"
but it seemed to skip the first parameter and still displayed printers etc. as before.
