• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

replace clause in filter

I have a button which adds a filter based on the more recent translactions. The user fills in the number of days and only the records since then show. The problem is if the user changes his mind and enters another number of days the filter is added to the previous one rather than replacing it.

What would be the best way to replace the Where clause below?

strWhere = strWhere & "RecentActivity >= #" & date & "#-" & strDays
0
Shawn
Asked:
Shawn
1 Solution
 
Dale FyeCommented:
intPos = instr(strWhere, "Recent")

if intPos > 0 then strWhere = Left(strWhere, intPos - 1)
strWhere = strWhere & "RecentActivity >= #" & date & "#-" & strDays
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you actually building the strWhere value? In most cases, you would simply wipe out that value, and then recreate it from scratch, building on the choices made by the user (assuming this is some form of Filter code snippet, of course).

fyed's suggestion will work, of course, but I'm curious as to why this is even needed.
0
 
aikimarkCommented:
Can you use the form/report Filter property?  This would retain the original SQL (as record source) and perform the post-open filtering for your user.
0
 
ShawnAuthor Commented:
Hi LSMConsulting,

I'm building the filter based on about 10 option groups and 2 text boxes.
Agreed. I would probably be better as you sugggested to wipe out the value and recreate. I'll do a little testing and come back.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now