Results 1 to 11 of 11

Thread: null to zero

  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unanswered: null to zero

    i am comparing two date values and i need to show a zero value where there null value.How can i do this from :

    Between [wk1st] And [wk1end]

    it just returns the the field names. Thanks dave

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I'm not sure I understand your question correctly, but the null replacement function in Access is Nz(variant,[valueIfNull])
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    i am getting values from my sql statement:

    SELECT [tblLinkShipment Lines].PNO, Sum([tblLinkShipment Lines].Count) AS [Count]
    FROM [tblLinkShipment Lines], tblweeksummary
    WHERE ((([tblLinkShipment Lines].Date) Between [wk1st] And [wk1end]))
    GROUP BY [tblLinkShipment Lines].PNO;

    basically searching between two dates and and returning a sum value.
    But when there is a no value to return i need it to return a zero. At the moment is displays just the greyed out field names with no value.

  4. #4
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    20
    Originally posted by davieboy_xr
    i am getting values from my sql statement:

    SELECT [tblLinkShipment Lines].PNO, Sum([tblLinkShipment Lines].Count) AS [Count]
    FROM [tblLinkShipment Lines], tblweeksummary
    WHERE ((([tblLinkShipment Lines].Date) Between [wk1st] And [wk1end]))
    GROUP BY [tblLinkShipment Lines].PNO;

    basically searching between two dates and and returning a sum value.
    But when there is a no value to return i need it to return a zero. At the moment is displays just the greyed out field names with no value.

    Try this.... WHERE (((Nz([tblLinkShipment Lines].Date)) Between .....

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If you get a type mis-match error, specify the zero for valueIfNull. The omitted default is a zero length string which should be comparable to dates:

    WHERE (((Nz([tblLinkShipment Lines].Date),0) Between
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    thanks for reply , still getting the same .Does anyone think an if statement would do the job?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE
    (
    [tblLinkShipment Lines].Date) is null
    OR
    [tblLinkShipment Lines].Date) Between [wk1st] And [wk1end]
    )


    rudy
    http://r937.com/

  8. #8
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Red face

    cheers rudy.. but still its still no count displaying a zero.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't understand your problem

    what do you mean, "still no count displaying a zero"?

    can you give some sample rows from the table, and show what sort of result you expect the query to produce

    rudy

  10. #10
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    Much appriciated for the help!


    Wk1St Wk1End
    01/01/2003 07/01/2003

    that is my table for the date ranges.This is used for weeks in the year.

    This the sql in access thtat get the the part no, from the date range and count how many parts fall in between those dates:


    SELECT [tblLinkShipment Lines].PNO, Sum([tblLinkShipment Lines].Count) AS [Count]
    FROM [tblLinkShipment Lines], tblweeksummary
    WHERE ((([tblLinkShipment Lines].Date) Between [wk1st] And [wk1end]))
    GROUP BY [tblLinkShipment Lines].PNO;

    Here is some out put data which workds fine;

    PNO Count
    HM0018/INS/G-DAS 2.4
    HM0023-FMC/GNK 13.2
    HM0024/ 4
    HM0024/50-FMC/HER 4
    HM0024-TKW 5
    HM0215-FMC/GNK 10.35
    HM0224P/20-DAS 1
    HM0224P/73-DAS 3.5



    Its when it doesnt count a part number i need it to display zero as this needs to be exported into an excel sheet.Say input the date some where in 2006 the part no wont be recognised as it has been input in the database. I need it to count 0. Many thanks

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i did not see that you were using a join

    now the answer is easy, just use an outer join:
    Code:
    SELECT [tblLinkShipment Lines].PNO
         , Sum([tblLinkShipment Lines].Count) AS [Count]
      FROM [tblLinkShipment Lines]
    left outer
      join tblweeksummary
        on [tblLinkShipment Lines].Date
           Between [wk1st] And [wk1end]
    GROUP 
        BY [tblLinkShipment Lines].PNO
    rudy

Posting Permissions

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