Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: Query That Allows Blank Fields From Form

    I am trying to write a query so that it would allow me to filter by a range of years and by Group. I also want it to allow a field to be blank and the query will ignore that criteria. I tried fiddling with the codes below but it is giving me the wrong results.


    This is how I would like it to work (it sends an error right now).
    Code:
    SELECT *
    FROM dbo_v_ScheduleWithInfo
    WHERE ((([Group_Name]=[Forms]![frmAuditPlanner]![cboGroups] Or [Forms]![frmAuditPlanner]![cboGroups] Is Null)=True) And (([Scheduled_Year] Between [Forms]![frmAuditPlanner]![txtYear] and [Forms]![frmAuditPlanner]![txtYear2]Or [Forms]![frmAuditPlanner]![txtYear] Is Null)=True)
    );
    This allows me to query but runs into problems when there is a blank field
    Code:
    SELECT dbo_v_ScheduleWithInfo.Department, dbo_v_ScheduleWithInfo.Group_Name, dbo_v_ScheduleWithInfo.[Supervisor/Point of Contact for the Audit & CPARs], dbo_v_ScheduleWithInfo.[Senior Manager Contact], dbo_v_ScheduleWithInfo.[Primary Audit Requirement], dbo_v_ScheduleWithInfo.[Support/Infrastructure Requirement], dbo_v_ScheduleWithInfo.[Relevant SOP & Some Procedures], dbo_v_ScheduleWithInfo.[Key Processes], dbo_v_ScheduleWithInfo.Q1, dbo_v_ScheduleWithInfo.Q2, dbo_v_ScheduleWithInfo.Q3, dbo_v_ScheduleWithInfo.Q4, dbo_v_ScheduleWithInfo.ScheduledYear
    FROM dbo_v_ScheduleWithInfo
    WHERE (((dbo_v_ScheduleWithInfo.ScheduledYear)=Forms!frmAuditPlanner!txtYear Or Forms!frmAuditPlanner!txtYear Is Null) And [Group_Name]=Forms!frmAuditPlanner!cboGroups Or Forms!frmAuditPlanner!cboGroups Is Null);

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    a field to be blank
    Do you mean Null?
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    63
    Yes, a null

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Do you mean allow users to omit one of the limitign factors or allow a null column when selecting the data, different problems


    if its allow users to omit that where element then you need to write our SQL on the fly. I don't think you could use an iif construct in the where clause (TBH never thought of using one so I don't know)

    if its allow null values then you need to use the or isnull(mycolumn) construct
    eg
    where ((mycolumn >= 20 and mycolumn<=50) or isnull(mycolumn))
    ... would mean retrieve any row where the value of mycolumn wasa between 20 and 50 or was null
    mind you you coudl also write that as
    where (mycolumn between 20 and 50 or isnull(mycolumn))
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the WHERE part of the second query:
    Code:
    ... WHERE (((dbo_v_ScheduleWithInfo.ScheduledYear)=Forms!frmAuditPlanner!txtYear Or Forms!frmAuditPlanner!txtYear Is Null) And [Group_Name]=Forms!frmAuditPlanner!cboGroups Or Forms!frmAuditPlanner!cboGroups Is Null);
    It's the column of the table (ScheduledYear) that you should test for Null, not the combo of the form.
    Have a nice day!

  6. #6
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by healdem View Post
    Do you mean allow users to omit one of the limitign factors or allow a null column when selecting the data, different problems


    if its allow users to omit that where element then you need to write our SQL on the fly. I don't think you could use an iif construct in the where clause (TBH never thought of using one so I don't know)
    I am trying to allow the users to omit one of the factors. I am basing my code off of another sample form Access Programmers UK. I tested his code and it works pretty good but doesn't seem to go well with my application. I can filter by Group just fine, but the ScheduledYear isn't working.


    Quote Originally Posted by Sinndho View Post
    In the WHERE part of the second query:
    It's the column of the table (ScheduledYear) that you should test for Null, not the combo of the form.
    I apologize for not clearly communicating. I want the query to omit the ScheduledYear if the Year Range textboxes are null. However, if the first Year textbox is not null and the second year textbox is null then omit the second Year textbox from the WHERE clause (example: 1995 - NULL will search for records dated from 1995 until whenever).
    Last edited by tkepongo; 08-04-11 at 13:26.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to write the SQL in the fly

    Code:
    strWhereClause = "Where 1=1"
    if not isnull(mycontrol1) then
      strWhereClause = strWhereClause & ", aNumericColumn = " & mynumericcontrol
    endif
    if not isnull(mycontrol2) then
      strWhereClause = strWhereClause & ", aStringColumn = '" & myStringcontrol & "'"
    endif
    if not isnull(mycontrol3) then
      strWhereClause = strWhereClause & ", aDateColumn = '" & format(mynumericcontrol,"#mm/dd/yyyy#") & "'"
    endif
    then your
    sql is
    strSQL = "Select my, comma, separated, column , list from mytable " & strWhereClause
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Let's suppose that the value of txtYear is Null. With your code, the condition:
    Code:
    dbo_v_ScheduleWithInfo.ScheduledYear=Forms!frmAuditPlanner!txtYear Or Forms!frmAuditPlanner!txtYear Is Null
    will be evaluated to:
    Code:
    bo_v_ScheduleWithInfo.ScheduledYear=Null Or Null Is Null
    which makes no sense.
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by healdem View Post
    so you need to write the SQL in the fly

    Code:
    strWhereClause = "Where 1=1"
    if not isnull(mycontrol1) then
      strWhereClause = strWhereClause & ", aNumericColumn = " & mynumericcontrol
    endif
    if not isnull(mycontrol2) then
      strWhereClause = strWhereClause & ", aStringColumn = '" & myStringcontrol & "'"
    endif
    if not isnull(mycontrol3) then
      strWhereClause = strWhereClause & ", aDateColumn = '" & format(mynumericcontrol,"#mm/dd/yyyy#") & "'"
    endif
    then your
    sql is
    strSQL = "Select my, comma, separated, column , list from mytable " & strWhereClause

    So I'm still running into query problems. My OnClick VB code looks like:

    Code:
    Dim group As String
    Dim year As Integer
    Dim year2 As Integer
    year2 = 2075
    
    strWhereClause = "Where 1=1"
    If Not IsNull(Me.cboGroups) Then
        group = Me.cboGroups.Value
        strWhereClause = strWhereClause & " and dbo_v_ScheduleWithInfo.Group_Name = '" & group & "'"
        Me.Text41 = strWhereClause
    End If
    If Not IsNull(Me.txtYear2) Then
        year2 = Me.txtYear2.Value
    End If
    If Not IsNull(Me.txtYear) Then
        year = Me.txtYear.Value
        strWhereClause = strWhereClause & "and (dbo_v_ScheduleWithInfo.ScheduledYear) between " & year & " and " & year2 & ""
        Me.Text41 = strWhereClause
    End If
    
    Me.qryScheduleWithInfo2.Requery
    My Query looks like:

    Code:
    ...WHERE [Forms]![frmAuditPlanner].[Text41]
    I've also tried the code below but get an "Enter Parameter Value" dialog box

    Code:
    ...WHERE [Forms]![frmAuditPlanner].[strWhereClause];
    A sample result in Text41 is
    Code:
    Where 1=1 and dbo_v_ScheduleWithInfo.Group_Name = 'Database Management Process (DO200, DO200A)'and (dbo_v_ScheduleWithInfo.ScheduledYear) between 2010 and 2015
    When I try to requery the subform, it doesn't produce the results it should be. If I copy and paste the result from Text41 into the Query SQL View and run it, it works perfectly. What am I doing wrong?
    Last edited by tkepongo; 08-04-11 at 15:27.

  10. #10
    Join Date
    Jul 2011
    Posts
    63

    Pass Function to Where Clause in Access Saved Query

    So I've learned that I can't pass a variable or control into a query but need to use a public variable and function. My query looks like this right now:

    Code:
    .....
    FROM dbo_v_ScheduleWithInfo
    WHERE GetWhereClause();
    This is what the GetWhereClause() function looks like:
    Code:
    Option Compare Database
    Public WhereClause As String
    
    Public Function GetWhereClause() As String
    GetWhereClause = WhereClause
    End Function

    This is what the Onclick() event looks like
    Code:
    Dim group As String
    Dim year As Integer
    Dim year2 As Integer
    year2 = 2075
    
    strWhereClause = "1=1"
    If Not IsNull(Me.cboGroups) Then
        group = Me.cboGroups.Value
        strWhereClause = strWhereClause & " and (dbo_v_ScheduleWithInfo.Group_Name) = '" & group & "'"
    End If
    If Not IsNull(Me.txtYear2) Then
        year2 = Me.txtYear2.Value
    End If
    If Not IsNull(Me.txtYear) Then
        year = Me.txtYear.Value
        strWhereClause = strWhereClause & " and (dbo_v_ScheduleWithInfo.ScheduledYear) between " & year & " and " & year2 & ""
    End If
    
    WhereClause = strWhereClause
    Me.Text41 = GetWhereClause()
    Me.qryScheduleWithInfo2.Requery
    The value in Text41 looks like:
    Code:
    1=1 and (dbo_v_ScheduleWithInfo.Group_Name) = 'Call Center' and (dbo_v_ScheduleWithInfo.ScheduledYear) between 2012 and 2075
    If I copy-paste the value from Text41 directly into the query, it works. Does anyone know what I'm doing wrong?
    Last edited by tkepongo; 08-05-11 at 13:00.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL statement for the query now and why do you use:
    Code:
    Me.qryScheduleWithInfo2.Requery
    Is qryScheduleWithInfo2 a query object or a control? If it's a query object it cannot be a property nor a member of any collection of Me, which is a shortcut to the current (active) form or report object. You can't address a query object that way.

    A query object is a member of the QueryDefs collection of the Database object. You need to create a QueryDef object and address the Query through it:
    Code:
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryScheduleWithInfo2")
    From there you can manipulate the properties and method of the query, but I'm rather sure it is not what you actually want or need to do. If it were you could as well modify the SQL property of the query there and you would not need to use a public function to pass a WHERE clause to it. You could use:
    Code:
    qdf.SQL = "SELECT ... FROM ... WHERE ...;"
    By the way, you don't need a public variable to work with the public function GetWhereClause. This would work and would be safer (a good advice is to avoid Public or Global variables if possible, because of unwanted side effects):
    Code:
    Public Function GetWhereClause(ByVal WhereClause As String) As String
        GetWhereClause = WhereClause
    End Function
    The code you use is correct and yield correct results except for Me.qryScheduleWithInfo2.Requery that cannot work.

    So what exactly does qryScheduleWithInfo2 do, or more precisely for what is it used in relation with the form where you try to manipulate it? Is it used as the RecordSource of a form or report, is it used as the RowSource of a ListBox or ComboBox?
    Have a nice day!

  12. #12
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Sinndho View Post
    What's the SQL statement for the query now and why do you use:
    Code:
    Me.qryScheduleWithInfo2.Requery
    Is qryScheduleWithInfo2 a query object or a control?

    .........

    So what exactly does qryScheduleWithInfo2 do, or more precisely for what is it used in relation with the form where you try to manipulate it? Is it used as the RecordSource of a form or report, is it used as the RowSource of a ListBox or ComboBox?
    The [qryScheduleWithInfo2] is a continuous subform who's source object is [subfrmAuditPlannerQuery]. [subfrmAuditPlannerQuery] is a continuous form who's Record Source is [qryScheduleWithInfo]. [qryScheduleWithInfo] is the saved query.

    I am using [qryScheduleWithInfo2] as a subform in [frmAuditPlanner]. [frmAuditPlanner] has controls that will allow me to filter the records in the subform.

    So given that [qryScheduleWithInfo2] is a subform in [frmAuditPlanner], would your suggestions work or is an alternative necessary?Sorry about the mess. I didn't know it can be so complicated to make a Query by Form.
    Last edited by tkepongo; 08-05-11 at 14:34.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to apologize

    As far as I know (but I may be wrong) you don't need to modify the query, you just need to filter its rowset in the subform. Can't you simply use:
    Code:
    Me.qryScheduleWithInfo2.Filter = strWhereClause
    Me.qryScheduleWithInfo2.FilterOn = True
    ' Not sure this last one is necessary:
    Me.qryScheduleWithInfo2.Requery
    Have a nice day!

  14. #14
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Sinndho View Post
    No need to apologize

    As far as I know (but I may be wrong) you don't need to modify the query, you just need to filter its rowset in the subform. Can't you simply use:
    Code:
    Me.qryScheduleWithInfo2.Filter = strWhereClause
    Me.qryScheduleWithInfo2.FilterOn = True
    ' Not sure this last one is necessary:
    Me.qryScheduleWithInfo2.Requery
    When I run it, I get a "Method or data member not found" error for the .Filter and .FilterOn. The only available option that's even close is FilterOnEmptyMaster

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops! it should be:
    Code:
    Me.qryScheduleWithInfo2.Form.Filter = strWhereClause
    Me.qryScheduleWithInfo2.Form.FilterOn = True
    ' Not sure this last one is necessary:
    Me.qryScheduleWithInfo2.Requery
    Sorry for that
    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
  •