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 Replies


ADVERTISEMENT

Tables :: Converting Excel Table For Lookup Of Values Based On Row And Column Headers

Sep 3, 2014

how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?

View 12 Replies View Related

Modules & VBA :: Restrict Value In Lookup Field To Only Include Values From Table B Not In Table A?

Feb 2, 2014

I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.

This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.

How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?

View 5 Replies View Related

Modules & VBA :: DLookup - Wildcard Search On Both Sides?

May 27, 2015

When trying to match people's names - sometimes with a middle initial, sometimes without - I'd like to try to wildcard each side of it.

So this

Code:

varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME Like '" & varString & "*'")

but also something like this.

Code:

varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME* Like '" & varString & "'")

View 8 Replies View Related

Modules & VBA :: Edit A String Or Use Wildcard In Search Function

Jul 18, 2013

Access 2007

I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.

Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.

Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.

Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()

[Code] .....

View 2 Replies View Related

Modules & VBA :: Search Form Will Not Return Exact With Wildcard SQL

Jul 4, 2014

I did export the table data into a tabbed delimited format and will include that at the end. If you want to reproduce my bug copy that data in a txt file and import that into the table tblMain. Make a search form and a sub form. the sub form is linked to the table and the main form is unbound with two search buttons.

Problem Statement:The code works fine. I did find a bug that seems to arise with the wild cards when the entire field values are entered. You can replicate the bug by testing the search criteria listed below.

This is a brief example of the bug. A detailed description is near the code below.

If my name is "Devtycoon" and I search "Dev" the SQL statement will build "*Dev*" and it will pull up my name, "Devtycoon". On the contrary if I search "Tycoon" the SQL statement will build "*tycoon*" and it will pull up my name, "Devtycoon". If i search "DevTycoon" the sql statement will build "*Devtycoon*" and no results will be returned. That is buggy because the name is in the database but no wildcards would be needed.

Table structure:

tblMain
ClientID
Surname [text]
Organization [text]
ProgramTitle [text]
City [text]
State[text]
Zip4 [text]
Telephone [text]

Form1 contains the controls for search criteria. Three text box controls are used to filter a sub form control called "DS". The sub form is called sFrmMain and is a datasheet that shows results of the search. there is a button that runs the code and another button that clears search criteria and shows all records. Both button's code set the sub form's record source using an SQL string built using a function that returns a segment of the overall search string using the contents of each control that then is concatenated into a SQL statement used to set the record source.

There is a function for the following components of the SQL statement

SELECT / FROM
WHERE
controlA = me.txtSurname
controlB = me.txtOrganization
controlC = me.txtProgramTitle
This is how you can replicate the bug.

I tested two additional surnames organizations and program titles as follows:

Try example (1). you will get both the 4's and the 14's records returned.

If you type in letter for letter of the second record (the one with the 14's) no records populate. It is like the wild card does not like it if you type in the entire field value. Uncle Gizmo's and Allen Brown's method do the same thing were no records populate if the 14's entire surname organization and program title are entered into the text controls. Can you reproduce this error? Other than that I think either method is bulletproof.

Example criteria

1) put the following criteria in each control then hit the search button

4 surname
4 organization
4 program title
[two records returned]

2) put the following criteria in each control then hit the search button

[no records returned]
14 surname 14
14 organization 14
14 program title 14

Code behind search form:

Code:
Option Compare Database
Option Explicit
Private Sub cmdShowallRecords_Click()
Dim strSQL0 As String
Me.txtOrganization = ""
Me.txtProgramTitle = ""
Me.txtSurName = ""
strSQL0 = fSQL_SelectFrom & fWhere & fSurName & fOrganization & fProgramTitle
Me.DS.Form.RecordSource = Left(strSQL0, Len(strSQL0) - 5) & ")"

[code]....

View 12 Replies View Related

Tables :: Limited Number Of Values In Lookup Column

Nov 27, 2012

I have a database for scheduling students' for tests. They can take up to six tests in a day. There are about 80 different tests that they can take.

In my table, I created columns titled Test1, Test2, Test3, etc. They are lookup columns and I chose to enter my own values, putting in the tests titles for the values in each column.

When I add these drop-down lookup fields onto the form, it will only display 37 of these values. When I go back to the table and select "edit list items," it shows that it did cut the list off at 37, even though originally it allowed me to enter all 80-ish titles.

Anyway. It appears that there are limited values you can have in a lookup column, though after doing a lot of searching online I can't find anything to indicate that is true.

It seems to me that it would be smarter to set this up with two different tables, storing the reg info in one table and the test titles in another table. However, I am having a hard time figuring out the relationship aspect of this solution and how to make it pull up the correct values for queries/reports as well.

View 6 Replies View Related

General :: Unique Values For Two Column Combobox

Jul 18, 2013

Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.

What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail.

View 14 Replies View Related

Modules & VBA :: Check If Values In Column A Of Excel Sheet Also Present In Access Table

Sep 2, 2014

In sheet 1, In column A all the Batch numbers are present. So I want to check if each of these is present in Access table named "tblmain". If its present then display its corresponding Policy number in Column B of attached workbook.

View 1 Replies View Related

Modules & VBA :: Search Table With Attributes - Extracting HTML Values

Oct 3, 2013

I have a table in msaccess which i would like to use as a search term to search values in another table. What i wanted to do is search the table with attributes and save all found attributes to a new table with its primary id.

Search_Keyword_Table
Id ---- Search_keyword
1 ----- Size - S
2 ----- Size - M
3 ----- Size - L
4 ----- Size - XL

Table to be searched

Id ----- Attributes
1 ----- <select name="attribute" id="attribute"><option value="Size - M">Size - M</option><option value="Size - L">Size - L</option></select>
2 ----- <select name="attribute" id="attribute"><option value="Size - S">Size - S</option><option value="Size - M">Size - M</option><option value="Size - L">Size - L</option></select>

Saved table results
Id ---- Attributes_found
1 ---- Size - M
1 ---- Size - L
2 ---- Size - S
2 ---- Size - M
2 ---- Size - L

View 2 Replies View Related

Forms :: Single Column Combobox To List Values From Multiple Fields

Apr 8, 2013

I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.

All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.

View 1 Replies View Related

Modules & VBA :: How To Remove Values From Lookup Values

Feb 23, 2015

I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.

View 2 Replies View Related

Populating A Column In A Table Based On Values In An Existing Column

Mar 5, 2007

Hi all,

In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc


I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.

So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc

How do I do this?

Kind Regards,

Matthew

View 1 Replies View Related

Modules & VBA :: Lookup Column In Text Box Access Form

Apr 5, 2015

I have 1 combo box and 1 text box i look up 2 columns in the combo box from that combo box i want to look up 2 column to text box

example:
Table values:
Col 1 Col 2
A 1
A 2

combo box successfully look up 2 columns but i look up to text box

Formula: =combo1.column(1)

But the text box look up the first row always even i choose the second row A

Also look-up first row 1

Any solution to look up 2nd row?

View 3 Replies View Related

Wildcard Within A Combobox

Nov 20, 2013

I have a combobox with 2 columns where I manually set the following criteria:

"E";"English"
"F";"French"
"EF";"Bilingual"

In my employee table under the Languages field I have E,F or EF

I wanted to display in a sub-form a list of employees who speak a specific language but I can't use a wildcard in the combo-box properties. Basically when someone is looking for an English speaking employee they should get both E and EF employees not only E.

I tried doing "E*";"English" but it doesn't display anything.

View 10 Replies View Related

Tables :: Requery Combobox Lookup On Table Datasheet

Feb 20, 2014

When designing a table I've created a field and set its lookup properties to display a combobox with a row source that returns a DISTINCT set of values already entered into the field.

After a row insert or row update the combobox needs to be required to ensure its list is complete.

If I create a form to display my datasheet this is easy. But I'd prefer to enter data directly into the table datasheet directly. I need to enter simple data into about 20 different tables and I'd prefer not to create 20 forms unless it's really necessary.

The lookup wizard generated entries similar to those I'd previously created manually, except the wizard generated a couple of extra settings that appeared briefly that were not part of the regular set of lookup tab properties. These additional settings referred to 'update propagation'. Once they'd disappeared I couldn't see any way to get them back..

(I am using Access 2013)

View 3 Replies View Related

Table That Depends On One Another Lookup Column

Nov 6, 2014

I have a table machine with fields:
ID(PK)model ( lookup column to machine model)serialnotype

Then a table meter reading:
IDmachineserialno (lookup column to tblmachine/serialno)readingdatetotalcounter

I want it to be like when i press lookup column in tblmeterreading/ machine serialno....it shall show the machine serial no|model in the popup combobox.

Instead it shows my machine serialno|id,

for example: when I select the machineserialno. a combobox shall show:
s/n:221233 | sony

instead it show:
s/n:221233 | 1

