Results 1 to 10 of 10
  1. #1
    msnyder1112 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2013
    Posts
    6

    Angry access module for find and replace in a query! please help!

    Ahh i need help!
    Pretty much I have 5 or so query tables.
    What I need to do is go in under design view, find yesterdays date and update it with todays date.
    Heres what I have so far written under a module.




    Code:
    Sub findandreplace()
    Dim sFind As String
    Dim sReplace As String
    DoCmd.OpenQuery "Aborts Step 1", acViewDesign
    'this opens my query table in the design view
    sFind = InputBox("Find what?", "Find and Replace sequence")
    sReplace = InputBox("Replace with:", "Find and Replace sequence")
    'this is for the user to enter what to find and replace with
    End Sub
    This is obviously just for the first query table.
    I cant figure out how to find and replace it now.

    Any help would be greatly appreciate!
    Last edited by msnyder1112; 03-15-2013 at 03:03 PM. Reason: added code tags

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect you're doing this the hard way. Normally you'd use a parameter query that either prompted the user for the date:

    [Enter date]

    in the criteria, or more commonly among professionals using a form for the user to enter the date and having

    Forms!FormName.TextboxName

    in the criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    msnyder1112 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    I'm not that familiar with Access....
    I have about 20 actual queries but I only need to run the find and replace on about half of them.

    so i would do

    Code:
    DoCmd.OpenQuery "Aborts Step 1", acViewDesign
    *script to change sFind to sReplace
    DoCmd.OpenQuery "Aborts Step 2", acViewDesign
    *script to change sFind to sReplace


    but i dont know exactly what the code is.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What I'm saying is that changing the query itself is not normally necessary (and not normally done). You use a parameter query and provide the criteria at runtime. If you really wanted to, you'd use a QueryDef in code, but I strongly suggest you look at parameter queries first.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    msnyder1112 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    I understand what you're saying but I am just doing what I was told here at work. I'm not that familiar with Access to know exactly what your suggesting.
    My manager explained that he goes in each query and change the date in design view of each of the 10 queries.
    He requested me to write a macro to run that will just do all that automatically when he runs the module.

    Is it possible?

  6. #6
    msnyder1112 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    I guess a more basic explaination is- I just need the macro to find and replace the SQL text inside the query.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To my knowledge there is no such tool. He's doing things the hard way, so perhaps you can advance you both. By the way, if he's just changing the date to today's, you can use Date() in the criteria instead of #3/15/13# and it will always use the system date.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    msnyder1112 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    ok... thanks anyways...

  9. #9
    msnyder1112 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    im looking at the SQL and it looks like it looks for a file "call_history031513" so hes trying to change it each day to the correct date where "031513" is.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have tables for each day? That would normally be a mistake, unless we're talking about a massive number of records. Typically the data would be in a single table and you'd use criteria to extract a given day, plus you could query a range of dates, compare dates, etc.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 11-16-2012, 05:02 AM
  2. find and replace
    By rohini in forum Access
    Replies: 7
    Last Post: 05-17-2012, 05:23 AM
  3. Find and Replace
    By Bedsingar in forum Access
    Replies: 1
    Last Post: 08-14-2011, 01:10 PM
  4. Find and Replace query from a 2nd table
    By elightbox in forum Queries
    Replies: 1
    Last Post: 09-17-2010, 05:37 PM
  5. Find and Replace Query
    By randolphoralph in forum Queries
    Replies: 4
    Last Post: 03-17-2010, 07:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums