Hi there

Sorry to bother people. I'm having problems with a piece of code and it's just not working!

It is
=DCount("*","Newsletter Database","[Date Joined]" >="[txtstartdate]" And <="[txtenddate"])

txtstartdate and txtenddate are text boxes on the form.

Hope someone can help!

Colin

I have also tried this

=DCount("*","Newsletter Database","[Date Joined] >=" & Nz([txtstartdate]) & "And <=" & Nz([txtenddate]))

However it just throws up #Error

Colin

Hi Colin,

I've been caught by this sort of thing before. The criteria clause is not
as smart as we are - it doesn't understand that the AND applies only to the
parameters! You need to include the field that the second parameter applies
to, and you'll also need to include date delimiter characters:

=DCount("*","Newsletter Database","[Date Joined] >= #" & Nz([txtstartdate])
& "# And [Date Joined] <= #" & Nz([txtenddate] & "#"))

HTH,

Rob


"Colin Weir" <(E-Mail Removed)> wrote in message
news:25022650.2977.1318939285861.JavaMail.geo-discussion-forums@vbzc27...
>I have also tried this
>
> =DCount("*","Newsletter Database","[Date Joined] >=" & Nz([txtstartdate])
> & "And <=" & Nz([txtenddate]))
>
> However it just throws up #Error
>
> Colin

Hi

Thanks for your reply - worked great! I had gotten a different solution toyours however I used yours as mine was more crude. However, after that I realised I was referencing the wrong thing. I was getting it to count values on a table when I was actually needing it to count a query. I changed the formula to accommodate however it is now saying #Error again. THe new code is as follows.

=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >= #" & Nz([txtstartdate]) & "# And [Date Joined] <= #" & Nz([txtenddate] & "#"))

Can you help please?

Thanks

Colin

Hi

Thanks for getting back to me - it worked great! Unfortunately I was counting the wrong thing. I've changed it to the Query it's meant to count, however it's giving me #Error again.

Can you help me please?

=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >= #" & Nz([txtstartdate]) & "# And [Date Joined] <= #" & Nz([txtenddate] & "#"))

Thanks

Colin

On Tue, 18 Oct 2011 06:11:25 -0700 (PDT), Colin Weir
<(E-Mail Removed)> wrote:

>Hi
>
>Thanks for getting back to me - it worked great! Unfortunately I was counting the wrong thing. I've changed it to the Query it's meant to count, however it's giving me #Error again.
>
>Can you help me please?
>
>=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >= #" & Nz([txtstartdate]) & "# And [Date Joined] <= #" & Nz([txtenddate] & "#"))
>
>Thanks
>
>Colin

What's NLCT Carers? Does it contain the Date Joined and Reference Number
fields?

If you just want to count records (without specifying which field within the
record) you can use

=DCount("*", "queryname", "criteria")
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

Colin Weir <(E-Mail Removed)> wrote in
news:5498291.87.1318942517331.JavaMail.geo-discussion-forums@vbau1:

> Hi
>
> Thanks for your reply - worked great! I had gotten a different
> solution to yours however I used yours as mine was more crude.
> However, after that I realised I was referencing the wrong thing.
> I was getting it to count values on a table when I was actually
> needing it to count a query. I changed the formula to accommodate
> however it is now saying #Error again. THe new code is as
> follows.
>
> =DCount("[Reference Number]","[NLCT Carers]","[NLCT Carers]![Date
> Joined] >= #" & Nz([txtstartdate]) & "# And [Date Joined] <= #" &
> Nz([txtenddate] & "#"))
>
> Can you help please?
>
> Thanks
>
> Colin
>

You have at least one misplaced closing parenthesis.
change
"# And [Date Joined] <= #" & Nz([txtenddate] & "#"))
to
"# And [Date Joined] <= #" & Nz([txtenddate]) & "#")


--
Bob Q.
PA is y I've altered my address.

Read Bob's reply: your parentheses are wrong.

However, just in case any of your users have their Regional Settings such
that their date is dd/mm/yyyy, you really should use:

=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >=
" & Format(Nz([txtstartdate]), "\#yyyy\-mm\-dd\#") & "And [Date Joined] <= "
& Format(Nz([txtenddate]), "\#yyyy\-mm\-dd\#"))

"Colin Weir" wrote in message
news:14363569.346.1318943485538.JavaMail.geo-discussion-forums@vbat14...

Hi

Thanks for getting back to me - it worked great! Unfortunately I was
counting the wrong thing. I've changed it to the Query it's meant to count,
however it's giving me #Error again.

Can you help me please?

=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >=
#" & Nz([txtstartdate]) & "# And [Date Joined] <= #" & Nz([txtenddate] &
"#"))

Thanks

Colin

On Tuesday, 18 October 2011 16:43:53 UTC+1, John W. Vinson wrote:
> On Tue, 18 Oct 2011 06:11:25 -0700 (PDT), Colin Weir
> <colin...@blueyonder.co.uk> wrote:
>
> >Hi
> >
> >Thanks for getting back to me - it worked great! Unfortunately I was counting the wrong thing. I've changed it to the Query it's meant to count, however it's giving me #Error again.
> >
> >Can you help me please?
> >
> >=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >= #" & Nz([txtstartdate]) & "# And [Date Joined] <= #" & Nz([txtenddate] & "#"))
> >
> >Thanks
> >
> >Colin
>
> What's NLCT Carers? Does it contain the Date Joined and Reference Number
> fields?
>
> If you just want to count records (without specifying which field within the
> record) you can use
>
> =DCount("*", "queryname", "criteria")
> --
>
> John W. Vinson [MVP]
> Microsoft's replacements for these newsgroups:
> http://social.msdn.microsoft.com/For...-US/accessdev/
> http://social.answers.microsoft.com/.../en-US/addbuz/
> and see also http://www.utteraccess.com

Thanks

NLCT Carers is a query that contains both Reference Number and date joined.I changed the formula as you described to

=DCount("*","[NLCT Carers]","[Date Joined]>= ([txtstartdate]) And <= ([txtenddate])")

Which throws up an error. I then tried

=DCount("*","[NLCT Carers]","'[Date Joined]>=[txtstartdate] And <= [txtenddate]'")

However this is counting all the records prior to any information being entered into txtstartdate and txtenddate.

Hope you can help?

Colin

Hi

Thanks for your reply - really apreciate it. As I'm in the UK the date format here is dd/mm/yyyy. I ammended the code you provided to

=DCount("[Reference Number]","[NLCT Carers]”,”[NLCT Carers]![Date Joined] >= " & Format(Nz([txtstartdate]), "\#-dd\-mm\yyy\#") & "And [Date Joined] <= " & Format(Nz([txtenddate]), "\#-dd\-mm\yyyy\#"))

However it throws up #Error.

Sorry

Colin