Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    36

    Unanswered: Report Expression

    I have a Report with 2 Fields, the first field is bound to a table and generates a list of names on the report held within that table. The second field is unbound and I would like to build an expression into this field that counts another field (Nights) within another table (HaemDailyRota1) for the name dispalyed in the first field (bmsNames)

    I used this expression as the control source but it is just displaying #Error


    =DCount([bmsName],[HaemDailyRota1]![Nights],[Nights]=[bmsName])

    Can anyone spot any obvious errors in the expression?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    #name# usually means the compiler can't resolve a column / table / variable name. that could be either a typo or the item isn't visible in the current scope. ie its not a variable present in the current fucntion / sub routine / form / module...

    are you certain that's the correct form for the dcount function
    DCount Function [Access 2003 VBA Language Reference]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2011
    Posts
    36
    Thanks for the link, thats now working with;

    =DCount("Nights","HaemDailyRota1","Nights = bmsName")

    Now I need to add a filter to the expression tofiler between 2 dates, think I need to do some more reading, I have no idea how to do that. I have a date picker that loads with the report that returns a start date and finnish date, but how I build these into the expression I have no idea.

  4. #4
    Join Date
    Sep 2011
    Posts
    36
    =DCount("Nights", "HaemDailyRota1", "Date1 BETWEEN DateValue('" & Me.FromDay & "') AND DateValue('" & Me.ToDay & "') "Nights = bmsName")

    This is what I come up with but im getting syntax error

  5. #5
    Join Date
    Sep 2011
    Posts
    36
    Sorted it now

    =DCount("SunNight","HaemDailyRota1"," [HaemDailyRota1]![Date1] Between Forms![Select Report Dates]![frD] And Forms![Select Report Dates]![toD] And [HaemDailyRota1]![SunNight] = [bmsName]")

Posting Permissions

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