FindFirst Multiple Criteria

  • Thread starter Thread starter BMoroneso
  • Start date Start date
B

BMoroneso

Hi,
I want to find the first instance of a record with multiple criteria...
CompanyID = CoID
SalesManagerID = SlsMgrID
ContactDate = Date
ContactType = Type
Not sure how to arrange all this in a find first.
Thanks in advance!
Becky
 
First is meaningless in a database unless you have some way to determine the
order of the records.

By first, do you mean the record with the earliest ContactDate?

SELECT CompanyId, SalesManagerID, ContactType
, Min(ContactDate) as
FirstContactForThisContactTypeForThisManagerForThisCompany
FROM YourTable
GROUP BY CompanyId, SalesManagerID, ContactType


If you wanted the earliest contact date by company and salesmanager and the
type of contact then you will need a subquery to solve the problem.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Here is a previous answer by Roger Carlson from the discussion group.

The FindFirst method has a parameter where you can specify a search
criteria, basically a WHERE clause without the Where keyword.
strStockNumber = "25" strWidth = "10D" strCriteria =
"[StockNumber] = '" & strStockNumber & "' And [Width] = '" & strWidth & "'"
rstShoes.FindFirst strCriteriaOf course, you would likely get your variable
values from a form or something, rather than hard-coding them. Also, be
aware that because the variables above are strings, you have to embed quote
marks in the criteria string. If they were both numbers, it would look
something like this: strCriteria = "[StockNumber] = " &
intStockNumber & " And [Width] = " & intWidthWith 3 criteria

Something like this: Dim strCriteria as String strCriteria = "[ID] =
" & Str(Nz(Me![List1026], 0)) & _ " And [Project
Name] = '" & Me![Project Name] & _ "' And
[Description] = '" & Me!Description & "'" rs.FindFirst strCriteriaIf ID
is a text value, then the criteria should look like this:strCriteria = "[ID]
= '" & Str(Nz(Me![List1026], 0)) & _ "' And [Project Name] =
'" & Me![Project Name] & _ "' And [Description] = '" &
Me!Description & "'"The above assumes that the textboxes on the form are the
same as the field names. If they are not, you will have to change it after
the "Me!". I always put criteria strings in a variable because then you can
display the value for debugging:

msgbox strCriteriaor debug.print strCriteria--Roger CarlsonJeanette
 
Sorry - here's some background. I'm importing sales contact data, which is
cumulative. I want to skip records that have been previously imported, so I
need to determine if the record exists - if that exact combination of
company, sales manager, contact date and contact type are already a record in
the database. In the import routine, I am using the findfirst method, but I
think I need to combine the find criteria in one line. I need the syntax for
that.
Thanks in advance,
Becky
 
Perhaps you would (a) consider sharing the code you have, and (b) telling us
the source type of the data you are importing

The solution could be as simple as applying a unique compound index to
company, sales manager, contact date and contact type and just ignoring the
error you get when you try to duplicate an existing record.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Here's the code that I'm working with. Source data is an excel table
imported into Access 2007.
CI.FindFirst "SalesManagerID= " & SlsMgrID And "CompanyID" = CoID And
"ContactDate = #" & SalesForce.Fields("Date") & "#" And "ContactType= '" &
SalesForce.Fields("Type") & "'"
If CI.NoMatch Then
CI.AddNew
CI.Fields("CompanyID") = CoID
CI.Fields("SalesManagerID") = SlsMgrID
CI.Fields("ContactDate") =
SalesForce.Fields("Date")
CI.Fields("ContactType") =
SalesForce.Fields("Type")
CI.Update
Else
End If
 
The Ands need to be inside the quotes.

CI.FindFirst "SalesManagerID= " & SlsMgrID & " And CompanyID" = CoID & " And
ContactDate = " & Format(SalesForce.Fields("Date"), "\#yyyy\-mm\-dd\#") & "
And ContactType= '" & SalesForce.Fields("Type") & "'"

Note, too, how I'm using the Format function on the date field. Regardless
of what your Short Date format has been set to through Regional Settings,
Access will not necessarily function with ambiguous date formats. Since you
have no control over what your users have chosen as their Short Date format,
using the Format function as I have will guarantee that it works correctly.

Finally, rename your fields from Date and Type. Those are reserved words,
and should never be used for your own purposes. Read what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html for a comprehensive list
of names you should avoid.
 
Back
Top