Expression Syntax

[Colina]

Hi

I'm trying to use this expression, which currently only
half works. Can anyone identify where my syntax/expression
is wrong and how I can fix it?

Its to calculate the percentage of non Rota staff. On a
report.

=DCount("[InvID]","tblInvoices","[OnRota]='Not On
Rota'")/DCount("[InvID]","tblInvoices") Between [Forms]!
[frmDateRange]![StartDate] And [Forms]![frmDateRange]!
[EndDate]

I have set the control format to percent this is why there
is no *100 anywhere.

I can remove the Between section and it works fine however
it then performs the expression on the entire record set.
I need it to do it between the dates specified.

Thanks for looking

Colin

 

[Marshall Barton]

Hi

I'm trying to use this expression, which currently only
half works. Can anyone identify where my syntax/expression
is wrong and how I can fix it?

Its to calculate the percentage of non Rota staff. On a
report.

=DCount("[InvID]","tblInvoices","[OnRota]='Not On
Rota'")/DCount("[InvID]","tblInvoices") Between [Forms]!
[frmDateRange]![StartDate] And [Forms]![frmDateRange]!
[EndDate]

I have set the control format to percent this is why there
is no *100 anywhere.

I can remove the Between section and it works fine however
it then performs the expression on the entire record set.
I need it to do it between the dates specified.

I believe you need to use the values from the form controls,
not just the reference:

Between " &
Format(Forms![rmDateRange!StartDate, "\#m\/d\/yyyy\#") & "
And " &
Format(Forms!frmDateRange!EndDate, "\#m\/d\/yyyy\#")