Results 1 to 11 of 11
  1. #1
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28

    Parameter Query

    I have a parameter query that repeats the request for parameter twice. Within the query I have 4 fields where parameters are needed:



    Vulnerabilituy Summary: Long Text with the LIKE criteria
    OS: Short Text with Like criteria
    Role: Short Text with the Like criteria
    Age: number with a >= criteria

    It uses two tables to draw the query that have " Include ALL records from the master Table to the minor Table

  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
    What's the question exactly? If you don't expect to get two prompts, it may help to post the SQL of the query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    What's the question exactly? If you don't expect to get two prompts, it may help to post the SQL of the query.
    here it is:

    SELECT Tbl_TVM.ip_address, Tbl_TVM.[vulnerability summary], [TBL_USMD Server List].OS, Tbl_TVM.OptumID, [TBL_USMD Server List].Hostname, [TBL_USMD Server List].Role, Tbl_TVM.age
    FROM [TBL_USMD Server List] RIGHT JOIN Tbl_TVM ON [TBL_USMD Server List].[IP Address] = Tbl_TVM.ip_address
    WHERE (((Tbl_TVM.[vulnerability summary]) Like "*" & [Enter Vulnerability here] & "*") AND (([TBL_USMD Server List].OS) Like "*" & [Enter OS here] & "*") AND (([TBL_USMD Server List].Role) Not Like "mo" Or ([TBL_USMD Server List].Role) Not Like "mike" Or ([TBL_USMD Server List].Role) Not Like "kyle" Or ([TBL_USMD Server List].Role) Not Like "optimix" Or ([TBL_USMD Server List].Role) Not Like "john" Or ([TBL_USMD Server List].Role) Not Like "phone team") AND ((Tbl_TVM.age)>=[Enter age desired]));

  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
    And your question exactly? I have some thoughts, but don't want to go in the wrong direction.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    And your question exactly? I have some thoughts, but don't want to go in the wrong direction.

    This query prompts me twice for the parameters that are set. For example, it will prompt me for the OS and when the parameter has been accepted, it will prompt me again. Why? and how can I fix it?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It shouldn't put up the same prompt twice. Is this just running the query, or in a form where perhaps it's being requeried? Can you attach the db here?

    Most of us would use a form to gather user input, not parameter prompts. You have a lot more control.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    It shouldn't put up the same prompt twice. Is this just running the query, or in a form where perhaps it's being requeried? Can you attach the db here?

    Most of us would use a form to gather user input, not parameter prompts. You have a lot more control.
    I just run the query. No form.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    Can you attach the db here?
    sorry. can't private/confidential database. I'll have to figure it out for myself, I guess. Thanks for trying

  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
    I understand. You shouldn't get prompted twice, but like I said most of us would use a form anyway, which would solve the issue.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What exactly is the prompt and is it exactly the same both times? The way I read your post it could be [TBL_USMD Server List].OS or [Enter OS here].
    You might have misspelled a table or field name and haven't even noticed the effect of that on the query results. In that case, you can get prompted for every reference to the misspelled word.

    Also, you're not using wildcards for several of the LIKE operations so there's not much point to it. I would use Not In('mike', 'kyle', 'john', 'etc.')
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2019, 08:28 PM
  2. Replies: 2
    Last Post: 01-26-2017, 08:19 AM
  3. Replies: 1
    Last Post: 07-21-2015, 03:38 PM
  4. Replies: 1
    Last Post: 02-28-2013, 01:20 PM
  5. Replies: 13
    Last Post: 01-10-2012, 09:56 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