Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012

    Unanswered: help with is null and parameter queries

    Hey guys, i am new here, so would appreciate the help

    I have a query, with three different columns with three different parameters

    so i have

    Grade, Class ID, Test ID, each one has a self entered paramater on it

    eg 5, Teacher x, Spelling

    But i want the ability to use the is null criteria, so i can say i want grade 5, but then be able to NOT specify which teacher or what test

    and so on, eg.

    i want grade 5, with teacher x, but dont specify spelling

    So different levels of looking at the data??
    Attached Thumbnails Attached Thumbnails look here.png  

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Here's a solution:

    1. In an independent Module (i.e. NOT a Module associated with a Form or a Report and NOT a Class Module), create the following function:
    Public Function GetParameter(ByVal Prompt As String) As Variant
        Dim var As Variant
        var = InputBox(Prompt)
        If var = "" Then GetParameter = "*" Else GetParameter = var
    End Function
    2. Modify the criteria expression(s) in the query (feel free to change the string argument according to your needs):
    Like GetParameter("Please enter a value")
    So that the query that was:
    SELECT Tbl_Calendar.SysCounter, 
    FROM   Tbl_Calendar
    WHERE  Tbl_Calendar.SysCounter=[Please enter a value];
    SELECT Tbl_Calendar.SysCounter, 
    FROM   Tbl_Calendar
    WHERE  Tbl_Calendar.SysCounter Like GetParameter("Please enter a value");
    Note: Not the case here but generally, when you have a question concerning a query, please post the SQL statement of the query in plain text and, possibly information about the table (or tables) involved, rather than a screenshot . That way, its easier to reproduce the situation causing problem in order to help you.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts