Between and AND statement

[Tony Williams]

I have a list box which when I double click should filter records within a
date range entered into 2 unbound text boxes. My code is

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

strField = "[tbldocument].[txtExpirydate]"
strWhere = strField & " Between #" & (Me.txtstartdate) & "# AND #" &
(Me.txtenddate) & "#"

DoCmd.OpenForm "frmMdi", , , strWhere

End Sub

However it doesn't work. Can someone help me with what's missing?
Thanks
Tony

 

[Keith Wilby]

I have a list box which when I double click should filter records within a
date range entered into 2 unbound text boxes. My code is

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

strField = "[tbldocument].[txtExpirydate]"
strWhere = strField & " Between #" & (Me.txtstartdate) & "# AND #" &
(Me.txtenddate) & "#"

DoCmd.OpenForm "frmMdi", , , strWhere

End Sub

However it doesn't work. Can someone help me with what's missing?
Thanks
Tony

txtExpirydate suggests a text box, not a field, so something to check there.
Try formatting the date as mm/dd/yyyy. Also, assuming Expirydate is a date
field, try using a Variant type instead of a string.

Keith.
www.keithwilby.com

 

[SteveS]

Hi Tony,

I'm curious as to why you are using a list box double_click event to open a
form when the list box value is not being used in the where argument.


Anyway, I made a table and 2 forms. I added a button and a list box.

I put this code in the click event of the button:

'************************
Private Sub Command0_Click()
Dim strWhere As String

If IsNull(Me.txtstartdate) Or IsNull(Me.txtenddate) Then
DoCmd.OpenForm "frmMdi"
Else
strWhere = "[txtExpirydate] Between #" & (Me.txtstartdate) & "# AND #" &
(Me.txtenddate) & "#"

'uncomment for debugging
'Msgbox strWhere

DoCmd.OpenForm "frmMdi", , , strWhere
End If
End Sub
'********************************

Then I put this code in the list double click:

'******************
Private Sub List5_DblClick(Cancel As Integer)
Dim strWhere As String

If IsNull(Me.txtstartdate) Or IsNull(Me.txtenddate) Then
DoCmd.OpenForm "frmMdi"
Else
strWhere = "[txtExpirydate] Between #" & (Me.txtstartdate) & "# AND #" &
(Me.txtenddate) & "#"

'uncomment for debugging
'Msgbox strWhere

DoCmd.OpenForm "frmMdi", , , strWhere
End If
End Sub
'***********************


Both the button and the list box opened the form "frmMdi" with filtered records.

 

[Tony Williams]

Thanks to you both I'll try your suggestions and report back!
Tony

Hi Tony,

I'm curious as to why you are using a list box double_click event to open
a form when the list box value is not being used in the where argument.


Anyway, I made a table and 2 forms. I added a button and a list box.

I put this code in the click event of the button:

'************************
Private Sub Command0_Click()
Dim strWhere As String

If IsNull(Me.txtstartdate) Or IsNull(Me.txtenddate) Then
DoCmd.OpenForm "frmMdi"
Else
strWhere = "[txtExpirydate] Between #" & (Me.txtstartdate) & "# AND
#" & (Me.txtenddate) & "#"

'uncomment for debugging
'Msgbox strWhere

DoCmd.OpenForm "frmMdi", , , strWhere
End If
End Sub
'********************************

Then I put this code in the list double click:

'******************
Private Sub List5_DblClick(Cancel As Integer)
Dim strWhere As String

If IsNull(Me.txtstartdate) Or IsNull(Me.txtenddate) Then
DoCmd.OpenForm "frmMdi"
Else
strWhere = "[txtExpirydate] Between #" & (Me.txtstartdate) & "# AND
#" & (Me.txtenddate) & "#"

'uncomment for debugging
'Msgbox strWhere

DoCmd.OpenForm "frmMdi", , , strWhere
End If
End Sub
'***********************


Both the button and the list box opened the form "frmMdi" with filtered
records.


--
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

I have a list box which when I double click should filter records within
a date range entered into 2 unbound text boxes. My code is

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

strField = "[tbldocument].[txtExpirydate]"
strWhere = strField & " Between #" & (Me.txtstartdate) & "# AND #" &
(Me.txtenddate) & "#"

DoCmd.OpenForm "frmMdi", , , strWhere

End Sub

However it doesn't work. Can someone help me with what's missing?
Thanks
Tony

 

[Tony Williams]

Steve sorry for the misunderstanding. The two unbound boxes holding the date
values should filter the contents of the list box. On selecting a record in
the list box and double clicking on the selected record the frmMDi should
open at the selected record. However the I think I'm going about this the
wrong way in that the filter should be the row source of the list box see my
later post.
thanks
Tony

Hi Tony,

I'm curious as to why you are using a list box double_click event to open
a form when the list box value is not being used in the where argument.


Anyway, I made a table and 2 forms. I added a button and a list box.

I put this code in the click event of the button:

'************************
Private Sub Command0_Click()
Dim strWhere As String

If IsNull(Me.txtstartdate) Or IsNull(Me.txtenddate) Then
DoCmd.OpenForm "frmMdi"
Else
strWhere = "[txtExpirydate] Between #" & (Me.txtstartdate) & "# AND
#" & (Me.txtenddate) & "#"

'uncomment for debugging
'Msgbox strWhere

DoCmd.OpenForm "frmMdi", , , strWhere
End If
End Sub
'********************************

Then I put this code in the list double click:

'******************
Private Sub List5_DblClick(Cancel As Integer)
Dim strWhere As String

If IsNull(Me.txtstartdate) Or IsNull(Me.txtenddate) Then
DoCmd.OpenForm "frmMdi"
Else
strWhere = "[txtExpirydate] Between #" & (Me.txtstartdate) & "# AND
#" & (Me.txtenddate) & "#"

'uncomment for debugging
'Msgbox strWhere

DoCmd.OpenForm "frmMdi", , , strWhere
End If
End Sub
'***********************


Both the button and the list box opened the form "frmMdi" with filtered
records.


--
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

I have a list box which when I double click should filter records within
a date range entered into 2 unbound text boxes. My code is

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

strField = "[tbldocument].[txtExpirydate]"
strWhere = strField & " Between #" & (Me.txtstartdate) & "# AND #" &
(Me.txtenddate) & "#"

DoCmd.OpenForm "frmMdi", , , strWhere

End Sub

However it doesn't work. Can someone help me with what's missing?
Thanks
Tony