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

    Unanswered: Report Query for day of week

    Im using the following expression in the control source of a text box on a report to count the number of entries in the database



    =DCount("Nights","HaemDailyRota1"," [HaemDailyRota1]![Date1] Between Forms![SelectReportDates]![frD] And Forms![SelectReportDates]![toD] And [HaemDailyRota1]![Nights] = [bmsName]")



    This works fine for what it does, but it would be usefull if I could select a particular day of the week for example only count the entries that are on mondays, could I modify this expression just to pick out ones on mondays?



    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the DatePart() function and add a condition to the WHERE clause:
    Code:
    AND DatePart("w", [HaemDailyRota1]![Date1], vbMonday) = 1
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    36
    Not sure if I understood you right, but this is what I came up with, but I just get syntax error.

    =DCount("SunNight","HaemDailyRota1"," [HaemDailyRota1]![Date1] Between Forms![SelectReportDates]![frD] And Forms![SelectReportDates]![toD] And [HaemDailyRota1]![SunNight] = [bmsName] And DatePart("w", [HaemDailyRota1]![Date1], vbMonday) = 1")

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The constant vbMonday is not recognized in the criteria. It should be (notice the single quotes: 'w'):
    Code:
    =Debug.Print DCount("SunNight", "HaemDailyRota1", _
                       "(HaemDailyRota1.Date1 Between [Forms]![SelectReportDates]![frD] And [Forms]![SelectReportDates]![toD]) AND (DatePart('w',[HaemDailyRota1]![Date1]," & vbMonday & ")=1)")
    However you can also use the numeric value of vbMonday (2), which makes the expression more readable:
    Code:
    =DCount("SunNight", "HaemDailyRota1", _
                       "(HaemDailyRota1.Date1 Between [Forms]![SelectReportDates]![frD] And [Forms]![SelectReportDates]![toD]) AND (DatePart('w',[HaemDailyRota1]![Date1], 2)=1)")
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    36
    Hi thanks for the examples, you missed out the critereia "And ([HaemDailyRota1]![Nights] = [bmsName])" but I got it working as below.

    =DCount("Nights","HaemDailyRota1","(HaemDailyRota1 .Date1 Between [Forms]![SelectReportDates]![frD] And [Forms]![SelectReportDates]![toD]) And ([HaemDailyRota1]![Nights] = [bmsName]) AND (DatePart('w',[HaemDailyRota1]![Date1], 2)=1)")

    Would it be possible to search for three different dates in the same expression? ie count the ones on monday tuesday or wednesay but not the other? rather than focus on just one day?

    Thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use:
    Code:
    "DatePart('w',[HaemDailyRota1]![Date1]," & vbMonday & ") IN (1, 2, 3)")
    Have a nice day!

Posting Permissions

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