Multiple Options To Find Criteria

Jan 19, 2006

I'm building a database with supplier information. Some users prefer to use the supplier names, but it is sometimes hard know how foreign suppliers are spelled, therefore some users prefer to use supplier numbers.

I want my form to show all supplier data based on either the supplier number or the supplier name, which can both be chosen from 2 comboboxes.

In addition, and here's the catch, I would also like the two comboboxes to update eachother. So if I choose supplier 2 in the first box, the other box should automatically show me the name of vendor 2 and vice versa.

I think the example form shows my problem best ..

View Replies


ADVERTISEMENT

Find Replace Multiple Criteria

Nov 10, 2005

My database has a street address field that I would like find and replace a handful of certain strings with other strings. For example I would like to see:

Sunset Point = Sunset Pt
Main = Main
Prospect = Prospect
Caminito Paseo = Camto Paseo
Camino Litoral = Cam Litoral

I would like to perform something exactly like Find and Replace but to multiple strings in ONE function or query to update.

Is this possible? I looked at fReplace() but not sure where to put this.

Thank you.

STARS14

View 6 Replies View Related

Forms :: Find And Update A Record Using Multiple Criteria

May 30, 2015

I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder).I have entered the code

Code:

Private Sub SearchBtn_Click()
PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'")
End Sub

This however is just bringing up a Type mismatch error. Even though both are text fields.Also even if I just use Barcode to search which works. My update Code

Code:

DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] &

I get an Compile error saying "expected expression".

View 3 Replies View Related

General :: Find Records That Match Criteria Of Multiple Fields

Mar 12, 2013

Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b

View 1 Replies View Related

Can I Set Tools/Options/Edit-Find/Confirm In VBA?

Jun 9, 2006

Hi Guys & Gals,

I have a report that runs a number of queries via a macro in the on open property. These queries populate a table used in the report. The on close property runs a query to clear the table contents.

When a user ran this, he had a flurry of warning notices which I tracked down to the Confirm settings in the tools/options/edit find tab.

Is is possible to set these using vba?

I guess that this could be achieved using send keys, but this would probably be a bit clumsy - to say the least.

Can it be done so that the settings are changed only for the current session so that users have the original settings?

Any thoughts welcome.

Regards,

Keith.

View 10 Replies View Related

Queries :: Set Criteria By Having Combination Of Three Options - Or Statement

Aug 19, 2013

In my query, I am looking to set the criteria by having the user pick 1 of 3 options, or a combination of those three options. Here's an example:

The fields are Manager, Employee, Job Function. Let's also say that my criteria is David, John, Busy Work. I want to be able to pull results by David, John, Busy Work, or a combination of those three.

My Query for picking 1 of the 3 looks like this:

Field: Manager Employee Job Function
Criteria: David
Or: John
Busy Work

When I try to add additional Or statements, it never works. An example would be in the fourth line of the criteria, I add:

David John

Running the query produces only the results for David. Mixing it up produces only the results from the first item in the criteria (Manager over Employee over Job Function).

View 2 Replies View Related

Using Multiple Search Options

May 14, 2006

Hi guys,

I made a form to search some values in a table and print them in a report.
The form looks like this:
http://www.minezone.nl/jamie/search.bmp

When clicking the "show report" button a macro is executed. In this macro I have the following code:

OpenReport -->
Report name: main
View: print preview
Where condition: [name]=[Forms]![searchfeedback]![namebox] And [week]=[Forms]![searchfeedback]![week]

Now what I want is a bit difficult.
I want to give users the ability to search by each option and by using multiple options.
i.e.
- When filling in the name and click the show button it gives all reports for that person.
- When filling in the name and selecting the weeknumber it gives all reports for that person in a particular week.

and so on with the id field, the date field and the supervisor field.

How do I create something like that?

View 3 Replies View Related

Modules & VBA :: Filter By Form With Multiple Options

Jul 23, 2014

I am using Select Case to filter a form on open and it works fine when I have this:

Code:

Case Is = 2
Forms![Open Opportunities List].Form.Filter = "[Employee] = 4"
Forms![Open Opportunities List].Form.FilterOn = True

But if I want the form to open filtering multiple employee records, like this:

Code:
Case Is = 6
Forms![Open Opportunities List].Form.Filter = "[Employee] = 2 & " And 9 & " And 10 & " And 11
Forms![Open Opportunities List].Form.FilterOn = True

I get a type mismatch error and I am stumped as to why and how to overcome.

View 14 Replies View Related

Tables :: Autofill Fields - Multiple Options

Nov 6, 2012

I am creating an access database for my employer which handles blood donating at different venues. I have come unstuck with a particular request.

