Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72

    Query returning all values instead of specified string


    Hello, I have a form that users fill in to compile a report with several options. The query works for all but one section. The ones that do work seem to have a IsNull column in the query but if I copy and paste this for the "Outcome" column on mine that isn't working, if I specify anything in the Outcome box the report doesn't return anything at all. I've attached a screen shot showing the query (the location works so I've included that, it's the Outcome one that doesn't) Don't know why the criteria is duplicated so many times, I didn't do that. I've also attached a screen shot of the same query in the SQL view. Sorry, I'm really new to this so this is probably something simple or obvious but I've been on it for hours
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    <>false is actually a TRUE.
    maybe you should not use prebuilt queries that check for NULL,and instead, build it on the fly,
    and ignore fields that are null. (you see the trouble that does)

    Code:
    '----------------
    sub btnReport_click()
    '----------------
    dim sWhere as string 
    dim qdf as querydef
    
    sWhere = "(1=1)"
    if not IsNUll(cboCat) then sWhere = sWhere & " and [State]='" & cboCat & "'"
    if not IsNUll(cboOffNum) then sWhere = sWhere & " and [officenum]='" & cboOffNum & "'"
    
    docmd.OPenReport "rMyReport",acViewPreview,,sWhere
    
    
    'or build the query behind the report
    
    set qdf = currentdb.querydefs("qsMyQuery")
    qdf.sql = "select * from table where " & sWhere
    qdf.close
    
    
    docmd.OPenReport "rMyReport",acViewPreview
    end sub

  3. #3
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Hi, thank you for the reply and sorry for my delay. I did tick email notifications when someone replies but it didn't for some reason. I copied your code onto my Analysis Builder forum OK button click but still doing the same thing, e.g. even if I specify "Referral" in the form it returns all results. I've attached a test database so you can see what I mean. Thanks again for the help. It's frustrating!
    Attached Files Attached Files

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When someone on this list provides code/SQL, you need to change it to suit the object names in your database.

    Code:
    If IsNull(Me!StartDate) Or IsNull(Me!EndDate) Or Me!StartDate > Me!EndDate Then
        MsgBox "Start and End dates not entered correctly"
        Exit Sub
    End If
    
    sWhere = "Behaviour.[Incident Date] Between #" & Me!StartDate & "# And #" & Me!EndDate & "#"
    If Not IsNull(Me!YearGroup) Then sWhere = sWhere & " And Behaviour.[Year Group]='" & Me!YearGroup & "'"
    If Not IsNull(Me!Location) Then sWhere = sWhere & " And Behaviour.[Location]='" & Me!Location & "'"
    If Not IsNull(Me![Behaviour Type]) Then sWhere = sWhere & " And Behaviour.[Behaviour Type]='" & Me![Behaviour Type] & "'"
    If Not IsNull(Me!Outcome) Then sWhere = sWhere & " And Behaviour.[Outcome]='" & Me!Outcome & "'"
    Debug.Print sWhere

  5. #5
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Thank you. I've changed the VBA associated with the command button click as suggested but still not working. I've attached what I've done. I just don't understand it unfortunately, but don't know why that's the only one not working.
    Attached Files Attached Files

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "Not working" means nothing to us. Post the result of the Debug.Print sWhere statement and tell us exactly what is happening.

  7. #7
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I'm not getting an error. It just doesn't filter any of the results. E.g. if I specify "internal monitoring" in the form it still returns all values.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I cannot run your report, there is something I don't have installed on my system. The problem is that the sWhere string applies to the record source of your report. However, there is none. It looks as tho all the information comes from query "Analysis Query ActionJHB". This needs to be the record source of your report, then each subreport must be linked to the main report using the Link Master and Link Child fields.

  9. #9
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I don't think I have linked any of the other fields. There are several queries and if I run AnalysisQueryActionJHB it shows me the table it is referring to. This query pulls in separate queries so when I click on those I get the counts. They all work except the Outcome one and I cannot see what I've done differently.This one works:SELECT [Analysis Query ActionJHB].Location, Count([Analysis Query ActionJHB].Location) AS CountOfLocationFROM [Analysis Query ActionJHB]GROUP BY [Analysis Query ActionJHB].Location;This one doesn't: SELECT [Analysis Query ActionJHB].Outcome, Count([Analysis Query ActionJHB].Outcome) AS CountOfOutcomeFROM [Analysis Query ActionJHB]GROUP BY [Analysis Query ActionJHB].Outcome;It just returns all the values in that field and they look identical to me.

  10. #10
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I can't even see a button to put this reply in the correct code box!!!

  11. #11
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I didn't type that message like that and I've tried to edit it!

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Actually, none of them work when you use the code provided. In post #2, Ranman said use code instead of doing it in the query. The query was not changed, so the code has become meaningless. You need to decide to either use the query or to use code. My advice has been based on using code.

  13. #13
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Ok, sorry, I didn't understand that. Let me try then without the query and just running the code from the command button. Thank you for your patience!! Could you direct me to some online lessons that would help my understanding. I've been through some basic ones but they were running manual queries. The issue is I don't really know what I don't know so I'm apparently googling the wrong things (if that makes any sense!)

  14. #14
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I tried to change the form so that it wasn't linked to the query anymore but guess I did it wrong as it still doesn't filter the results in the Outcome box. Thanks for your efforts but I think I'll give up on this one, I'm not getting anyway

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Give up? Bad habit! If you want help with the solution you can come back to us.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-11-2016, 02:34 AM
  2. Replies: 4
    Last Post: 10-13-2015, 02:29 PM
  3. Replies: 5
    Last Post: 07-10-2015, 08:30 AM
  4. Query not returning all values
    By whitelexi in forum Queries
    Replies: 12
    Last Post: 09-06-2014, 11:40 PM
  5. Query not returning null values
    By janelgirl in forum Access
    Replies: 5
    Last Post: 10-11-2011, 10:31 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