Filtering for a date in a report...

[KPatel]

I am trying to create a form that will be used to filter
a report. The filter is based on 8 fields, of which one
of them is a date field. I have declared all the fields
as strings:
Dim strid As String
Dim strstatus As String
Dim strlast As String
Dim strfirst As String
Dim strmake As String
Dim stryear As String
Dim strplate As String
Dim strdate As String
Dim strfilter As String
Dim strsortorder As String
When the form is populated with the filter criteria all
the fields work fine except the date field. For some
reason when I enter a date in the date field the filter
returns a blank report. The following is the code for my
strfilter (which takes in the value for the filter)

strfilter = "[C&R ID] " & strid & " AND [Vehicle
Status] " & strstatus & " AND [Last Name] " & strlast & "
AND [First Name] " & strfirst & " AND [Vehicle Make] " &
strmake & " and [Base Plate State] " & strplate & " and
[Reg Expiration] " & strexp

If any of the fields are blank they are assigned the
value "Like'*'" (including strdate)

Can someone please help me find a solution to this
problem.

 

[Allen Browne]

The Date/Time value needs # as the delimiter.

It looks like you must be tying the operator as well into these text boxes
(something I would not trust the user to remember), so try entering
something like:

= #1/1/2004#

in that text box.

The Like operator won't work well for dates (or numbers really).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a form that will be used to filter
a report. The filter is based on 8 fields, of which one
of them is a date field. I have declared all the fields
as strings:
Dim strid As String
Dim strstatus As String
Dim strlast As String
Dim strfirst As String
Dim strmake As String
Dim stryear As String
Dim strplate As String
Dim strdate As String
Dim strfilter As String
Dim strsortorder As String
When the form is populated with the filter criteria all
the fields work fine except the date field. For some
reason when I enter a date in the date field the filter
returns a blank report. The following is the code for my
strfilter (which takes in the value for the filter)

strfilter = "[C&R ID] " & strid & " AND [Vehicle
Status] " & strstatus & " AND [Last Name] " & strlast & "
AND [First Name] " & strfirst & " AND [Vehicle Make] " &
strmake & " and [Base Plate State] " & strplate & " and
[Reg Expiration] " & strexp

If any of the fields are blank they are assigned the
value "Like'*'" (including strdate)

Can someone please help me find a solution to this
problem.

 

[KPatel]

Thanks for your prompt response.

This is the code that I have so far

If IsNull(Me.Exp.Value) Then
strdate = "Like '*'"
Else
strdate = "=" & Me.Exp.Value

Where would I insert the #. I tried the following

strdate = "=" & #Me.Exp.Value#

but I got a compile error

Any help would be appreciated.

Thanks

-----Original Message-----
The Date/Time value needs # as the delimiter.

It looks like you must be tying the operator as well into these text boxes
(something I would not trust the user to remember), so try entering
something like:

= #1/1/2004#

in that text box.

The Like operator won't work well for dates (or numbers really).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a form that will be used to filter
a report. The filter is based on 8 fields, of which one
of them is a date field. I have declared all the fields
as strings:
Dim strid As String
Dim strstatus As String
Dim strlast As String
Dim strfirst As String
Dim strmake As String
Dim stryear As String
Dim strplate As String
Dim strdate As String
Dim strfilter As String
Dim strsortorder As String
When the form is populated with the filter criteria all
the fields work fine except the date field. For some
reason when I enter a date in the date field the filter
returns a blank report. The following is the code for my
strfilter (which takes in the value for the filter)

strfilter = "[C&R ID] " & strid & " AND [Vehicle
Status] " & strstatus & " AND [Last Name] " & strlast & "
AND [First Name] " & strfirst & " AND [Vehicle Make] " &
strmake & " and [Base Plate State] " & strplate & " and
[Reg Expiration] " & strexp

If any of the fields are blank they are assigned the
value "Like'*'" (including strdate)

Can someone please help me find a solution to this
problem.

.

 

[Allen Browne]

You need to end up with a string that contains:
" AND ([NameOfYourFieldHere] = " & Format(Me.Exp, "\#mm\/dd\/yyyy\#") &
")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your prompt response.

This is the code that I have so far

If IsNull(Me.Exp.Value) Then
strdate = "Like '*'"
Else
strdate = "=" & Me.Exp.Value

Where would I insert the #. I tried the following

strdate = "=" & #Me.Exp.Value#

but I got a compile error

Any help would be appreciated.

Thanks

-----Original Message-----
The Date/Time value needs # as the delimiter.

It looks like you must be tying the operator as well into these text boxes
(something I would not trust the user to remember), so try entering
something like:

= #1/1/2004#

in that text box.

The Like operator won't work well for dates (or numbers really).

I am trying to create a form that will be used to filter
a report. The filter is based on 8 fields, of which one
of them is a date field. I have declared all the fields
as strings:
Dim strid As String
Dim strstatus As String
Dim strlast As String
Dim strfirst As String
Dim strmake As String
Dim stryear As String
Dim strplate As String
Dim strdate As String
Dim strfilter As String
Dim strsortorder As String
When the form is populated with the filter criteria all
the fields work fine except the date field. For some
reason when I enter a date in the date field the filter
returns a blank report. The following is the code for my
strfilter (which takes in the value for the filter)

strfilter = "[C&R ID] " & strid & " AND [Vehicle
Status] " & strstatus & " AND [Last Name] " & strlast & "
AND [First Name] " & strfirst & " AND [Vehicle Make] " &
strmake & " and [Base Plate State] " & strplate & " and
[Reg Expiration] " & strexp

If any of the fields are blank they are assigned the
value "Like'*'" (including strdate)

Can someone please help me find a solution to this
problem.