They would like to create 3 autofill fields for "dates", which are dependent on the previous field "Venues"

There are different venues, which are visited 3/4 times per year. This data is stored in a separate "Venues" table.

On the main user form, they would like to see the dates available to donate, when the Venue field is selected (this is an autofill box, from the Venue table). So if the London venue is visited on 1/1/13, 2/2/13 and 4/4/13. When the user types London into the "Venue" field then the next 3 cells auto fill with 1/1/13, 2/2/13 and 4/4/13.

View 6 Replies View Related

Using Access To Create Document From Multiple Choice Options

Mar 23, 2013

I currently write (very long) reports for work, which have a lot of standard text in them - but the standard text varies according to the client's history.

I want to create a report-writing application for myself, composed of series of yes/no/maybe buttons
and depending on whether I click yes/no/maybe in each section, a different segment of text is inserted into the report.

Ideally the application would be able to insert, or create, formatted text - such as headings, bullet point lists etc - I would need to be able to export this report into Word

I started out by making a table, with each section being a different field, and the various paragraph options as different cells in the field, but I am stuck at present on the basic functionality of "click button , insert text"...

View 3 Replies View Related

General :: Use Vlookup To Find Criteria?

Jul 6, 2012

I need want to use the vlookup function in Access 2010 when creating a query. I am doing this as an expression in design view of my query.

I want the field in which I am creating this question, to first, ask what state I am looking for (I have put this in the criteria box of the field "[State]"), then search another table (I have it created already, it is a list of the states) for that state and plug the cell with the name of that state into this new query cell.

View 6 Replies View Related

Modules & VBA :: Find First Criteria - Check Field?

Feb 6, 2014

What is wrong with this criteria...

RecSet.FindFirst ("[SomeField] = '" & mMESTO & "'") and ([CheckField] = True)

View 5 Replies View Related

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

View 4 Replies View Related

How To Find Sum Of Multiple Fields In A Table

Nov 10, 2014

i need to find sum of three or more fields in a table.

View 6 Replies View Related

Find The First Occurrence Of Multiple Record Entries

Mar 8, 2008

As my screen name implies, I am a new to Access and I’m seeking some input to resolve an issue. I have tried numerous queries, read MSDN forums and Access help w/o success. I hope someone would have some ideas.

See ATTACHMENT for screen shot of the following scenario:

Two tables in a one-to-many relationship. The parent table (tblAdmByWard) contains the autonumber primary key (PatientID) and additional fields that will not change while the child table (tblContStayEval) has a corresponding field (i.e. PatientID) to secure referential integrity PLUS additional fields as shown that are evaluated and updated daily. During the update the current date is entered (DateOfReview) along with additional info thus creating a new daily record in the child table.

I would like to query the child table (tblContStayEval) to find and return ONLY the first occurrence (DateOfReview) that the criteria (Variance) was selected…i.e.2/24/2008 as illustrated in the screen shot. This needs to occur for all (PatientID) records but exclude any subsequent and/or similar selections. By “similar” I mean the date will be different for each record in the child table while the data in the other associated fields may or may not be.

I hope I explained this okay as I’m not sure how to go about this. Any ideas or thoughts would be appreciated.

View 2 Replies View Related

Combo Box To Find Records From Multiple Tables

Jul 30, 2013

I have a main form that has fields from different tables. This is a research study, so each form enters data into different tables. Well, each record is a person's data. Instead of scrolling through each record, I need an option on the main form that allows me to search for a specific person and have their data populate into the form.So far I am able to make a combo box that pulls up the record from one table. Well, HOW do i do it from ALL the tables!?? Do I have to make a query?

View 3 Replies View Related

How To Find Record On Form Based On Multiple Queries

Jun 23, 2006

I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.

I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.

I have no idea how to accomplish this...can anyone help?

View 1 Replies View Related

Queries :: Find Difference Between Multiple Fields In Two Tables

May 8, 2013

I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].

View 2 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Multiple Criteria

Jun 22, 2005

Is it possible to have more than 9 OR criteria in an update query?

Thanks,
SKK

View 1 Replies View Related

Multiple Criteria

Jul 20, 2005

hi. my name is tim. im having some problems with a query that I am writing. it should be rather simple and involves only one table.

the table is called tblCustomer. The query involves 2 fields: cResort and cWeek. Neither of these fields are unique. Together they make up the primary key.

cResort is populated with a textfield: PF, PV, and GS.
cWeek is populated with an integer: 1-52 (the number of weeks in a year)

This is what the query is supposed to do:
SELECT tblCustomer.*
FROM tblCustomer
WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10

