Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Handeling If null

    Hello,

    Ill post the SQL of a query I'm having issue with:

    Code:
    SELECT JobT.Project_ID, Count(JobT.Date_Complete) AS CountOfDate_Complete, Count(JobT.Job_ID) AS CountOfJob_ID, Sum(JobT.[removed from scope]) AS [SumOfremoved from scope]
    FROM JobT
    GROUP BY JobT.Project_ID
    HAVING (((JobT.Project_ID)=[Forms]![background form]![Project]) AND ((Sum(JobT.[removed from scope]))=True));
    I'm using a few querys (this being the first) to work out the percentage of work complete on a project. Then show a graphic to represent the work complete.

    Problem here is.... If the query returns nothing. There is no corresponding image to display. The form in this case will go totally blank.

    So... I need data..(or a suggestion for a workaround)

    Is it possible to have something that will show a result if there is not one to display. (hopefully that made sense).

    Just as an FYI and an example. A project may be totally new. So the project does exist... But it has no relationship to any Jobs. This is one of the ways this error occurs.

    Any/all advice appreciated.

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    This is the final query that will select the picture. Maybe it makes more sense to deal with this one for "if null"

    Code:
    SELECT picture.pic
    FROM picture INNER JOIN [percentage rounded up] ON picture.percentage = [percentage rounded up].Expr1;

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps something like

    Code:
    SELECT nz(p.pic, d.pic) as disp
    FROM (SELECT pic FROM picture WHERE ID=defaultID) d, (SELECT pic
    FROM picture INNER JOIN [percentage rounded up] ON picture.percentage = [percentage rounded up].Expr1) p
    (SELECT picture.pic FROM picture WHERE ID=defaultID) - this should return one record only

    scrap that - doesn't work

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Thanks for your reply. Ill attempt to understand more what I'm trying to achieve. Sometimes that works. haha.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    don't have tables to set up and test but assuming pic is a text field containing a path to a picture try

    Code:
    SELECT nz(picture.pic,"C:\images\default.jpg") as disp
    FROM [percentage rounded up] LEFT JOIN picture ON [percentage rounded up].Expr1=picture.percentage

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Just throwing some things out. Can you just test the results and if nothing, display a msgbox or something else on the form?

    rs1 = "Select ..."
    if rs1.RecordCount = 0 then
    msgbox "No Records Found"
    End If

    Also on your form, base the top on the main project so it has data and the subform on the details so if the subform has no data, the main form should still show data.

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    This right now is just for testing, Ill follow up all suggestions shortly. But as a matter of interest....

    The only way I can currently get this image to show is if the forms record source is the query. I'm pretty sure I can have a record source for the image be the query.. So far that has been unsuccessful. (Ill attempt this again making sure that there is data to display. correct me if I'm wrong)

    Failing that. I could split the form to have several sub forms. I know this would work but may take some work to look remotely decent.

    I may be being stupid here. But... using VBA to define the record source....

    Where do I do the code? I can probably do the code myself. with some trial and error... I just don't know where I'm doing it. (this is probably something I've done before to be honest.. but I have as many excuses as an F1 driver...My brain hasn't recovered from new years being the main one. haha)

    Ill post back tomorrow probably when I get some more time with this.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Ajax, Thanks for that, Still nothing.

    Code:
    SELECT nz(picture.pic,"C:\Users\Admin\Pictures\untitled.png") AS disp
    FROM [percentage rounded up] LEFT JOIN picture ON [percentage rounded up].Expr1=picture.percentage;
    Interestingly, when there is data to display, no picture loads but the form does. When there's no data it's still all blank.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK this sounds like your [percentage rounded up] is a query which is returning nothing as well. Perhaps the issue is there and can be fixed in a similar way with a left join between the jobs table and whatever the query is doing

  10. #10
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Yes that's right. from the first query to the last (there's about 4) if the first one is blank then all are. Im attempting to do this via VBA now which brings its own problems for me. Ive tried using NZ all over the place with no joy.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    So you need to start at the first one - can you provide the sql?

  12. #12
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    That's the one in the first post mate.

    Its because there is no related jobs to that project when the project is a new one.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK - so I think you need an additional query left joining the list in your combo to that query

  14. #14
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I think im going to go down the VBA route providing I can get that working. However, Im still interested in the nz function and it's use. So ill be looking into that. I'm not entirely sure what you mean.

    But ill give it a go! I understand what youre saying here but i thought that this was the purpose of the function.

    Ill work it out though. Ill try a few things with left joins.

    Thanks for the help.

  15. #15
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    It was my misunderstanding of the function that was the problem. I've done a left join as you suggested (I assumed I didn't need to as this was the "null"). All working fine now.

    I was getting a null error when using calculated fields. But I've also dealt with that. Ironic how I struggle with two possible solutions for a few days then I manage to get them both working at the same time.

    Your help on both is appreciated. Cheers.

    All this for a picture haha.

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

Similar Threads

  1. Replies: 7
    Last Post: 11-07-2016, 09:24 AM
  2. Query handeling empty FK keys
    By warlock916 in forum Access
    Replies: 1
    Last Post: 01-12-2016, 07:18 AM
  3. Error Handeling for a 3201 and 3022 Error
    By starlancer805 in forum Access
    Replies: 3
    Last Post: 03-11-2015, 12:46 PM
  4. Replies: 1
    Last Post: 02-23-2012, 02:27 PM
  5. Handeling Multipule Units of Measure
    By Waldenbound in forum Database Design
    Replies: 1
    Last Post: 09-02-2011, 08:24 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