I am still having trouble with the complicated SQL statement that I
posted a few days ago. 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 code 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
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
If loForm.name = stDocName
Then
loForm.DataEntry =
False
If loForm.RecordSource <> "qryCurrentPolicies"
Then '** 1.0.16
loForm.RecordSource =
"qryCurrentPolicies"
End If
End If
Next

Call DoCmd.Close(acForm, Me.name)
Call DoCmd.OpenForm(stDocName, , , lsCriteria)
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
Else
Call MsgBox("No Policies Found", vbOKOnly + vbExclamation,
"NRI Policies")
txtName.SetFocus
End If

Show us the actual sql statement that results from running your code.
Add a "debug.print lsSQL" statement to your code and copy the statement
from the debug window.

It would also help to show us a few rows of sample data (in tabular
format) along with the desired results (also in tabular format)

My preference would be to get the sql statement working in a query
before attempting to run it via dynamic sql.


FireyColin wrote:

--
HTH,
Bob Barrows

FireyColin wrote:


Try using:

If Me.txtWhichPolicies = True Or Me.txtWhichPolicies =
False Then
lsSQL = lsSQL & " AND P.Cancelled=" &
Me.txtWhichPolicies
End If

You should also add a space before UNION

--
Marsh

On Dec 6, 5:18*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Thanks Marsh

That makes sense to me but where in the code should I put it? Sorry if
I seem a bit thick but, as you've probably worked out, I'm a bit of an
amateur at this.,

Colin

FireyColin wrote:


Put in place of the line with ????????????????

--
Marsh

On Dec 7, 4:18*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Thanks again Marsh

I tried that but whichever option I choose it always retrieve all the
records.

Colin

FireyColin wrote:


That implies txtWhichPolicies is neither True nor False. I
suspect that it's a Text value that might look like 0 or -1,
but is just text characters. I guess you could try changing
it to:
If Me.txtWhichPolicies = "-1" Then
lsSQL = lsSQL & " AND P.Cancelled = True"
ElseIf Me.txtWhichPolicies = "0" Then
lsSQL = lsSQL & " AND P.Cancelled = False"
End If

--
Marsh

On Dec 7, 6:12*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:

Thanks again Marsh
Still no joy i'm afraid, it just comes up with 'Too few parameters.
Expected 1'
Colin

FireyColin wrote:


That's what you get when there is a misspelled field name or
the SQL is garbled. Since the query runs when the text box
is neither "0" or "-1" the problem is in the part that's
added when it is "0" or "-1". Inspect that carefully. If
you can't find anything, add the line:
Debug.Print lsSQL
right before the OpenRecordset line and look at the
Immediate window to see the completed SQL statement. That
should allow you to spot what's wrong there and maybe you
can back track to what's wrong.

--
Marsh

On Dec 8, 3:12*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
I know I'm being a complete pain but there must be something I'm doing
completely wrong. I tried 'Debug.Print lsSQL'
as you suggested and nothing happened. I then went back to basics and
tried just to get the code to return only 'true' but that still
brought back all the records. The same again with false records.

Could have something to do with the fact that this is a UNION query?