Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Probably a really simple question but can't get this to work. I'm using this as part of some code in a database I'm doing

    strwhere =" (DateIssued between #" & Format(Me.Combo0, "mm/dd/yyyy") & "# AND #" & Format(Me.Combo2, "mm/dd/yyyy") & "#) and"

    where combo0 is the start date and combo2 is the end date, this is part of passing some variables to a query depending on the combo box being Null or not. What I want to do is change thisso that the dates may or may not be used too but I don't know how to do this as it has 2 variables in it, combo0 and combo2. I know how to do this if it dealt with just 1 combo box, for example my next line is

    If Not IsNull(Me.Combo4) Then
    strwhere = strwhere & "(Regime_11 =" & Chr(34) & Me.Combo4 & Chr(34) & ") and"
    End If

    So just looking for some help on how to change the first line into If Not IsNull as well

    Cheers

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm not sure how you are passing the "WHERE clause" to your query - are you assembling it in VBA as a query, creating a filter for a form, or modifying a saved query using the QueryDef approach? In any event you need to deal with four separate conditions:
    1 - Both combos are null
    2 - Combo0 is null
    3 - Combo2 is nul
    4 - Neither combo is null
    ln case1 you don't want a WHERE clause, in case 2 you want a string that says ">= Combo0", in case 3 you want "<= Combo2" and in case 3 you want either "Between Combo0 And Combo2" or ">=Combo0 and <=Combo2"
    Wendell

  3. #3
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is something I already have running and works fine, it's a form I use so that others can select what type of report they want to run and then depending on that I have 8 combo boxes that will either be enabled or disabled and set to null with something like this

    Select Case Me.Combo6
    Case "All Outputs"
    Me.Combo0.Enabled=True
    Me.Combo2.Enabled=True
    Me.Combo4.Enabled=False
    Me.Combo4=Null
    Me.Combo12.Enabled=False
    Me.Combvo12=Null

    and so on for all the combo boxes and then for all the diffrent report types(about 15 diffrent types or so) I use a report with all of the possible criteria selected and when passed to it it will only use the combos that are enabled as criteria. Tis all works fine but now I want to change it so that the start date and end date are not always active and was looking for a bit of help as to how to enter that in the where clause instead of what I have just now

    strwhere =" (DateIssued between #" & Format(Me.Combo0, "mm/dd/yyyy") & "# AND #" & Format(Me.Combo2, "mm/dd/yyyy") & "#) and"

    I would like to change this so it is also If Not IsNull but as it's dealing with 2 combo boxes instead of 1 I don't really know how to enter it.

    Cheers

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The point I was trying to make (but didn't very well) is that you can't really do it with a single statement. Instead you need something like this:
    Code:
    If Not IsNull(Me.Combo0) Then
        If Not IsNull(Me.Combo2) Then
            strwhere =" (DateIssued between #" & Format(Me.Combo0, "mm/dd/yyyy") & "# AND #" & Format(Me.Combo2, "mm/dd/yyyy") & "#) and"
        Else
            strwhere =" (DateIssued >=" & Format(Me.Combo0, "mm/dd/yyyy") & "#) and"
        End If
    Else
        If Not IsNull(Me.Combo2) Then
            strwhere =" (DateIssued <=" & Format(Me.Combo2, "mm/dd/yyyy") & "#) and"
        Else
    '        Do nothing in this case, as both are empty
        End If
    End If
    Again this is air code, so test and debug it carefully.
    Wendell

  5. #5
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry I forgot to say that only conditions 1 and 4(i.e. they will both either be null or both have a value), I think I got a bit carried away rambling on and forgot to add that

  6. #6
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi, ive not read everything, so fi this has been said before i am sorry,

    but in Access isnt Isnull(#value) a boolean,

    so in the if statements wouldnt you say

    if isnull(#value) = True then...
    blah blah...

    Sorry if i have misread the problem.

    (in this situation it would actually be "If isnull(#value) = False"

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Christopher Theobald View Post
    hi, ive not read everything, so fi this has been said before i am sorry,

    but in Access isnt Isnull(#value) a boolean,

    so in the if statements wouldnt you say

    if isnull(#value) = True then...
    blah blah...

    Sorry if i have misread the problem.

    (in this situation it would actually be "If isnull(#value) = False"
    IF IsNull([Control]) Then is the same as IF IsNull([Control])=True Then

    Similarly

    IF NOT IsNull([Control]) Then is the same as IF IsNull([Control])=False Then

    Just less to type
    Andrew

  8. #8
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ah ok, i didnt realise that, you learn something new every day then

  9. #9
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Soory been away for a week so late in replying but thanks guys, got this to work so all looking good now.

    Cheers

Posting Permissions

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