Between and AND statement

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

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
 
Tony Williams said:
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
Click to expand...

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
 
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.
 
Thanks to you both I'll try your suggestions and report back!
Tony
SteveS said:
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.)

Tony said:
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
Click to expand...
Click to expand...
 
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


SteveS said:
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.)

Tony said:
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
Click to expand...
Click to expand...
 
Back
Top