filter code

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
 
D

Danny J. Lesandrini

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

steve a

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
 
D

Douglas J. Steele

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

Danny J. Lesandrini

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
 
D

Douglas J. Steele

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top