I have a rather complicated procedure which includes a SQL statement
in which I need to add a further criterion which is the field
P.Cancelled (a Yes/No field) to select those with 'yes', those with
'no' or 'all policies'. The relevant criterion is defined in the
textbox 'txtWhichPolicies' and will be '0', '-1', or '*'. I have
tried lots of different variations of cade adding it in where I have
placed ???????????????? in the example below all seem either bring
back all the records or just freeze the whole thing. Is it because it
is a UNION query that I am having the problem?

Private Sub cmdOK_Click()
Dim dbs As Database
Dim loForm As
Form '**
1.0.16
Dim lrs As DAO.Recordset
Dim lsCriteria As String
Dim lsSQL As String
Dim stDocName As String
Dim stPolicyStatus As String

stPolicyStatus = txtWhichPolicies

On Error GoTo Err_cmdOK_Click

'* Attempt to find the Policy
Set dbs = CurrentDb
lsSQL = "SELECT DISTINCT P.policy_no " _
& "FROM dbo_Policy P, " _
& "dbo_Organisation O " _
& "WHERE P.insured_id = O.organisation_id " _
& "AND P.version_no IN (SELECT MAX(version_no) " _
& "FROM dbo_Policy P " _
& "WHERE P.policy_no = policy_no) " _
& "AND (O.organisation_alias LIKE '*" & txtName & "*'" _
& "OR " _
& "O.organisation_name LIKE '*" & txtName & "*') "
lsSQL = lsSQL & "UNION " _
& "SELECT DISTINCT P.policy_no " _
& "FROM dbo_Policy P, " _
& "dbo_CoverSchedule CS, " _
& "dbo_CoverScheduleSiteGroup CSSG, " _
& "dbo_Site S " _
& "WHERE P.policy_no = CS.policy_no " _
& "AND P.version_no = CS.version_no " _
& "AND P.version_no IN (SELECT MAX(version_no) " _
& "FROM dbo_Policy P " _
& "WHERE P.policy_no = policy_no) " _
& "AND CS.policy_no = CSSG.policy_no " _
& "AND CS.version_no = CSSG.version_no " _
& "AND CS.cover_schedule_sequence =
CSSG.cover_schedule_sequence " _
& "AND CSSG.site_id = S.site_id " _
& "AND S.site_name LIKE '*" & txtName & "*' "
????????????????

Set lrs = dbs.OpenRecordset(lsSQL)
lsCriteria = "policy_no IN ("
If Not lrs.EOF Then
'* Build up filter string
While Not lrs.EOF
lsCriteria = lsCriteria & """" & lrs!policy_no & """"
lrs.MoveNext
If lrs.EOF Then
lsCriteria = lsCriteria & ")"
Else
lsCriteria = lsCriteria & ","
End If
Wend
'* Warn the user they have more than one hit
If lrs.RecordCount > 1 Then
If MsgBox("There are " & lrs.RecordCount & " Policies with
the name you specified." _
& vbNewLine & vbNewLine _
& "Do you want to browse them all?", _
vbYesNo + vbQuestion + vbDefaultButton1, _
"NRI Policies") = vbNo Then
txtName.SetFocus
lrs.Close
Set lrs = Nothing
Set dbs = Nothing
Exit Sub
End If
End If
stDocName = "frmPolicy"
'* Look for loaded form to remove Data Entry mode
For Each loForm In
Forms '** 1.0.16
If loForm.name = stDocName
Then '** 1.0.16
loForm.DataEntry =
False '** 1.0.16
If loForm.RecordSource <> "qryCurrentPolicies"
Then '** 1.0.16
loForm.RecordSource =
"qryCurrentPolicies" '** 1.0.16
End
If '** 1.0.16
End
If '**
1.0.16

Next
'** 1.0.16
Call DoCmd.Close(acForm, Me.name)
Call DoCmd.OpenForm(stDocName, , ,
lsCriteria) '** 1.0.16
Forms!frmPolicy.AllowDeletions = False
Forms!frmPolicy.AllowAdditions = False
If lrs.RecordCount > 1 Then
Forms!frmPolicy.NavigationButtons = True
Else
Forms!frmPolicy.NavigationButtons = False
End If
Forms!frmPolicy.cmdClose.Visible = True
Forms!frmPolicy!
txtPolicyNo.SetFocus '** 1.0.15
Else
Call MsgBox("No Policies Found", vbOKOnly + vbExclamation,
"NRI Policies")
txtName.SetFocus
End If

On Fri, 3 Dec 2010 09:18:08 -0800 (PST), FireyColin
<colin.mardell (AT) btopenworld (DOT) com> wrote:

FWIW I usually leave complex queries as queries. Then, in VBA I'll do
the SELECT * FROM queryname WHERE ...." Where the WHERE clause is
created in code.

That might help improve thngs for you.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

What other values might you have in your P.Cancelled field except 0, -1, or
* (by the way, a Yes/No field holds only integer values; it cannot hold a *
character as a value -- so is this field *actually* a Yes/No field, or are
you using it as such?)? Do you even need to filter on that field at all?

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"FireyColin" <colin.mardell (AT) btopenworld (DOT) com> wrote


<snipped>

Thanks Tony but it is the query I need to adjust and I'm a bit lost as
to how I go about it.

Colin