replace clause in filter

Posted on 2012-09-14
Last Modified: 2012-11-30
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
Question by:Shawn
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    intPos = instr(strWhere, "Recent")

    if intPos > 0 then strWhere = Left(strWhere, intPos - 1)
    strWhere = strWhere & "RecentActivity >= #" & date & "#-" & strDays
    LVL 84

    Accepted Solution

    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.
    LVL 44

    Expert Comment

    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.
    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now