Queries :: Custom Search Form Query?

Jan 28, 2015

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'

View Replies


ADVERTISEMENT

Queries :: Search Form That Uses A Query To Show Results Of A Search

Aug 5, 2014

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.

View 2 Replies View Related

Enable And / Or On Custom Search Form

Jun 9, 2014

I've created a custom search form where using unbound text boxes and combo boxes I enter the criteria for a query. I would like to enhance my form to allow "and" and "or" searches on a field. For example, a product on my database has multiple features. I would like to search for products who have "feature1 and feature 2" or "feature1 or feature2". {Note: The table, Product is connected to table, Features using table, LineItem_ProdFeature where there are multiple records in Features for one record in Product}

Right now, the search form has a combo box that lists all product features. The user selects a feature and runs the query. All products with that feature are listed in the query.

It would be so great if the user could choose multiple features from the combo box. Then the query would return all products that have all of the selected features ("and" search). It would be just awesome if the user could select multiple features, and specify if they want an "and" or "or" search of those features.

View 14 Replies View Related

Searching For Value Range On A Custom Search Form

Jun 9, 2014

I built a custom search form that uses unbound text boxes to set the criteria for a query. For example, I have an unbound text box, LastName on my search form. Then in the query, I set the criteria to Like "*"&[Forms]![SearchForm]![LastName]&"*". This returns all records if the text box is left blank or returns those records that match the text box if it isn't.

I would like to do the same thing but instead of searching a text field, I want to search a number field. And I want to search for a range of values aka >10 instead of just a single value. I still need the query to return all records if the text box is left blank.

View 8 Replies View Related

Queries :: Search Query Based On Form?

Nov 13, 2013

I am unable to get multiple search output using the below code in queries, the search text is based on form :

Search form name : Find
Query name : Search
Table name : Sheet1
[forms]![find]![city]

The above works fine if it is only used in one of the fields, but as soon as I have it in multiple fields the output is null.

I am aware that i can used Like "*" code, but the thing I want to find records which are perfectly matching based on the form.

View 14 Replies View Related

Queries :: Wildcard In Query Fed From A Search Form

Jan 9, 2014

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.

View 14 Replies View Related

Queries :: Create Query That Takes Values From Search Form And Provides Records

May 5, 2015

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".

View 9 Replies View Related

Queries :: Criteria In Query Custom Field

Dec 16, 2014

I have made a function returning True/False values. I used this function in a query and now it return value Error as well...Is there a way to set criteria to values received in that field (0/-1/#Error). I've tried putting Like 0, Like True with or without quotation mark.Also every workaround comes into play as long as it works.

View 2 Replies View Related

Queries :: Custom Delete And Duplicate Query

Jun 12, 2013

I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D

I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below

Example
A B
John Doe Tires
John Doe Wipers
Allison Doe Tires
Allison Doe Tires

As you can see from the above Tires is a duplicate and need to be deleted.

A B
John Doe Tires
John Doe Wipers
Allison Doe Tires

View 7 Replies View Related

Queries :: Custom Field In A Query And Null To 0

Oct 30, 2013

Database query. I need the query to count the records of a field and display a number for the records of the field. For instance, one field is [Genre] and the other is [Show]. The query needs to list the Genres along with the number of shows for each genre. I've been able to just use the query design and add the genre field and I can add the show field, use totals count which gives me the genre counts the number of shows. My problem is the null. Some genres don't have a show listed so the genre doesn't even show up in the result. If I could get the the genres that have null shows to result a 0 it would be perfect.

View 5 Replies View Related

Queries :: Display A Custom Message Box When Open Query?

Jun 20, 2013

I want to display a custom message box when I open my query. how to do this?

View 1 Replies View Related

Custom Search??

Jun 3, 2005

I am currently creating a new part of my database which will enable users to store data for Storage Job which we do. the form has a number of fields called "pallet ref1 / pallet ref2" etc. now when the customer phones up and gives the pallet reference number, the user will need to search these fields to find out where in the warehouse the pallets are stored. But i want to create a search that will only search fields "pallet ref1 / pallet ref2" so that it will not have to search all the feilds within each record.

ne help would b appreciated

View 3 Replies View Related

Need Custom Search Ability

May 18, 2005

I support a database for some users who don't find the built in search function (ctl+f) to be useful enough.

The main data entry form of the database has fields for subdivision, lot number and address, any of which they may use to find the record they want. They're requesting that I add two types for searching:
* select sub and/or lot number as search criteria and have the selected record populate the screen
* type in a freeform address and have the record populate the screen

I'm debating about what the best approach would be to do this. Should I have a command button which opens a pop up form with the fields? If so, what is the code I will need to take the data selected/entered on the popup, run a query and then populate the underlying form? I think I need to pass parameters but I'm inexperienced at this so I need some guidance.

Also thinking that if I do create a popup form that I'll use it in the open event on the data entry form as well.

Thanks.
:confused:

View 3 Replies View Related

Standard Form Used To Display User-custom Query

Nov 15, 2006

Hi,

I have generated a standard form for displaying/editing of data and I have also created a form for generating Sql query statements (The form builds a string but does not execute the query). Can I execute a query and use it with that form.

So when I generate the the SQL statement and execute the query it loads the form (With Data) instead of a query datasheet. Thank you.

View 1 Replies View Related

Forms :: Generate Custom Form Based On Query Results

Dec 11, 2013

I'm trying to track daily production at a manufacturing company running many different processes at different locations each running multiple parallel "lines." The tricky part is that the number of lines running and the shift schedules e.g. 2shifts 10hrs/day 4days/week or 3shifts 8hrs/day 5days/week change frequently for each process.

Right now I have a form for process data that specifies the schedule and number of lines running each day. Then I'm running an append query to a "production" table that generates blank production records associated with each item made in each process for the correct shift/line combinations.

Up to now I've been manually changing the date on the append query each time I run it. Then I have a seperate query for each process that pulls out the production records for each day. My problem is that the preferred interface for production data entry is a spreadsheet with the following layout:

--------DAYS LINE 1 DAYS LINE 2 SWING LINE 1 SWING LINE 2
ITEM 1 100
ITEM 2 2250
ITEM 3

which changes each time the shift/line schedule changes for each process. The only way to achieve this layout I know of is a crosstab query which isn't updateable. Ideally, each day the manager will specify the shift schedules and forms will be automatically generated with the correct structure and sent to the process supervisors. I'm open to different form layouts and even redesigning the database completely.

View 2 Replies View Related

Queries :: Search Form In Web Database

Apr 2, 2013

I am creating a database in Access 2010. It is a web database and will be uploaded/used on our Sharepoint site.

I have created a search form, called ContactSearch. It has multiple fields, but the first field, for an example, is FirstName. It then searches the query called ContactSearchQ to display the record.

In the criteria field for the query I have:
Like "*" & [Forms]![ContactSearch]![FirstName] & "*"

It works fine...except when it comes to using it on the web database. I am sure there is a solution for creating a search form on a web database, but I have not found it yet.

View 14 Replies View Related

Queries :: Search Query With Wildcards?

Jun 15, 2013

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?

View 8 Replies View Related

Queries :: Search Form Not Showing All Results

Jun 18, 2015

I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with

Description
CarNum
SerialNum
Category
Condition

In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)

