Results 1 to 8 of 8
  1. #1
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23

    Create Msg Box Based Off Different Values In Different Queries

    I have several different queries that hold information, some from the same table, some from a different tables. Every time I try to create a macro using MS Access' native macro feature, it always wants me to reference a form in order to create the macro.



    However, I don't want to compile these queries into a form. I would like an IF THEN statement, say, for instance, like this:

    If queryA.valueA >= queryB.valueB Then
    MsgBox (Blah Blah Blah)
    End If

    However, I will probably need this nested in VBA, to include other values from other tables, or queries, can anybody help me with referenceing values in queries for analysis/calculation (comparison) and producing a message box from the result?

    Can someone help? Thank you so much.

  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
    Simplest is probably DLookup(). You could also open a recordset on a query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23
    so create a query, and choose the columns i want and set dlookups in the criteria? would you happen to know the criteria for the dlookup? how to write it, they are confusing sometimes. btw, what do you mean, open a recordset on a query? interesting

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Have the macro run a VBA function. In the function, use DLookUps to get value A and another to get value B. Then compare the two and have a msgbox.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by gameemaster View Post
    so create a query, and choose the columns i want and set dlookups in the criteria? would you happen to know the criteria for the dlookup? how to write it, they are confusing sometimes. btw, what do you mean, open a recordset on a query? interesting
    No:

    If DLookup(...ValueA) >= DLookup(...valueB) Then
    MsgBox (Blah Blah Blah)
    End If

    Here's help on the syntax:

    http://www.theaccessweb.com/general/gen0018.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The solutions suggested can't really be much better than the information provided, which unfortunately, is not very much. Why are there multiple queries on the same table or sets of joined tables? Is it because you're returning the same fields but based on different parameters (such as dates)? Or are different fields being returned, and regardless of which it is, you want to message box the query results? Maybe we ought to be telling you to use a form and pass the parameter(s), thereby only having one query (or at least fewer than you currently have) against that table or set of tables. Or maybe you might benefit from a function that could build the message regardless of which query you use.

    It does seem strange that you want to present query results in message boxes though...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23
    I am building a database that tracks and notifies the user of extremes in market breadth criteria. Almost all that criteria is in one table and calculated by using expressions in calculated fields. Secondly, I am taking the simple moving averages of that data, in order for me to get the moving average query to work, I have to append the data over to the moving avg tables. I also calculate pivot points from the main table (Table A), which uses several sub-queries. I append the weekly, monthly and yearly pivot point data to their own tables, because the queries are quite complex. So I have four main tables: 1) Table A, holds everything 2) Table B, holds the moving average data and 3) table C, which holds weekly pivot points and table D which holds monthly pivot points. I'd like to create a function that gives me a message box, when certain metrics in table a are larger or smaller than they are in table b, but based off where the current value for that metric is in table C or D, make sense?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    when certain metrics in table a are larger or smaller than they are in table b, but based off where the current value for that metric is in table C or D, make sense?
    Not to me. The red part is, I think, just criteria like WHERE A <> B. In that case, I don't see what bearing C or D has on it. Regardless, if you can make a query that returns records that satisfy the requirement, anybody here should be able to help with the desired message box. My thinking is to run this query via code, and if it returns any records to a recordset, grab whatever field values you want and put them in the message. But I'm basically repeating what was provided in post #2.

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

Similar Threads

  1. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  2. Replies: 2
    Last Post: 11-26-2013, 03:29 PM
  3. Replies: 6
    Last Post: 11-13-2013, 04:17 PM
  4. Replies: 2
    Last Post: 11-19-2012, 05:42 PM
  5. Replies: 1
    Last Post: 03-27-2010, 06:13 AM

Tags for this Thread

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