Issues with code

Bdavis · Dec 29, 2005

Well I thought I'd try this myself and I failed miserably. Here's what I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form, there
are two fields that I'm using to create a date range filter. Here's the code
I'm attempting to use to put the filter in place. Right now, the error I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

guido · Dec 29, 2005

Is "date" the name of your field? If so, Access will give you a lot of
problems because it is a reserved word in the code. "Date" returns the
current date in VBA.

John Griffiths · Dec 29, 2005

strWhere = strWhere & "([date] BETWEEN #" & Format(Me.txtBeginDate,
conDateFormat) & "# AND #" & Format(Me.txtEndDate,
conDateFormat) & "#)"

A "Debug.Pint strWhere" would have given you an idea why it wasn't working.

Regards - John

BruceM · Dec 29, 2005

Debug.Print, that is

John Griffiths said:
strWhere = strWhere & "([date] BETWEEN #" & Format(Me.txtBeginDate,
conDateFormat) & "# AND #" & Format(Me.txtEndDate,
conDateFormat) & "#)"

A "Debug.Pint strWhere" would have given you an idea why it wasn't
working.

Regards - John

Bdavis said:

Well I thought I'd try this myself and I failed miserably. Here's what I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form, there
are two fields that I'm using to create a date range filter. Here's the code
I'm attempting to use to put the filter in place. Right now, the error I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

John Griffiths · Dec 29, 2005

No definatly a pint - Hic - John
Wine + 2mins in microwave to heat up

Bdavis · Dec 29, 2005

Thanks John,

But I'm still recieving the exact same error message. No luck. ANy ideas?

John Griffiths said:
strWhere = strWhere & "([date] BETWEEN #" & Format(Me.txtBeginDate,
conDateFormat) & "# AND #" & Format(Me.txtEndDate,
conDateFormat) & "#)"

A "Debug.Pint strWhere" would have given you an idea why it wasn't working.

Regards - John

Bdavis said:

Well I thought I'd try this myself and I failed miserably. Here's what I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form, there
are two fields that I'm using to create a date range filter. Here's the code
I'm attempting to use to put the filter in place. Right now, the error I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

John Griffiths · Dec 29, 2005

Hi

You are using "strWhere" on both sides of "=", I assume you have some code
not shown.

If there is no other assignment to "strWhere" then you can change to:-
strWhere = "([date] BETWEEN #" & Format(Me.txtBeginDate,
conDateFormat) & "# AND #" & Format(Me.txtEndDate,
conDateFormat) & "#)"

Check to see exactly is being passed by "strWhere"
using
Debug.Print strWhere

and post the text and full error message - John

Bdavis said:
Thanks John,

But I'm still recieving the exact same error message. No luck. ANy ideas?

John Griffiths said:

strWhere = strWhere & "([date] BETWEEN #" & Format(Me.txtBeginDate,
conDateFormat) & "# AND #" & Format(Me.txtEndDate,
conDateFormat) & "#)"

A "Debug.Pint strWhere" would have given you an idea why it wasn't working.

Regards - John

Bdavis said:

Well I thought I'd try this myself and I failed miserably. Here's

Click to expand...

what
I'm

trying to do:

I have a subform (affectionetly called Child17). On the main form, there
are two fields that I'm using to create a date range filter. Here's

Click to expand...

the
code

I'm attempting to use to put the filter in place. Right now, the

Click to expand...

error
I'm

getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

Douglas J. Steele · Dec 29, 2005

In addition to putting the square brackets around date, remove the =#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

Bdavis · Dec 30, 2005

Thanks Doug,

I no longer get the error, however, the filter is not returning any data.
I'm can't figure out why to save my life... any ideas?

Douglas J. Steele said:
In addition to putting the square brackets around date, remove the =#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Well I thought I'd try this myself and I failed miserably. Here's what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form, there
are two fields that I'm using to create a date range filter. Here's the
code
I'm attempting to use to put the filter in place. Right now, the error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Douglas J. Steele · Dec 30, 2005

What value is actually in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:
Thanks Doug,

I no longer get the error, however, the filter is not returning any data.
I'm can't figure out why to save my life... any ideas?

Douglas J. Steele said:

In addition to putting the square brackets around date, remove the =#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Well I thought I'd try this myself and I failed miserably. Here's what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form,
there
are two fields that I'm using to create a date range filter. Here's the
code
I'm attempting to use to put the filter in place. Right now, the error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

TC · Dec 30, 2005

Show the text of the filter.

TC

Bdavis · Dec 30, 2005

Doug,

Here is the code that populates the date range fields. It is "borrowed"
from Steven LeBans Calendar control. Your correction of my orginal code to
activate the filter is at the bottom:

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.txtBeginDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.txtBeginDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.txtBeginDate.Value = Null
Me.txtEndDate.Value = Null
End If

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

'Me.Child17.Form.FilterOn = False

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
'strWhere = strWhere & "(date=#" & Me!Combo37 & "#)"
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

End Sub

Douglas J. Steele said:
What value is actually in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Thanks Doug,

I no longer get the error, however, the filter is not returning any data.
I'm can't figure out why to save my life... any ideas?

Douglas J. Steele said:

In addition to putting the square brackets around date, remove the =#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well I thought I'd try this myself and I failed miserably. Here's what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form,
there
are two fields that I'm using to create a date range filter. Here's the
code
I'm attempting to use to put the filter in place. Right now, the error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

Douglas J. Steele · Dec 30, 2005

I repeat, what value is actually in strWhere?

Try changing to:

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
Debug.Print "strWhere = " & strWhere
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

then go to the Debug Window (Ctrl-G) after you've clicked the button to see
what Access comes up with.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:
Doug,

Here is the code that populates the date range fields. It is "borrowed"
from Steven LeBans Calendar control. Your correction of my orginal code
to
activate the filter is at the bottom:

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.txtBeginDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.txtBeginDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.txtBeginDate.Value = Null
Me.txtEndDate.Value = Null
End If

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

'Me.Child17.Form.FilterOn = False

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
'strWhere = strWhere & "(date=#" & Me!Combo37 & "#)"
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

End Sub

Douglas J. Steele said:

What value is actually in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Thanks Doug,

I no longer get the error, however, the filter is not returning any
data.
I'm can't figure out why to save my life... any ideas?

:

In addition to putting the square brackets around date, remove the =#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well I thought I'd try this myself and I failed miserably. Here's
what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form,
there
are two fields that I'm using to create a date range filter. Here's
the
code
I'm attempting to use to put the filter in place. Right now, the
error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

Bdavis · Jan 2, 2006

Hey Doug,

Sorry if I misunderstod your question.

I ran your code and in the immediate window, it says: strWhere = False

Douglas J. Steele said:
I repeat, what value is actually in strWhere?

Try changing to:

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
Debug.Print "strWhere = " & strWhere
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

then go to the Debug Window (Ctrl-G) after you've clicked the button to see
what Access comes up with.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Doug,

Here is the code that populates the date range fields. It is "borrowed"
from Steven LeBans Calendar control. Your correction of my orginal code
to
activate the filter is at the bottom:

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.txtBeginDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.txtBeginDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.txtBeginDate.Value = Null
Me.txtEndDate.Value = Null
End If

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

'Me.Child17.Form.FilterOn = False

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
'strWhere = strWhere & "(date=#" & Me!Combo37 & "#)"
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

End Sub

Douglas J. Steele said:

What value is actually in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,

I no longer get the error, however, the filter is not returning any
data.
I'm can't figure out why to save my life... any ideas?

:

In addition to putting the square brackets around date, remove the =#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well I thought I'd try this myself and I failed miserably. Here's
what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main form,
there
are two fields that I'm using to create a date range filter. Here's
the
code
I'm attempting to use to put the filter in place. Right now, the
error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

Douglas J. Steele · Jan 3, 2006

That's probably my fault. There's one two many = strWhere in that string!

It should be:

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:
Hey Doug,

Sorry if I misunderstod your question.

I ran your code and in the immediate window, it says: strWhere = False

Douglas J. Steele said:

I repeat, what value is actually in strWhere?

Try changing to:

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
Debug.Print "strWhere = " & strWhere
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

then go to the Debug Window (Ctrl-G) after you've clicked the button to
see
what Access comes up with.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Doug,

Here is the code that populates the date range fields. It is
"borrowed"
from Steven LeBans Calendar control. Your correction of my orginal
code
to
activate the filter is at the bottom:

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.txtBeginDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.txtBeginDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.txtBeginDate.Value = Null
Me.txtEndDate.Value = Null
End If

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

