Changing queries using labels.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 labels that are supposed to change the query in a subform. I would
like lblAllDates to show all record (no filter on), and then lblFilterDates
to show all records after the current date. Can someone let me know what I
am doing
wrong? I think it has something to do with --"VacationDate > " & Date--.

---------------------------------------
Private Sub lblAllDates_Click()

Me.sfrmVacation.Form.Filter = vbNullString
Me.sfrmVacation.Form.FilterOn = False
Me.lblAllDates.Visible = False
Me.lblFilterDates.Visible = True

End Sub

Private Sub lblFilterDates_Click()

Me.sfrmVacation.Form.FilterOn = True
Me.sfrmVacation.Form.Filter = "VacationDate > " & Date
Me.lblAllDates.Visible = True
Me.lblFilterDates.Visible = False
End Sub
--------------------------------------
 
Dates need to be delimited with # characters, and should be in mm/dd/yyyy
format, regardless of what your regional settings may be. (Okay, this last
part isn't strictly true: the date can be in any unambiguous format, such as
dd mmm yyyy or yyyy-mm-dd. The point is, if your regional settings are
dd/mm/yyyy, it won't work for the first 12 days of any month...)

Try:

Me.sfrmVacation.Form.Filter = "VacationDate > " & Format$(Date,
"\#mm\/dd\/yyyy\#")
 
James said:
I have 2 labels that are supposed to change the query in a subform. I would
like lblAllDates to show all record (no filter on), and then lblFilterDates
to show all records after the current date. Can someone let me know what I
am doing
wrong? I think it has something to do with --"VacationDate > " & Date--.

---------------------------------------
Private Sub lblAllDates_Click()

Me.sfrmVacation.Form.Filter = vbNullString
Me.sfrmVacation.Form.FilterOn = False
Me.lblAllDates.Visible = False
Me.lblFilterDates.Visible = True

End Sub

Private Sub lblFilterDates_Click()

Me.sfrmVacation.Form.FilterOn = True
Me.sfrmVacation.Form.Filter = "VacationDate > " & Date
Me.lblAllDates.Visible = True
Me.lblFilterDates.Visible = False
End Sub
--------------------------------------
Click to expand...


You're right, the problem is with the filter string. When
you concatenate a date as you have, the system setting for
short date format is used. Typically, that will result in
the filter looking like:
VacationDate > 10/22/05

While that may look like a date to you, according to the
rules of expression evaluation, it's 10 divided by 22
divided by 5, so you end up comparing VacationDate to a
pretty small number. The syntax for a date literal requires
# signs around the date (much like a text string must have
quotes around it).

A systems settings independent way of doing what you want
is:
"VacationDate > " & Format(Date, "\#m\/d\/yyyy\#")
Note that the / characters are escaped by \ to prevent the
system settings date separator from replacing the /.

OTOH, there are many circumstances where you can use
expressions, so the Date function can be evaluated when the
filter is actually used. This means that, in this case, you
do not have to concatenate the date to create the filter
string. Instead you can use:
"VacationDate > Date()"

Note that the parenthesis are required with the Date
function because VB code is the only environment where you
can omit them on a function call with no arguments.
 
Thanks to both of you for the help. It works!

Marshall Barton said:
You're right, the problem is with the filter string. When
you concatenate a date as you have, the system setting for
short date format is used. Typically, that will result in
the filter looking like:
VacationDate > 10/22/05

While that may look like a date to you, according to the
rules of expression evaluation, it's 10 divided by 22
divided by 5, so you end up comparing VacationDate to a
pretty small number. The syntax for a date literal requires
# signs around the date (much like a text string must have
quotes around it).

A systems settings independent way of doing what you want
is:
"VacationDate > " & Format(Date, "\#m\/d\/yyyy\#")
Note that the / characters are escaped by \ to prevent the
system settings date separator from replacing the /.

OTOH, there are many circumstances where you can use
expressions, so the Date function can be evaluated when the
filter is actually used. This means that, in this case, you
do not have to concatenate the date to create the filter
string. Instead you can use:
"VacationDate > Date()"

Note that the parenthesis are required with the Date
function because VB code is the only environment where you
can omit them on a function call with no arguments.
Click to expand...
 
Back
Top