Filter based on date range and a field value

[ckerns]

I want to filter a form based on a date range and the value of a
field. These return empty recordset. Any ideas?

Dim sStart As String
Dim sEnd As String
sStart = "1/1/" & Me.txtYear
sEnd = "12/31/" & Me.txtYear


DoCmd.ApplyFilter , "[DatePaid] between #" & Format(sStart,
"mm/dd/yyyy") & "# And #" & Format(sEnd, "mm/dd/yyyy") & "# And
Client=" & Me.Client


have also tried

DoCmd.ApplyFilter , "[DatePaid] between #" & Format(sStart,
"mm/dd/yyyy") & "# And #" & Format(sEnd, "mm/dd/yyyy") & "# And
Client='" & Me.Client & "'"

 

[John Vinson]

I want to filter a form based on a date range and the value of a
field. These return empty recordset. Any ideas?

Dim sStart As String
Dim sEnd As String
sStart = "1/1/" & Me.txtYear
sEnd = "12/31/" & Me.txtYear


DoCmd.ApplyFilter , "[DatePaid] between #" & Format(sStart,
"mm/dd/yyyy") & "# And #" & Format(sEnd, "mm/dd/yyyy") & "# And
Client=" & Me.Client


have also tried

DoCmd.ApplyFilter , "[DatePaid] between #" & Format(sStart,
"mm/dd/yyyy") & "# And #" & Format(sEnd, "mm/dd/yyyy") & "# And
Client='" & Me.Client & "'"

You're making two mistakes: treating a date as a text string - it
isn't - and using the Format() function to recast a constructed text
string into another text string.

It's a lot simpler than that!

DoCmd.ApplyFilter, "[DatePaid] > #" & DateSerial(Me.txtYear, 1, 1) _
& "# AND [DatePaid] < #" & DateSerial(Val(Me.txtYear)+1, 1, 1) & "#"

Note that this assumes txtYear contains a four-digit year - 99 would
get you a time in the Roman Empire if Access cates ran back that far.

John W. Vinson[MVP]

 

[ckerns]

Thanks! How do I add the Client criteria to the docmd so that it
additionally filters for the client field??

I want to filter a form based on a date range and the value of a
field. These return empty recordset. Any ideas?

Dim sStart As String
Dim sEnd As String
sStart = "1/1/" & Me.txtYear
sEnd = "12/31/" & Me.txtYear


DoCmd.ApplyFilter , "[DatePaid] between #" & Format(sStart,
"mm/dd/yyyy") & "# And #" & Format(sEnd, "mm/dd/yyyy") & "# And
Client=" & Me.Client


have also tried

DoCmd.ApplyFilter , "[DatePaid] between #" & Format(sStart,
"mm/dd/yyyy") & "# And #" & Format(sEnd, "mm/dd/yyyy") & "# And
Client='" & Me.Client & "'"

You're making two mistakes: treating a date as a text string - it
isn't - and using the Format() function to recast a constructed text
string into another text string.

It's a lot simpler than that!

DoCmd.ApplyFilter, "[DatePaid] > #" & DateSerial(Me.txtYear, 1, 1) _
& "# AND [DatePaid] < #" & DateSerial(Val(Me.txtYear)+1, 1, 1) & "#"

Note that this assumes txtYear contains a four-digit year - 99 would
get you a time in the Roman Empire if Access cates ran back that far.

John W. Vinson[MVP]

 

[John Vinson]

Thanks! How do I add the Client criteria to the docmd so that it
additionally filters for the client field??

Well, bear in mind that I cannot see your database, so I have no idea
what the name, bound control, or datatype of the client field might
be, but assuming that the ClientID is a numeric field, try

DoCmd.ApplyFilter, "[DatePaid] > #" & DateSerial(Me.txtYear, 1, 1) _
& "# AND [DatePaid] < #" & DateSerial(Val(Me.txtYear)+1, 1, 1) & "#" _
& " AND [ClientID] = " & Me!txtClientID

If ClientID is text you'll need quotemarks around it (use ' in the
same way that the date field uses #).

John W. Vinson[MVP]

 

[ckerns]

Sorry for lack of info. Client is a text data type.

Why use the ! in "Me!txtClientID" instead of a dot?

Thanks! How do I add the Client criteria to the docmd so that it
additionally filters for the client field??

Well, bear in mind that I cannot see your database, so I have no idea
what the name, bound control, or datatype of the client field might
be, but assuming that the ClientID is a numeric field, try

DoCmd.ApplyFilter, "[DatePaid] > #" & DateSerial(Me.txtYear, 1, 1) _
& "# AND [DatePaid] < #" & DateSerial(Val(Me.txtYear)+1, 1, 1) & "#" _
& " AND [ClientID] = " & Me!txtClientID

If ClientID is text you'll need quotemarks around it (use ' in the
same way that the date field uses #).

John W. Vinson[MVP]

 

[John Vinson]

Sorry for lack of info. Client is a text data type.

Ok, just use the quotes:

DoCmd.ApplyFilter, "[DatePaid] > #" & DateSerial(Me.txtYear, 1, 1) _
& "# AND [DatePaid] < #" & DateSerial(Val(Me.txtYear)+1, 1, 1) & "#" _
& " AND [ClientID] = '" & Me!txtClientID & "'"

Why use the ! in "Me!txtClientID" instead of a dot?

Longstanding habit on my part. They both work.

John W. Vinson[MVP]

 

[ckerns]

That is exactly what i did and it works perfectly! Thank you very
much! (I was just curious about the bang)

Sorry for lack of info. Client is a text data type.

Ok, just use the quotes:

DoCmd.ApplyFilter, "[DatePaid] > #" & DateSerial(Me.txtYear, 1, 1) _
& "# AND [DatePaid] < #" & DateSerial(Val(Me.txtYear)+1, 1, 1) & "#" _
& " AND [ClientID] = '" & Me!txtClientID & "'"

Why use the ! in "Me!txtClientID" instead of a dot?

Longstanding habit on my part. They both work.

John W. Vinson[MVP]