Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    16

    Unanswered: Return default value in query if criteria not match

    Can a query return a default value instead of nothing showed if the criteria not match. Thank you

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I know no quick fix for that. but if you could tell why you need it, maybe I could come up with a workaround.
    ghozy.

  3. #3
    Join Date
    May 2004
    Posts
    16
    I'm trying to make weekly production report where it shows production of each employee each day(Mon-Fri). If employee does not work on that day, the value for production will be 0.

    Thank you.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    in this case Nz() function could help you I guess.
    Nz(valueorfieldname,0) will produce zero instead of null, if the field is null.
    ghozy.

  5. #5
    Join Date
    May 2004
    Posts
    16
    I'll try it. Thank you very much.

  6. #6
    Join Date
    May 2004
    Posts
    16
    It doesn't work for me ghozy. I have 4 fields: 3 number and 1 text. I want 3 number return 0 and text return null. Thank you.

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can use nz function with text too. nz(fieldname,""). though I don't know any detail about your project so I can't really say more.
    ghozy.

  8. #8
    Join Date
    May 2004
    Posts
    16
    This is my query, can you take a look to see wat wrong with it? Thank you very much.

    SELECT Nz([ScanHour],0) AS [Scan Hour], Nz([OtherHour],0) AS [Other Hour], Nz([TotalHour],0) AS [Toal Hour], Nz([OtherWork],"") AS [Other Work]
    FROM tblWorkHour
    WHERE (((tblWorkHour.WorkDate)=[Forms]![FrmReport]![StartDate]) AND ((tblWorkHour.EmployeeID)=CurrentUser()));

  9. #9
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    It doesn't seem like wrong at all.
    there is a work around for that but its really ugly.
    ghozy.

  10. #10
    Join Date
    May 2004
    Posts
    16
    ghozy, can I see the work around? Maybe, it will work for me. Thank you very much.

  11. #11
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    create a table with the fields you have in your query that you use in your report. Then in your report's open event check the report's Hasdata property. if it doesn't have any data which means your query returns nodata, change the report's recordsource property to this empty table.
    something like this:

    Private Sub Report_Open(Cancel As Integer)
    If Not Me.HasData Then Me.RecordSource = "table1"
    End Sub

    ' table1 is the table with default values.
    I hope this helps.
    ghozy.

Posting Permissions

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