Help with Doubleclick code on list box

[Tony Williams]

First off thanks to Ofer Cohen and Katuu for helping me get this far!

I have an unbound form which has an unbound text box called txtname and two
date fields, txtto and txtfrom. The txt field is to insert a string with a
wild card that filters on a field, txtlastname in a table, tblinstitution.
The date fields are used to filter the "from and to" dates of a field,
txtexpirydate in my table, tbldocument.

The list box holds the results of the filters and on selecting a particular
record and double clicking on it a form frmMDi should open at the
appropriate record.

Well, the first part works in that the text box filters the lastname but the
second part of the filter doesn't work ie the dates aren't filtered in the
list box. However if I select a record the form opens but this time the date
filter works and not the name filter.

I am at a loss!!!!

Here is my code so far

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

End Sub

I have commented out this section

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

Because on running debug I get Compile error expected expression

Is this the problem?
Can anyone help?
Thanks

 

[Keith Wilby]

I have commented out this section

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

Remove the "If" after the "And".

Keith.
www.keithwilby.com

 

[Tony Williams]

Hi Keith have done that now get a syntax error on debug and this is
highlighted
strWhere & " And "
Any ideas?
Thanks for your help
Tony

 

[Keith Wilby]

Hi Keith have done that now get a syntax error on debug and this is
highlighted
strWhere & " And "
Any ideas?
Thanks for your help

Hadn't spotted that but the extra "if" just leapt out. strWhere needs to be
assigned a string. Perhaps it should read

strWhere = strWhere & " And "

strWhere has to "equal" (=) something, currently it makes no sense.

Regards,
Keith.
www.keithwilby.com

 

[Tony Williams]

Tried that Keith and now get
Syntax Error(missing operator)in query
expression'[tbldocument].[txtExpirydate] Between #01/01/05# AND #31/12/05#
And'.

The dates are the ones I entered in the search form, although I entered them
as 01/01/2005 and 31/12/2005 ie with a 4 character year

Are we getting anywhere?
Thanks for sticking with me.
Cheers
Tony

 

[Keith Wilby]

Tried that Keith and now get
Syntax Error(missing operator)in query
expression'[tbldocument].[txtExpirydate] Between #01/01/05# AND #31/12/05#
And'.

The dates are the ones I entered in the search form, although I entered
them as 01/01/2005 and 31/12/2005 ie with a 4 character year

Are we getting anywhere?
Thanks for sticking with me.

It looks like you need to trim the trailing "And" so that it reads

[tbldocument].[txtExpirydate] Between #01/01/05# AND #31/12/05#

Perhaps take a look at why it's there in the first place, it may be surplus
to requirements.

Keith.