??

View 4 Replies View Related

Modules & VBA :: Filter Subform By Nth Column In Combobox?

Aug 5, 2015

I am currently having trouble filtering my subform by a different column than the bound column set in properties.

the comobobox shows the ID for the last email sent, with the combobox drop down showing the name and date of email when dropped down. I have tried 2 things and neither work...

1) Calling the filter on the combo-box column itself:

Code:

Dim myDate as string
myDate = [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]
DateFilt = " AND" & myDate & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"

I have used similar code on another form, but I can't get the myDate variable to get to value of the 6th column in the dropdown (Date Of Email). an Easier way to look at this would be:

Code:
DateFilt = " AND [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]" & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"

2) Inside the subform, I have set up a seperate field that reads the result of the dropdown box column(5) and shows it. For example the Email with ID 22 has a date of 4/8/15, so any record with last sent email being 22 has a record that says 4/8/15. I am trying to use this value to filter between, but am unable of passing the value to my filter.

The textbox is called "Email Date" and the Control source is "=[Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column](5)"

It shows the correct data, but does not allow me to filter by this field, when the apply filter button is pressed it asks me for the parameter value of the LastEmail Field.

Code:
DateFilt = " AND" & " [EmailDate] BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"

I use a number of set variables for my filters, so my filter ends up looking like

Code:
.Filter = IDFilt + EmailFilt+ DateFilt

where all but IDFilt begin with AND. I use this on a number of other forms so I am sure this is not the issue!

View 3 Replies View Related

Linking Result From Lookup Column In A Form To The Values Form?

May 29, 2012

I am creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.

We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.

I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier.

View 7 Replies View Related

Crosstab Queries, Column Headings And Lookup Table

Aug 22, 2006

Not sure if there is a quick answer but I am trying to complete a crosstab query that references a lookup table. I cannot remove the lookup tables because the database was designed by a consultant. The lookup table is referenced as the column heading. The query works fine until I change the column headings in the properties box - it returns the column headings but there are no values. Am I doing something simple wrong or is it having troubles because it is a lookup table for column headings.

View 3 Replies View Related

How To Make A Table Lookup Field Depend On Value In Another Column

Sep 20, 2012

I have a table called Locations that lists Countries and Cities:

USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans

I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).

View 5 Replies View Related

Modules & VBA :: Syncing Combo Boxes Which Are Also Lookup Values

May 1, 2015

I have two tables,

tbl_Retainer
tbl_Retainer_Grant_Funding
tbl_Retainer has the field,
Retainer_ID

And tbl_Retainer_Grant_Funding has the fields,
Retainer_ID (a lookup field from tbl_Retainer)
Agreement_Num (a lookup field from tbl_Grant)

I have a form based off of a query(not sure if that matters), that uses that tables, tbl_Assignment and tbl_Assignment_Grant_Funding. These tables have the above fields as lookup fields.

So...what happens is, if an Assignment has a Retainer, I want the Agreement_Num box to show only the Agreement_Num's associated with that Retainer, otherwise just show all the Agreement Num's.

In my form, I have Retainer_ID with the row source,
SELECT tbl_RETAINER.Retainer_ID FROM tbl_RETAINER;

And Agreement_Num with the row source,
SELECT [tbl_GRANT].Grant_ID, [tbl_GRANT].Agreement_Num FROM tbl_GRANT ORDER BY [Agreement_Num];

In my AfterUpdate event for Retainer_ID I have,

Private Sub Retainer_ID_AfterUpdate()
Dim strSql As String
strSql = "SELECT [Retainer_ID]," & _
"[Agreement_Num]," & _
"FROM tbl_RETAINER_GRANT_FUNDING" & _
"WHERE [Retainer_ID] = " & Me.Retainer_ID.Value

Me.Agreement_Num.RowSource = strSql
Me.Agreement_Num.Requery
End Sub

When I am in my form and choose a Retainer ID, the Agreement_Num box goes blank, and there are no choices to choose from. I am wondering if this is because the Agreement_Num's are sourced from tbl_Grant and not from tbl_Retainer_Grant_Funding.

View 6 Replies View Related

Lookup Values In Another Table

Jun 13, 2006

BACKGROUND

I am desperate to solve this problem but unfortunately I have not been able to figure it out. Below I will outline a design of a database and the desired results.

I know what I want to do but I don’t know how to do it (or whether it is impossible!)

DATABASE DESIGN

The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.

