Filtering a form's output using SQL

[Guest]

Hey all,

I am trying to use a SQL statement to filter the results that are displayed
in a form (edit mode) by using the WHERE statement. I am able to filter the
results (clicking on a button) between a one-to-many relationship. What I am
trying to accomplish is to allow the user to filter the results by customers
(a one-to-many relationships [the many side is Issues]) and also the if the
ticket is Open or Closed. The ticket status field is a combo box that is a
lookup wizard related to another table. Here is my code:

Private Sub cmdOpenCall_Click()
On Error GoTo Err_cmdOpenCall_Click


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEditCallLog"

stLinkCriteria = "[CustID]=" & Me![CustID] & " AND" & "[OpenClose]=" &
Me![OpenClose] = [Open]

DoCmd.FindRecord stDocName, , , stLinkCriteria

Exit_cmdOpenCall_Click:
Exit Sub

Err_cmdOpenCall_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCall_Click

End Sub


The problem occurs in defining the stLinkCriteria variable. The code works
fine before including the second part of the WHERE statement (after the AND
statement).

Any help would be much appreciated,

Thanks in advance,

J. B.

 

[Michael Keating]

Hey all,

I am trying to use a SQL statement to filter the results that are displayed
in a form (edit mode) by using the WHERE statement. I am able to filter the
results (clicking on a button) between a one-to-many relationship. What I am
trying to accomplish is to allow the user to filter the results by customers
(a one-to-many relationships [the many side is Issues]) and also the if the
ticket is Open or Closed. The ticket status field is a combo box that is a
lookup wizard related to another table. Here is my code:

Private Sub cmdOpenCall_Click()
On Error GoTo Err_cmdOpenCall_Click


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEditCallLog"

stLinkCriteria = "[CustID]=" & Me![CustID] & " AND" & "[OpenClose]=" &
Me![OpenClose] = [Open]

DoCmd.FindRecord stDocName, , , stLinkCriteria

Exit_cmdOpenCall_Click:
Exit Sub

Err_cmdOpenCall_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCall_Click

End Sub


The problem occurs in defining the stLinkCriteria variable. The code works
fine before including the second part of the WHERE statement (after the AND
statement).

Any help would be much appreciated,

Thanks in advance,

J. B.

Hi,

Firstly, I think I'd put a space between the "AND" and the "{OpenClose]

but I think it's more important to establish what the [Open] is doing ...

If this is the text in the combo, then it needs to be 'Open' rather than
[Open], otherwise Access will be looking for a field called Open. However,
you just want the filter to work on the value of the combo

so ...

stLinkCriteria = "[CustID]=" & Me![CustID] & " AND [OpenClose] =" &
Me![OpenClose]

HTH

MFK.