DCount Help

[steve711]

Hello All,

I've looked at the DLookup Usage Samples on Access Web but am unable to apply it to my needs.

I keep getting wrong number of arguments...

I am trying to count the number of days OFF for a particular pilot between two dates (payroll dates).

Here is what I've got so far, I think I'm close but cannot get over the hump.

DaysOffCount = DCount("[tbl_Entry.Pilot]", "tbl_Entry", "[Pilot] = '" & strPilot & "'" _
                And [tbl_Entry.Daily_Status], "tbl_Entry", _
                "[Daily_Status] = '" & strOff & _
                "' and [tbl_Entry.flt_Date] between #" & [Forms]![frm_Entry]![txtStart] & "# and #" & [Forms]![frm_Entry]![txtEnd] & "#")

 

[namliam]

Dcount syntax (from the access help):
DCount(Expr, Domain, Criteria)

You only have 1 expresion (what do you want to count)
1 Domain (what table)
1 Criteria (your limitations)

You are repeating your table "tbl_Entry" which is wrong...

I think this is what you are after:

strCrit = "[Pilot] = '" & strPilot & "' " & _
     " And [Daily_Status] = '" & strOff & "' " & _
     " And [flt_Date] between #" & [Forms]![frm_Entry]![txtStart] & "# " & _
                        " And #" & [Forms]![frm_Entry]![txtEnd] & "#"
DaysOffCount = DCount("[tbl_Entry.Pilot]", "tbl_Entry", strCrit)

Or if you want to force it on one command:

DaysOffCount = DCount("[tbl_Entry.Pilot]", "tbl_Entry", "[Pilot] = '" & strPilot & "' " & _
                                                   " And [Daily_Status] = '" & strOff & "' " & _
                                                   " And [flt_Date] between #" & [Forms]![frm_Entry]![txtStart] & "# " & _
                                                                      " And #" & [Forms]![frm_Entry]![txtEnd] & "#" )

Happy coding !

 

[steve711]

You know I really love this forum.

Thank you for the help, I always struggle with those statements, too many " and '.

 

[namliam]

too many " and '.

That is not true...
Your problem is / was that your code is UN - FREAKING - READABLE

Sorry for shouting, but it is a bet peave of mine.... Making stuff readable makes, 9 times out of 10, the code easier to write, maintain and support. So why not put in 10 extra seconds to space out stuff and actually do make it readable??

In the spaced out sample I gave you, anyone looking at the code can quickly see what it is searching on, because the fields are direcly below eachtother.

Neatness counts! Take it from someone who has inherited a few databases in my lifetime... Making it readable helps a WHOLE lot!