'Me.Child17.Form.FilterOn = False

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)
'strWhere = strWhere & "(date=#" & Me!Combo37 & "#)"
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

End Sub

:

What value is actually in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,

I no longer get the error, however, the filter is not returning any
data.
I'm can't figure out why to save my life... any ideas?

:

In addition to putting the square brackets around date, remove the
=#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well I thought I'd try this myself and I failed miserably.
Here's
what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main
form,
there
are two fields that I'm using to create a date range filter.
Here's
the
code
I'm attempting to use to put the filter in place. Right now, the
error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

Bdavis · Jan 3, 2006

Thanks again Doug... I think we're close.

The immidiete window is returning:
strWhere = ([date] between #12/27/2005# And #12/27/2005#

However, I'm getting nthe error message "Can't assign value to this object"

Bdavis said:
That's probably my fault. There's one two many = strWhere in that string!

It should be:

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bdavis said:

Hey Doug,

Sorry if I misunderstod your question.

I ran your code and in the immediate window, it says: strWhere = False

Douglas J. Steele said:

I repeat, what value is actually in strWhere?

Try changing to:

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " & Format(Me.txtEndDate,
conDateFormat)
Debug.Print "strWhere = " & strWhere
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

then go to the Debug Window (Ctrl-G) after you've clicked the button to
see
what Access comes up with.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Here is the code that populates the date range fields. It is
"borrowed"
from Steven LeBans Calendar control. Your correction of my orginal
code
to
activate the filter is at the bottom:

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.txtBeginDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.txtBeginDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.txtBeginDate.Value = Null
Me.txtEndDate.Value = Null
End If

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

'Me.Child17.Form.FilterOn = False

strWhere = strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)
'strWhere = strWhere & "(date=#" & Me!Combo37 & "#)"
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

End Sub

:

What value is actually in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,

I no longer get the error, however, the filter is not returning any
data.
I'm can't figure out why to save my life... any ideas?

:

In addition to putting the square brackets around date, remove the
=#.

strWhere = strWhere & "([date] between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well I thought I'd try this myself and I failed miserably.
Here's
what
I'm
trying to do:

I have a subform (affectionetly called Child17). On the main
form,
there
are two fields that I'm using to create a date range filter.
Here's
the
code
I'm attempting to use to put the filter in place. Right now, the
error
I'm
getting is "You can't assign a value to this object".

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = strWhere & "(date=#" & " between " &
Format(Me.txtBeginDate, conDateFormat) & " And " &
Format(Me.txtEndDate,
conDateFormat)

Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True

Click to expand...

Click to expand...

Bdavis · Jan 3, 2006

I think I got it. There was an extra "("

The final code is as follows... Thanks for all your help Doug!

Private Sub cmdDateRange_Click()
' Retrieve the currently selected date(s).
' Call our Function to display the Calendar
Dim blRet As Boolean
Dim DateStart As Date
Dim DateEnd As Date
' If the control is NULL then use Today's date.
DateStart = Nz(Me.txtBeginDate.Value, Date)
DateEnd = DateStart + 7

' Use named parameters for clarity
blRet = ShowMonthCalendar(clsMC:=mc, StartSelectedDate:=DateStart, _
EndSelectedDate:=DateEnd)

If blRet = True Then
Me.txtBeginDate.Value = DateStart
Me.txtEndDate.Value = DateEnd
Else
' Add any message here if you want to
' inform the user that no date was selected
Me.txtBeginDate.Value = Null
Me.txtEndDate.Value = Null
End If

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

'Me.Child17.Form.FilterOn = False

strWhere = strWhere & "[date] between " & Format(Me.txtBeginDate,
conDateFormat) & " And " & Format(Me.txtEndDate, conDateFormat)
Debug.Print "strWhere = " & strWhere
Me.Child17.Form.Filter = strWhere
Me.Child17.Form.FilterOn = True
End Sub

Issues with code

Bdavis

guido

John Griffiths

BruceM

John Griffiths

Bdavis

John Griffiths

Douglas J. Steele

Bdavis

Douglas J. Steele

TC

Bdavis

Douglas J. Steele

Bdavis

Douglas J. Steele

Bdavis

Bdavis