List box question Part 2!

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a search form that uses an unbound list box there is a text box that
is used to filter three fields in three tables and I've tried to add a date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1], tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS [Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & " " &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr =
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] &
"*")) OR (((tblInstitution2.txtlastname) Like [Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records", vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form opens to a
new record. Am I going about this is the right way? How do I combine the two
strings to use them both to open the form?

Thanks as ever
Tony
 
For starters, there should be an And between your concatenation of strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of the
table field, you will need to have the correct delimiters around your values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

Tony Williams said:
I have a search form that uses an unbound list box there is a text box that
is used to filter three fields in three tables and I've tried to add a date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1], tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS [Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & " " &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr =
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] &
"*")) OR (((tblInstitution2.txtlastname) Like [Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records", vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form opens to a
new record. Am I going about this is the right way? How do I combine the two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
 
Thanks Klatuu. Could you help me with the delimiters I always find the use
of these confusing. In relation to my code could you explain where I would
put the delimitersin this part

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

and this

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

and is this statment correct?

StrWhere3 = #strWhere# & "stWhere2"

VBA is a mystery to me but I'm learning!!!!
Thanks
Tony




Klatuu said:
For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

Tony Williams said:
I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & " " &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr =
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form opens
to a
new record. Am I going about this is the right way? How do I combine the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
 
I'm getting a compile error with
If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If
Any ideas?
Tony
Klatuu said:
For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

Tony Williams said:
I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & " " &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr =
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form opens
to a
new record. Am I going about this is the right way? How do I combine the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
 
I will be happy to, but I will need to know the table field data types of all
the fields you are referencing

Tony Williams said:
Thanks Klatuu. Could you help me with the delimiters I always find the use
of these confusing. In relation to my code could you explain where I would
put the delimitersin this part

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

and this

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

and is this statment correct?

StrWhere3 = #strWhere# & "stWhere2"

VBA is a mystery to me but I'm learning!!!!
Thanks
Tony




Klatuu said:
For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

Tony Williams said:
I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & " " &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr =
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form opens
to a
new record. Am I going about this is the right way? How do I combine the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
Click to expand...
 
Doh!
Sorry Tony, I was cutting and pasting and did a really poor job of it.
I hate doing something so obviously stupid in a public place.
If Not IsNull(Me.txtstartdate) And Not IsNull(Me.txtenddate) Then
Click to expand...


Tony Williams said:
I'm getting a compile error with
If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If
Any ideas?
Tony
Klatuu said:
For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

Tony Williams said:
I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & " " &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr =
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form opens
to a
new record. Am I going about this is the right way? How do I combine the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
Click to expand...
 
txtstartdate and txtenddate and txtexpirydate are all date fields

TxtRefNbr is a number field

Is that sufficient info?

Thanks, really appreciate your help
Tony
Klatuu said:
I will be happy to, but I will need to know the table field data types of
all
the fields you are referencing

Tony Williams said:
Thanks Klatuu. Could you help me with the delimiters I always find the
use
of these confusing. In relation to my code could you explain where I
would
put the delimitersin this part

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

and this

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

and is this statment correct?

StrWhere3 = #strWhere# & "stWhere2"

VBA is a mystery to me but I'm learning!!!!
Thanks
Tony




Klatuu said:
For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

:

I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & "
" &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr
=
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like
[Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form
opens
to a
new record. Am I going about this is the right way? How do I combine
the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
Click to expand...
 
If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= #" & (Me.txtenddate) & "#"
End If
Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= #" & (Me.txtstartdate) & "#"
Else
strWhere = strField & " Between #" & (Me.txtstartdate) & "# AND #" &
Me.txtenddate) & "#"
End If

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3


Tony Williams said:
txtstartdate and txtenddate and txtexpirydate are all date fields

TxtRefNbr is a number field

Is that sufficient info?

Thanks, really appreciate your help
Tony
Klatuu said:
I will be happy to, but I will need to know the table field data types of
all
the fields you are referencing

Tony Williams said:
Thanks Klatuu. Could you help me with the delimiters I always find the
use
of these confusing. In relation to my code could you explain where I
would
put the delimitersin this part

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

and this

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

and is this statment correct?

StrWhere3 = #strWhere# & "stWhere2"

VBA is a mystery to me but I'm learning!!!!
Thanks
Tony




For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

:

I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & "
" &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr
=
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like
[Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form
opens
to a
new record. Am I going about this is the right way? How do I combine
the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
Click to expand...
 
You are allowed to make at least one mistake a day :-)
Tony

Klatuu said:
Doh!
Sorry Tony, I was cutting and pasting and did a really poor job of it.
I hate doing something so obviously stupid in a public place.
If Not IsNull(Me.txtstartdate) And Not IsNull(Me.txtenddate) Then
Click to expand...


Tony Williams said:
I'm getting a compile error with
If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If
Any ideas?
Tony
Klatuu said:
For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type of
the
table field, you will need to have the correct delimiters around your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

:

I have a search form that uses an unbound list box there is a text box
that
is used to filter three fields in three tables and I've tried to add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " " &
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname & "
" &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON tbldocument.txtRefNbr
=
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr = tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like
[Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the strWhere
doesn't work I get a prompt box asking me what it is and the form
opens
to a
new record. Am I going about this is the right way? How do I combine
the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
Click to expand...
 
I'm getting this line of code showing in red with a syntax error

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "

Any ideas?
Thanks again
Tony
Klatuu said:
If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= #" & (Me.txtenddate) & "#"
End If
Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= #" & (Me.txtstartdate) & "#"
Else
strWhere = strField & " Between #" & (Me.txtstartdate) & "# AND #"
&
Me.txtenddate) & "#"
End If

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3


Tony Williams said:
txtstartdate and txtenddate and txtexpirydate are all date fields

TxtRefNbr is a number field

Is that sufficient info?

Thanks, really appreciate your help
Tony
Klatuu said:
I will be happy to, but I will need to know the table field data types
of
all
the fields you are referencing

:

Thanks Klatuu. Could you help me with the delimiters I always find the
use
of these confusing. In relation to my code could you explain where I
would
put the delimitersin this part

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

and this

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

and is this statment correct?

StrWhere3 = #strWhere# & "stWhere2"

VBA is a mystery to me but I'm learning!!!!
Thanks
Tony




For starters, there should be an And between your concatenation of
strWhere
and strWhere2 provided both data fields are not Null:

If Not IsNull(Me.txtstartdate) And If Not IsNull(Me.txtenddate) Then
strWhere & " And "
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]

Then, you need to review you delimiters. Depending on the data type
of
the
table field, you will need to have the correct delimiters around
your
values:
Numeric = None
Text = Single or Doube Quotes ' or "
Date = #

:

I have a search form that uses an unbound list box there is a text
box
that
is used to filter three fields in three tables and I've tried to
add a
date
range function. Here is the SQL of the list box query

SELECT tblInstitution1.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtfirstname & " "
&
tblInstitution1.txtlastname AS [Contact 1],
tblInstitution2.txtInstitution2,
tblInstitution2.txtFirstname & " " & tblInstitution2.txtlastname AS
[Contact
2], tblInstitution3.txtInstitution3, tblInstitution3.txtFirstname &
"
" &
tblInstitution3.txtlastname AS [Contact 3]
FROM ((tbldocument INNER JOIN tblInstitution2 ON
tbldocument.txtRefNbr
=
tblInstitution2.txtRefNbr) INNER JOIN tblInstitution1 ON
tbldocument.txtRefNbr = tblInstitution1.txtRefNbr) INNER JOIN
tblInstitution3 ON tbldocument.txtRefNbr =
tblInstitution3.txtRefNbr
WHERE (((tblInstitution1.txtlastname) Like
[Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution2.txtlastname) Like
[Forms]![frmsearch3]![txtname]
& "*")) OR (((tblInstitution3.txtlastname) Like
[Forms]![frmsearch3]![txtname] & "*"))
ORDER BY tblInstitution1.txtInstitution;

The code for the Ondoubleclick of my list box is

Private Sub List3_DblClick(Cancel As Integer)
Dim strWhere As String
Dim strWhere2 As String
Dim StrWhere3 As String
Dim strField As String

strField = "[tbldocument].[txtExpirydate]"

If IsNull(Me.txtname) Then
MsgBox "You must enter some text in the box to filter the records",
vbOKOnly
End If

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strField & " <= " & (Me.txtenddate)
End If

Else
If IsNull(Me.txtenddate) Then
strWhere = strField & " >= " & (Me.txtstartdate)

Else
strWhere = strField & " Between " & (Me.txtstartdate) & " AND " &
(Me.txtenddate)
End If
End If

strWhere2 = "[tblInstitution1].[txtRefNbr]=" & Me.[List3]
StrWhere3 = "strWhere" & "stWhere2"
DoCmd.OpenForm "frmMdi", , , strWhere3
End Sub

The code filters the list box with the strWhere2 line but the
strWhere
doesn't work I get a prompt box asking me what it is and the form
opens
to a
new record. Am I going about this is the right way? How do I
combine
the
two
strings to use them both to open the form?

Thanks as ever
Tony
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select query populating list box 10
Populating a listbox 10
Help with Doubleclick code on list box 5
Access Multi-select List Box w/ Date Range problem. 7
Filter Specific Date in Access. 0
Please help w/form collecting parameters for Report 1
Code not working 5
Filter Report by date from Form 2

Back
Top