here's where the problem lies, this filters out ALL "PF" and ALL cWeek < 10.

I want PF's with a corresponding cWeek from 1-10. I dont want to see PFs with corresponding cWeek 11-52.

Example:
Show me cResorts: PF with corresponding cWeek:1-10.
Do NOT show me cResorts: PF with corresponding cWeek:11-52.

Is this clear? Thank you for the help.

View 6 Replies View Related

Multiple Criteria

Mar 29, 2006

I am trying to construct a fairly complex query on the fly. I have it working except for the criteria side of the query which I am having trouble with. It doesn't really matter about the vb side of it because I can alter the construction easy enough. it is more a question of how I get the criteria right.

I'll show you the SQL below first:

SELECT DISTINCTROW tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.Status, tblCandidate.CandidateID AS [Count]
FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));

The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.

This query works fine to give me all Candidates that comply with these criteria. The only trouble is I want to only results where the Candidate(s) ALL three set of criteria. I have tried replacing the OR with AND and using extra brackets to enclose the whole WHERE statement to imply I need all criteria met but this reports back no results despite me having test data where a candidate meets all criteria.

I am sure this is not as hard as I am making it but I can't figure it out at all.

I also ideally wanted to show only one record (ie one candidate that meets all instead of three records showing the candidates). This is not so important as I can work this out easily enough, by probably hiding fields I don't want to see and showing only unique values, etc...

Can anyone help me with this?

Thanks in advance,

Daz

View 14 Replies View Related

Multiple Criteria Using IIF

Apr 8, 2006

:eek: I am looking for a method that works in query criteria (NOT HARDCODING SQL) as i am quite new to this.:eek:

i have two fields for query to lookup, firstname and lastname.

THE FIRSTNAME FIELD:

IIf(IsNull([Forms]![frmSearch]![FirstName]),"",[Forms]![frmSearch]![FirstName])

THE LASTNAME FIELD:

IIf(IsNull([Forms]![frmSearch]![LastName]),"",[Forms]![frmSearch]![LastName])



Intended operation;
1) The value is seen on my search form (frmsearch) and returned to FIRSTNAME and LASTNAME query fields as NULL or VALUE.

2) If on the search form FIRSTNAME has value and LASTNAME is NULL then query should lookup a record with valid FIRSTNAME and nothing in LASTNAME.

3) If on the search form LASTNAME has value and FIRSTNAME is NULL then query should lookup a record with valid LASTNAME and nothing in FIRSTNAME.

4) If on the search form FIRSTNAME and LASTNAME have value (NOT NULL) then lookup record with valid FIRSTNAME and LASTNAME.

5) If no information is entered on the search form then "enter some information" as message box.

6) I would want wildcard using which also doesn't seem to work in IIF statement. e.g. "*" & [Forms]![frmSearch]![FirstName] & "*"

THERE FORE ---> IIf(IsNull("*" & [Forms]![frmSearch]![FirstName] & "*"),"","*" & [Forms]![frmSearch]![FirstName] & "*")



ONLY NUMBERS ABOVE (2) (3) AND (6) DO NOT WORK, IF ANYONE COULD GIVE SOME LIGHT ON THIS I WOULD BE VERY GRATEFUL, AS I AM IN THE BLUE ON THIS.:rolleyes:

TAKE IT EASY:D

View 3 Replies View Related

Multiple Search Criteria

Apr 26, 2005

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?

View 2 Replies View Related

Dcount With Multiple Criteria

May 16, 2005

Not sure if this belonged in reports or queries, so I chose general. I have looked at several DCount threads but haven't quite found my answer. I want to use Dcount in an unbound textbox in a report. It counts the number of records in another table - comparison
the first part of the statement works fine ( up to 'iss'"). When i added the between date part i'm not getting any # returned in my report. I want to addthe criteria of RecDate between the 2 dates on the open form.
Can anyone tell me where my problem lies? ( if this makes sense)

Thanks
Kevin


=DCount("[type]","comparison","[type] Like 'iss*'" And ("[comparison].[RecDate]" Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2))

View 14 Replies View Related

Multiple Criteria In Query

May 14, 2005

Hello all:

I need your urgent help with the following:

I am setting up a query in access. I have about 4 criteria for one of the fields called Center, I want the query to do the following:

Return all rows if:
Center is Like 6101*
Center is Like 61HKS56800
Ignore row if Center is Like 6101D*
Ignore row if Center is like 6101SALM01

I put in the following and I am not getting the correct results:

Like "6101*" Or Like "61HKS56800*" And Not Like "6101D*" And Not Like "6101SALM01"


Thanks,
Odun

View 1 Replies View Related







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