List box question Part 2!

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
 
K

Klatuu

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...
 
T

Tony Williams

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...
 
T

Tony Williams

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...
 
K

Klatuu

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...
 
K

Klatuu

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...
 
T

Tony Williams

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...
 
K

Klatuu

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...
 
T

Tony Williams

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...
 
T

Tony Williams

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
Access Multi-select List Box w/ Date Range problem. 7
Help with Doubleclick code on list box 5
Please help w/form collecting parameters for Report 1
Filter Report by date from Form 2
Code not working 5
Running 2 reports in a form together 3

Top