filter code

  • Thread starter Thread starter steve a
  • Start date Start date
S

steve a

Hi ,
I'm trying to filert a form via a command button and details in a combo box.

the code on the click event on the button is:

If Me.cbodatefilter > 0 Then
Me.Filter = "[txtdatestart] = " & Me.[cbodatefilter]
Me.FilterOn = True
Me.TabCtl18 = (0)
End If


The problem is that i keep on getting a box asking for txtdatestart!
any chance for some help? the values i'm using are dates, and the allow
filter is set to yes.
mnay thanks
steve
 
Try this ...
Me.Filter = "[DateStart] = #" & Me.[cbodatefilter] & "#"

The filter must reference an actual recordset field, not a form control
Dates must be delimited by pound signs.
 
that's great it works!! i just couldn't get the code right. was nearly
there though.
Many thanks

--
steve adlam


Danny J. Lesandrini said:
Try this ...
Me.Filter = "[DateStart] = #" & Me.[cbodatefilter] & "#"

The filter must reference an actual recordset field, not a form control
Dates must be delimited by pound signs.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


steve a said:
Hi ,
I'm trying to filert a form via a command button and details in a combo box.

the code on the click event on the button is:

If Me.cbodatefilter > 0 Then
Me.Filter = "[txtdatestart] = " & Me.[cbodatefilter]
Me.FilterOn = True
Me.TabCtl18 = (0)
End If


The problem is that i keep on getting a box asking for txtdatestart!
any chance for some help? the values i'm using are dates, and the allow
filter is set to yes.
mnay thanks
steve
Click to expand...
Click to expand...
 
<picky>

Not everyone's Short Date format is set to a format Access is guaranteed to
recognize. Users who have their Short Date format set to dd/mm/yyyy will
experience problems with that code for the first 12 days of any month.

Far safer is

Me.Filter = "[DateStart] = " & _
Format(Me.[cbodatefilter], "\#yyyy\-mm\-dd\#")

</picky>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Danny J. Lesandrini said:
Try this ...
Me.Filter = "[DateStart] = #" & Me.[cbodatefilter] & "#"

The filter must reference an actual recordset field, not a form control
Dates must be delimited by pound signs.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


steve a said:
Hi ,
I'm trying to filert a form via a command button and details in a combo
box.

the code on the click event on the button is:

If Me.cbodatefilter > 0 Then
Me.Filter = "[txtdatestart] = " & Me.[cbodatefilter]
Me.FilterOn = True
Me.TabCtl18 = (0)
End If


The problem is that i keep on getting a box asking for txtdatestart!
any chance for some help? the values i'm using are dates, and the allow
filter is set to yes.
mnay thanks
steve
Click to expand...
Click to expand...
 
Doug, are you sure?

It's true that ...
CStr(Format(Date(), "mm-dd-yyyy") <> CStr(Format(Date(), "m-d-yyyy")

.... for every date, but when comparing the actual dates, formats are irrelevent.
CDate(Format(Date(), "mm-dd-yyyy") = CDate(Format(Date(), "m-d-yyyy")

So if the Filter is comparing a date field [DateStart] with a pound delimited
value like #2/16/09# it doesn't matter what format it's in. The evaluation
will be done on the date's "numeric" value, won't it?

CLng(#2/16/09#) = 39860

I've never worried about date formats in query expressions, unless the date
includes a TIME facet and I'm looking for equivilence.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Douglas J. Steele said:
<picky>

Not everyone's Short Date format is set to a format Access is guaranteed to recognize. Users who have their Short Date format
set to dd/mm/yyyy will experience problems with that code for the first 12 days of any month.

Far safer is

Me.Filter = "[DateStart] = " & _
Format(Me.[cbodatefilter], "\#yyyy\-mm\-dd\#")

</picky>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Danny J. Lesandrini said:
Try this ...
Me.Filter = "[DateStart] = #" & Me.[cbodatefilter] & "#"

The filter must reference an actual recordset field, not a form control
Dates must be delimited by pound signs.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


steve a said:
Hi ,
I'm trying to filert a form via a command button and details in a combo box.

the code on the click event on the button is:

If Me.cbodatefilter > 0 Then
Me.Filter = "[txtdatestart] = " & Me.[cbodatefilter]
Me.FilterOn = True
Me.TabCtl18 = (0)
End If


The problem is that i keep on getting a box asking for txtdatestart!
any chance for some help? the values i'm using are dates, and the allow
filter is set to yes.
mnay thanks
steve
Click to expand...
Click to expand...
Click to expand...
 
I think you may have missed my point, Danny.

If the user's ShortDate format is set to dd/mm/yyyy, and it's, say, March
4th, 2009, then

Me.Filter = "[DateStart] = #" & Me.[cbodatefilter] & "#"

is going to result in

Me.Filter = "[DateStart] = #04/03/2009#"

Even though the user's short date format is dd/mm/yyyy, that's ALWAYS going
to be interpretted as 03 April, 2009. It's not until the 13th of March that

Me.Filter = "[DateStart] = #13/03/2009#"

will be correctly interpretted.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Danny J. Lesandrini said:
Doug, are you sure?

It's true that ...
CStr(Format(Date(), "mm-dd-yyyy") <> CStr(Format(Date(), "m-d-yyyy")

... for every date, but when comparing the actual dates, formats are
irrelevent.
CDate(Format(Date(), "mm-dd-yyyy") = CDate(Format(Date(), "m-d-yyyy")

So if the Filter is comparing a date field [DateStart] with a pound
delimited
value like #2/16/09# it doesn't matter what format it's in. The
evaluation
will be done on the date's "numeric" value, won't it?

CLng(#2/16/09#) = 39860

I've never worried about date formats in query expressions, unless the
date
includes a TIME facet and I'm looking for equivilence.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Douglas J. Steele said:
<picky>

Not everyone's Short Date format is set to a format Access is guaranteed
to recognize. Users who have their Short Date format set to dd/mm/yyyy
will experience problems with that code for the first 12 days of any
month.

Far safer is

Me.Filter = "[DateStart] = " & _
Format(Me.[cbodatefilter], "\#yyyy\-mm\-dd\#")

</picky>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Danny J. Lesandrini said:
Try this ...
Me.Filter = "[DateStart] = #" & Me.[cbodatefilter] & "#"

The filter must reference an actual recordset field, not a form control
Dates must be delimited by pound signs.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi ,
I'm trying to filert a form via a command button and details in a combo
box.

the code on the click event on the button is:

If Me.cbodatefilter > 0 Then
Me.Filter = "[txtdatestart] = " & Me.[cbodatefilter]
Me.FilterOn = True
Me.TabCtl18 = (0)
End If


The problem is that i keep on getting a box asking for txtdatestart!
any chance for some help? the values i'm using are dates, and the
allow
filter is set to yes.
mnay thanks
steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top