Staff Data
Contains daily data for several members of staff
Staff ID
Staff Name
Date
Data Field 1
Example records:
600-001, Bob Smith, 01/03/2006, 50
600-001, Bob Smith, 02/03/2006, 50
600-001, Bob Smith, 03/03/2006, 50
600-001, Bob Smith, 04/03/2006, 50
600-001, Bob Smith, 05/03/2006, 50
600-002, Jayne Cole, 01/03/2006, 60
600-002, Jayne Cole, 02/03/2006, 60
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
600-002, Jayne Cole, 05/03/2006, 60
600-003, Alex Winter, 01/03/2006, 20
600-003, Alex Winter, 02/03/2006, 20
600-003, Alex Winter, 03/03/2006, 20
600-003, Alex Winter, 04/03/2006, 20
600-003, Alex Winter, 05/03/2006, 20

Team Lookup
Shows what team each staff member belongs to and what date this is effective.
Staff ID
Team
Start Date

Example records:
600-001, Sales, 01/01/06
600-002, Sales, 01/01/06
600-003, Accounts, 01/01/06
600-002, Accounts, 04/03/06

Please note:

The first three records show that at the start of the year Bob (600-001) and Jayne (600-002) worked for Sales and that Alex (600-003) worked for Accounts.

The last record shows that from 04/03/06 Jayne switched teams to Accounts

Query Assign Team
Assigns the correct team to Staff ID for each date
Staff ID
Staff Name
Team
Date
Data Field 1
Desired Results:
600-001, Bob Smith, Sales, 01/03/2006, 50
600-001, Bob Smith, Sales, 02/03/2006, 50
600-001, Bob Smith, Sales, 03/03/2006, 50
600-001, Bob Smith, Sales, 04/03/2006, 50
600-001, Bob Smith, Sales, 05/03/2006, 50
600-002, Jayne Cole, Sales, 01/03/2006, 60
600-002, Jayne Cole, Sales, 02/03/2006, 60
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
600-002, Jayne Cole, Accounts, 05/03/2006, 60
600-003, Alex Winter, Accounts, 01/03/2006, 20
600-003, Alex Winter, Accounts, 02/03/2006, 20
600-003, Alex Winter, Accounts, 03/03/2006, 20
600-003, Alex Winter, Accounts, 04/03/2006, 20
600-003, Alex Winter, Accounts, 05/03/2006, 20

Query Group By Team
Summarises data by team/date
Team – Group By
Date – Group By
Data Field 1 - Sum
Desired Results:
Sales, 01/03/06, 110
Sales, 02/03/06, 110
Sales, 03/03/06, 110
Sales, 04/03/06, 50
Sales, 05/03/06, 50
Accounts, 01/03/06, 20
Accounts, 02/03/06, 20
Accounts, 03/03/06, 20
Accounts, 04/03/06, 80
Accounts, 05/03/06, 80

PROBLEM: WHAT I AM TRYING TO DO

I don’t know how to get the query “Query Assign Team” to work!!

I would like to lookup up the ‘Staff ID’ and ‘Date’ in “Team Lookup” and return the appropriate value for ‘Team’

If the only two records in Tbl Staff data were:

600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60

I want the query to return:

600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60

Can anybody help me?

Should I be using DLOOKUP? If so, how?
Is VBA the only way around my problem? If so, can you tell me what it is?
Am I attempting the impossible?

View 9 Replies View Related

How To Lookup Values In A Table And Add Them To Another?

Jan 22, 2007

I just started a new database and I'm new at this so I have a question about ID numbers for my two tables.

In Table1, I have faculty demographic information, and a FacultyID (unique ID number created by AutoNumber).

Table2 has the courses those faculty members taught - one faculty teaches many courses. But this table does not have the FacultyID that I added to Table1.

So, how do I automate the process of looking up the FacultyID number from Table1 and adding it to the currently blank FacultyID field in Table2? Theres hundreds of records and this will be done every semester, so I need an automatic way of doing this lookup.

Any help would be appreciated. Thanks.

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

Forms :: Cascading Combobox - Adding Column To Table?

Aug 4, 2014

I have a form with two cascading comboboxes where the first selection is the alphabet and the second selection is a person's name. (the alphabet is a coding system but not assigned based on a person's name) I used one table based on example 2 of this website: fontstuff. com/access/acctut10.htm

What I want to do is add columns to the table such as organization, purpose, etc. then have them in the form as textboxes that update as soon as the second combo box is selected. Is this possible with just one table or do I need to go a different route?

View 11 Replies View Related







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