linking criteria

  • Thread starter Thread starter Mark Hepburn
  • Start date Start date
M

Mark Hepburn

Hi, it must be monday morning blues but i cannot get this
sorted out.

I have the following lines of code (each is a seperate
section):

rem Client Group
stLinkCriteria = "[client group] like '*" & Me![txtSearch]
& "*'"

rem Name
stLinkCriteria = "[owner] like '*" & Me![txtOrgSearch]
& "*'" & " OR " & "[name] like '*" & Me![txtOrgSearch]
& "*'"

rem Address
stLinkCriteria = "[address1] like '*" & Me![txtAddress]
& "*'" & " OR " & "[address2] like '*" & Me![txtAddress]
& "*'" & " OR " & "[town] like '*" & Me![txtAddress]
& "*'" & " OR " & "[region] like '*" & Me![txtAddress]
& "*'"

rem Respite
stLinkCriteria = "[respite]=" & Me![chkRespiteService]

Each of the 4 are run seperately when an independant
button is clicked. They sort out the criteria for what
records get shown when a form is opened. I would like to
have all 4 sections run in the way:

stLinkCriteria= section1 AND section2 AND section3 AND
section4

so that the criteria can be set and one button activates
it .... i cannot seem to get this right.

I hope this makes sense to someone. I'd appreciate any
direction.

Mark.
 
Mark Hepburn said:
I have the following lines of code (each is a seperate
section):

rem Client Group
stLinkCriteria = "[client group] like '*" & Me![txtSearch]
& "*'"
Click to expand...
---- said:
Each of the 4 are run seperately when an independant
button is clicked. They sort out the criteria for what
records get shown when a form is opened. I would like to
have all 4 sections run in the way:

stLinkCriteria= section1 AND section2 AND section3 AND
section4

so that the criteria can be set and one button activates
it .... i cannot seem to get this right.
Click to expand...

Mark,

here's some code for the one button's OnClick event:

Dim strClientGrp As String
Dim strName As String
Dim strAddress As String
Dim strRespite As String
Dim strLinkCriteria As String

strClientGrp = "[client group] like '*" & Me![txtSearch] & "*'"

strName = "[owner] like '*" & Me![txtOrgSearch] & "*'" _
& " OR " & "[name] like '*" & Me![txtOrgSearch] & "*'"

strAddress = "[address1] like '*" & Me![txtAddress] & "*'" _
& " OR " & "[address2] like '*" & Me![txtAddress] & "*'" _
& " OR " & "[town] like '*" & Me![txtAddress] & "*'" _
& " OR " & "[region] like '*" & Me![txtAddress] & "*'"

strRespite = "[respite]=" & Me![chkRespiteService]

strLinkCriteria = strClientGrp _
& " AND (" & strName & ") AND (" & strAddress _
& ") AND " & strRespite

However, if you need to check the criterias for Null (i.e. some of the
criterias can be left out), there is more to it. Post back for further
help.

BTW, this search will be very very slow due to the lots of wildcards
at the beginning of the criterias. Jet cannot use in such cases the
indexes. Maybe you'll consider making some improvements, like combos
to choose the town and the region, maybe also for the client group.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top