The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.

View 5 Replies View Related

Queries :: Advanced Search Form - Is Between Or Is Null

May 14, 2013

I'm creating an advanced search form which is making use of Like queries and is Null. A working example is below:

Like "*" & [Forms]![Frm_Data_Mine]![Combo_Primary_SIC] & "*" Or Is Null

So this either returns matching values from the field, or basically ignores this if left blank. All good.

I have two other fields:

[Turnover_From]
[Turnover_To]

Both are numeric values. What I would like to achieve is the same as the above, so I can search between a from and to value, or if left null, ignore them. I had a go with the below but it's clearly not right, and I can work how where and how I insert the Like statement which makes the others work.

Between [forms]![Frm_Data_Mine]![Turnover_From] And [forms]![Frm_Data_Mine]![Turnover_From] Or Is Null

View 8 Replies View Related

Queries :: Can Have Two Tables Linked To One Search Query

Nov 28, 2013

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?

View 4 Replies View Related

Queries :: How To Create Query To Search For Computer By Name

Jun 17, 2013

I am relatively new to MS Access. I am wanting to create a query that allows me to search for a computer by name.

I have two fields in the Query. Main user and Computer Name.

I would like it when you open the query it asks you to input and name then once inputted display that persons computer name.

View 1 Replies View Related

Queries :: Parameter Query To Search For Number

Feb 11, 2015

I have 2 tables: Table A contains [ID] (and other columns) AND Table B contains [Request ID] (and other columns).

However, [Request ID] contains several number separated by comma. For example: 10, 15, 20 etc.

I created a query with parameter with : Like "*" & [ID] & "*" under [Request ID] criteria.

Here is my problem:

if [Request ID] = 44, 60 then it is returning [ID] 4 records (4, 6, 44, & 60). Is there a way to show only 2 records? 44 & 60?

View 4 Replies View Related

Queries :: Search Across Multiple Tables With One Query

Apr 18, 2013

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.

View 7 Replies View Related

Queries :: Search Function In Parameter Query

Nov 18, 2014

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.

View 2 Replies View Related

Queries :: Search Form Displays Blank Table

Feb 20, 2015

I have made a search form for my company, where all the data is put simply into the tables and i have made a search form to search with the selected fields, it was working at first, but now it just displays blank table when i search.I am using ms access 2007.Here is the sql of the query::

Code:
SELECT
ClientT.[Project Number],
ClientT.[Client Surname],
ClientT.[Client Name],

[code]...

View 4 Replies View Related

Queries :: Wildcard Search Does Not Work With A Form Control

May 24, 2015

I am trying to get a wildcard search to work with a form.

I have a query, in which the criteria is:

Like "*" & [Enter a word] & "*"

That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:

Like "*" & [Forms]![Myform]![Mycontrol]&"*"

What am I doing wrong?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved