Forms :: Advanced Search (MultiField) - Add Combobox?

Nov 21, 2013

I created Advanced search (Multi- Field) form and its work perfect but I want to add a combo box for search [Date Removed] and the options is "Active" and "Inactive".

my table include:
[Location]
[Date Installed]
[Installed by]
[EquipmentTag]
[Date Removed]
...

The users may not enter [Date Removed] which mean its Null ( also which mean in my project company / Combo box its "Active" ).

In another hand, if users enter [Date Removed] which mean it's not Null ( also which mean in my project company / Combo box its "Inactive").

In short, if user chose "Active" it will search for nulls/empty column in [Date Removed]
if user chose "Inactive" it will search for filled column in [Date Removed]

The code is attached:-

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conJetDate = "#mm/dd/yyyy#"

[Code] .....

View Replies


ADVERTISEMENT

Multifield Keyword Search

Jul 7, 2006

HI, Im trying to create a keyword search based on the data in several fields. The below command works well for searching one field, but I want to search several at once. Any help would be great. Thanks!

What you need is a parameter query. In the criteria space in the desired field in your query, put the following: Like "*" & [Search by word:] & "*"

When you run the query, a message box will pop up that says "Search by word:", and then you could type in "mountain" or "goat" or even just type in one or two letters. To make it more/less specific, play around with those wildcards.

View 5 Replies View Related

Advanced Search Box

Dec 27, 2005

I want to make something similar to the Google search box, although not quite as complicated.

I would like to be able to type more than one word in a search box.
I am only searching on 1 field [Title]. The query would show all the records with the first search word in the Title, plus all the records with the second search word in the Title.

I have a query now that has the below in the Title field criteria:
Like "*" & [forms]![Searchform]![searchcontrol] & "*"

This searches for the word typed into the searchcontrol no matter where the word is in the title. It works great, but I can only type in one word at a time.

View 1 Replies View Related

Advanced Search More Than 1 Table

Apr 15, 2008

Hi,

I'm a newbie (I know nothing in VBA & SQL but I'm learning) and I've a database of 10 tables. I want to make a query & form to search through these tables and display results(in a form & report) according to the search criteria.

example of my request is attached..

I can PM you my real database if you need it.

Help is appreciated

View 1 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

Advanced Searching Through Forms

Mar 1, 2006

ok, i have just had a lengthly conversation with my user and i need an advanced search system.

what i would need is a search function, that does not only list the full field details from each record in the combos..

here is what i mean.. i have a quotation form with different locations and prices in a list box. i have a text box at the top of the form with an onchange event that alters the results in the list box.

so if the user types 'a'.. only the axxxxx records are displayed.. if they type 'ab' only the 'abxxxx records are shown.. and so on.

because my customer may not remember the exact address of their pickup or destination, i cant use this system, because i wont know the first letter of the details previously entered..

eg.. a user may have been picked up from '5 house street, new hampshire crescent.

if all the customer remembers is 'hampshire crescent.. then the above system will not reveal my record..

therefore i would need a search box at the top that allows me to search for any word (or identical consecutive characters) within a chosen field.

can this advanced string search be done in access?

View 3 Replies View Related

Forms :: Creating Advanced Form With Comboboxes

Aug 4, 2013

So, I have a form with 4 combo boxes and 1 field for a date.

I'd like to be able to choose an option from one or more comboboxes and create a report of all of the records matching those criteria.

Will this require VBA Coding or can I do this without it? I don't know VBA

I know I will probably have to use Queries and the Like criteria but I'm not sure where to begin and how to do it.

View 1 Replies View Related

Combobox Search

Jul 13, 2005

I've seen comboboxes before that by typing the first letter of the word your looking for it will go to the first word matching that letter, but then by typing that letter again it will go to the next word starting with that letter:
Such as in a "Choose Your State" box by typing "M" it selects Massachusetts then by typing m again it selects Maryland, the third time it selects Maine... and so on. I would like to add this to a cbobox on a form but havent had any luck. I have searched the forum but didnt find anything pertaining to this. Any help or suggestions on where to get this info is greatly appreciated.

JO

View 1 Replies View Related

Combobox Search

Jun 29, 2005

I know there are threads on this issue but I have searched and can't find them. I'm not coming up with the right search string so I apologize for asking something I know I should be able to find. I;m trying to find the code for typing the first few letters of a word-Name, city, etc in a combobox and have combobox bring up with those letters. Can someone tell me code or suggest the correct search string to search the forum?

View 2 Replies View Related

Forms :: Update Row-source Of Combobox Based On Value Selected On Another ComboBox?

Apr 26, 2015

I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.

Code:

Private Sub Manufacturer_AfterUpdate()
If (Me.Manufacturer.Value = "Siemens") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SeimensTable"
Me.Model.RowSource = "SELECT Model FROM SeimensTable"
Else
If (Me.Manufacturer.Value = "Samsung") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SamsungTable"
Me.Model.RowSource = "SELECT Model FROM SamsungTable"
End If
End If
End Sub

But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?

View 5 Replies View Related

ComboBox Search Not Working

Dec 31, 2004

I have a database of daily reports.
There are three shifts per day. 0800-1600, 1600-2400, and 2400-0800.
I ran the combobox wizard and asked it to "find a record based on the value I selected in my combo box."
I chose (and I know these are misnamed fields) field 3 which is the date field and field 2 which is the shift field.
Then I tested the combo box.
I click on the down arrow of the combo box the date and shift is displayed. For some reason tho when I select a date/shift, eg. 12/12/2004 | 1600-2400 the right record pops up as far as the date is concerned but the 2400-0800 shift record comes up. I select 11/25/2004 | 0800-1600 and again the right date but the same 2400-0800 record is displayed. Why is the right date but the wrong shift being displayed?

BUMP

View 7 Replies View Related

Search (While Typing) In A ComboBox?

Dec 5, 2014

My goal is during typing in combobox to can search by keyword in the combobox it self.

View 6 Replies View Related

Form With Combobox To Search Record

Mar 4, 2005

Hi,

I am trying to build a form that allows the user to INPUT stock as it arrives. This is simple with a product form that shows all the products in the table. I just go to the quantity field and change it.

But, I want to create a 'search' function in the field. I want to allow the user to type in the Product number. Then the Product name, Product Price and Quantity is AUTOMATICALLY displayed.

The user can then change this value (items in stock).

Thats pretty much the jisst of what I wanna do.

Can anyone help?

Thanks in advance.

View 7 Replies View Related

Entering Data Using ComboBox For Search.

Nov 17, 2006

Hello,

Below is the main data entry form of my application. We disrtibute a product called MC Cloth to Shops to display. After a month we visit again and take stock of products sold, replenish and the shop pays for the products sold.
the Database keeps a record of the shop, products displayed, refilled, sold and respective payments.

The dtabase and the form is loaded in Handheld (PDAs) by the sales people who enter data during the visit and then synchronise with a master on return


http://affiliatesexcel.com/MC_main_form.jpg

I need to sum up values in one field for example MC Refill from the first record till the new record and show it in another field, for example MC Refill Total

Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).

However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty.


NameCust_IDRecordNumAddress
whs01ggggggggg
02qqqqqqq
03mmmmm
04nnnnnnnnn
05ooooooo
06pppppp
Kickstart08xyz
09898989898
012mmp
013qty

This makes the search function impossible based on the NAME value.
As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)


Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down .
(I have tried a mehod which entered the values for
all records but this clutter up the Pull down with SAME
Name for repeated records making the PULL DOWN unusable.)

I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.

+++++++++++
Code:

Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
' If the matching record is found, the procedure goes to it.
' If the record isn't found, the focus stays on the current record.

Dim Criteria As String ' This is the argument to the FindFirst method.
Dim MyRS As Recordset ' Recordset used to search.
Dim ComboName As String ' The name of the company to search for.
Const IDYES = 6

Set MyRS = Me.RecordsetClone
' Build the criteria.
ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
Criteria = "[Name]=" & ComboName
' Perform the search.

MyRS.FindLast Criteria
If MyRS.NoMatch Then

Response = MsgBox("Could not find the Supplier Name: " & ComboName & " Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
If Response = IDYES Then
MyRS.AddNew ' Create new record.
MyRS("Name") = Screen.ActiveControl
MyRS.Update ' Save changes.
MyRS.Move 0, MyRS.LastModified ' Go to new record
Me.Bookmark = MyRS.Bookmark ' Go to new record
Else
GoTo Endsub
End If
Else
MyRS.AddNew ' Create new record.
MyRS("Name") = Screen.ActiveControl
MyRS("Cust_ID") = MyRS("Name")
MyRS.Update ' Save changes.
MyRS.Move 0, MyRS.LastModified ' Go to new record
Me.Bookmark = MyRS.Bookmark ' Go to new record

'Me.Bookmark = MyRS.Bookmark

Dim recNo As Long

' for this to work there cannot be any RecordNumber with a value of 0
' it finds the highest record number for the name in the combo box
' and returns 0 and exits if no record found.
recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
If recNo = 0 Then
Exit Sub
End If
Me.Text90.SetFocus
DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent


End If
Endsub:
MyRS.Close


End Sub

+++++++++++++++++++++++
The question is is there an easier way to
achieve the summing function ?

Any help is greatly appreciated as always.

--------------------------------------------------------------------------------

View 14 Replies View Related

What Happens When A Combobox Search Has Multiple Results

Jan 22, 2007

When you put a combo box to search the values in a form what happens if there are more than one (in my case) name that matches the criteria. ie: if you search for Smith and there are 10 people with the surname of Smith it brings up the first record. is there a way of doing adding a "search again' function so you can look for another Smith?

View 3 Replies View Related

Form Search Combobox - Not In List Message

Jan 24, 2007

When you use a combobox to search values in the form - and someone searches for a value that isn't there - the form just defaults to the first entry. Is there a way of putting a message box that says "This is a new patient" (or something like that). The NotInList event doesn't work - any ideas??

View 2 Replies View Related

Combobox Search Defaulting To Primary Key Rather Than Selection?

May 3, 2013

I have generated a combobox search for my form and I am able to select names that are different and the information is generated on the form. However, when I have two names that have the same last name my selection will default to the primary key instead of the name I selected in the combobox. Here is the code I am using:

Here is my RowSource: SELECT [Client Info].CltLname, [Client Info].CltFname, [Client Info].DOB, [Client Info].[Last 4 SSN] FROM [Client Info] ORDER BY [Client Info].CltLname, [Client Info].CltFname;

I have run a query to sort by CltLname and then by CltFname

Private Sub cboClientSearch_AfterUpdate()
'Moves to Customer Name text box and
'finds the record of whatever name is selected in the combo box
DoCmd.ShowAllRecords
Me!CltLname.SetFocus
DoCmd.FindRecord Me!cboClientSearch
'Set value of combo box equal to an empty string
Me!cboClientSearch.Value = ""
End Sub

how to get the combobox to display what I have selected instead of defaulting to the earliest primary key associated with the same last name?

View 11 Replies View Related

Combobox Won't Auto-search When Multiple Periods Are In Entry

Jul 27, 2005

I'm having trouble finding an entry anywhere about this, although I'm sure I'm not the first person to come across it...

When I am typing my entry into a combobox it begins to autosearch, highlighting the part of the entry that it finds that matches the beginning that I typed in (as a normal combobox does in access).

However, when the entry it finds has multiple periods in it (i.e.: S.T.A.R) it stops searching and does not highlight the remainder of the entry it moved to. I'm not sure that searching is the correct term, which may be why I can't find any information on this, but is there a way to correct this problem?

View 2 Replies View Related

Modules & VBA :: Continuous Form Combobox Search As You Type

Jul 28, 2014

What i want is to have a combo box on a continuous form that as you type it filters the Query it is based on using a Like *. and drops downs so the user can see the updated list after each key press? Is this possible?

View 4 Replies View Related

Modules & VBA :: Wildcard Search Of Combobox - Lookup Values In Table Column

Nov 19, 2014

I have a form which has a combobox called Task_Ref which looks up values in a table column.

I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-

Test number 1
Yesterdays Test

As long as the word "Test" appears I would like the above to happen?

I was thinking of something along the lines of:-

If InStr(Task_Ref.Value, "Test") > 0 Then
P1.Value = True
Else
P1.Value = False
End If
End Sub

But this hasn't worked

View 4 Replies View Related

Queries :: Add A Box On Search Forms To Search 3 Cells Of Record For A Keyword

Jun 10, 2013

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.

View 3 Replies View Related

Forms :: Search Text Box With Option Box As Criteria For Search

Mar 4, 2014

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.

View 3 Replies View Related

Forms :: Search Combo Box To Search For A Field On Form

May 7, 2013

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.

View 5 Replies View Related

Advanced Customization

Feb 2, 2008

This should be a piece of cake for someone with a few experience years on me. I have a client for which I am building a custom database. They use this for various reasons. One main reason is to track man hours, cost, billing information on a per job/customer basis.

Each of their customers has a unique contract. Which specifies the information required in order to bill the customer for a job. For example: Customer "Big Factory" requires PO# and Job#.

The contract also specifies the billing rate for each piece of equipment and each type of employee. For example: For customer "Big Factory" again, we charge $10 a day for a pickup truck and $20 an hour for a welder. Customer "Little Factory" has a different contract with different rates and required fields.

*Now I have all of the above under control (in my opinion)*

Following is part is where I struggle. Each contract also specifies which hours (on a daily basis) are standard work hours and which are premium work hours, as well as, at what point (# of weekly hours) to start billing only premium hours during the remaining part of the week. For example: "Big Factory" Standard hours are from 07:00 to 17:00, Premium are from 17:00 to 07:00, Weekly premium start once an employee has worked 40 hours for that week regardless if the employee has worked some hours for another client(*Exceptions do exist where the employee must work 40 hours for the client before the weekly premium kicks in*).

I need this to automatically calculate for each employee. It must update each time sheet that the employee exists on.

Currently I have it working in a rather unorthodox way, but it isn't exactly scalable to work for each customer's unique contract. I can post how I have it, but it will be quite a long post. It has to do with a running sum query then a make table query then an update query.

One other caveat: My client pays their employees premium pay on a 40 hour work week (>40 per week is premium time) regardless of which hours they work. (Seems like they are ripping the employees off I know). I posted this once before, but had received only a negative post chewing me out for not being fair. Anyway...

If presented with this how would you do it?

Any help is greatly appreciated.
Thanks,
Eric

View 6 Replies View Related

Advanced Query Help

Feb 15, 2006

The below Stored Procedure was refined to incorporate a UDF. My problem now is that I need it to be even more dynamic. I ideally need the user to have the option to select which fields are incorporated into the query. I have the front end framework although not sure currently how to make field selection dynamic...

CREATE PROCEDURE dbo.sp_ClientQuery

(
@inputRegion varchar(500),
@inputPub varchar(500),
@inputCountry varchar(500)
)

AS


SELECT dbo.tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City, dbo.tblPublication.pName
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(@inputRegion,',') )) AND
(dbo.tblPublication.pName IN (select value from fnSplit(@inputPub,',') )) AND
(dbo.tblClient.Country IN (select value from fnSplit(@inputCountry,',') ))
GO


This is becoming pretty complicated so any help appreciated.

Phil

View 1 Replies View Related

Advanced Sql Query

Jul 26, 2006

Hi Guys

I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,

I have data as follows...

FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..

Second problem is i need to take this data and transform it to look like the table below

RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut
411 07/23/200620:45:02iGuard# IN
421 07/23/200620:46:17iGuard# OUT
431 07/23/200620:48:08iGuard# IN
441 07/23/200620:48:18iGuard# OUT
451 07/23/200620:48:24iGuard# IN
461 07/23/200620:48:30iGuard# OUT
471 07/23/200620:48:36iGuard# IN
481 07/23/200620:48:41iGuard# OUT
501 07/23/200620:49:57iGuard# IN
511 07/23/200620:50:14iGuard# OUT
521 07/23/200620:59:34iGuard# IN
531 07/23/200620:59:40iGuard# OUT

Employee IDDateInOutIn OutTotalTimeIn
123/07/200620:3520:3620:3820:3900:02


Basicaly i need to transpose it..

If anyone has the sql or knows the sql on how to do this i would be very greatful

View 3 Replies View Related







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