Code help

[Colin Weir]

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

 

[Colin Weir]

I have also tried this

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

However it just throws up #Error

Colin

 

[Rob Parker]

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]

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

 

[Colin Weir]

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

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Quintal]

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]) & "#")

 

[Douglas J Steele]

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

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

 

[Colin Weir]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/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

 

[Colin Weir]

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

 

[Colin Weir]

Hi

I have the formula working now with

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

However it doesn't change automatically. Any suggestions?

Thanks

Colin

 

[John W. Vinson]

Hi

I have the formula working now with

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

However it doesn't change automatically. Any suggestions?

Thanks

Colin

When should it "change automatically"? Where did you put this expression?

You might need to requery this control in the AfterUpdate events of
[txtstartdate] and [txtenddate].
--

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

 

[Colin Weir]

I've put this code directly into the Control Source of the text box. Wanting it to change after I've put the dates into txtstartdate and txtenddate.

Thanks

Colin

 

[John W. Vinson]

I've put this code directly into the Control Source of the text box. Wanting it to change after I've put the dates into txtstartdate and txtenddate.

Thanks

Colin

In that case, put either a Macro or VBA code in the AfterUpdate events of
txtstartdate and txtenddate to Requery your textbox.
--

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

 

[Colin Weir]

Thanks

That seems to work. I now get prompted to enter a perameter value Forms!Totals!Category

Sorry.

Colin

 

[John W. Vinson]

Thanks

That seems to work. I now get prompted to enter a perameter value Forms!Totals!Category

Sorry.

Colin

If you're getting prompted, it means that Access can't find an open form named
Totals, or that it doesn't have a control named Category. Is the form open at
the time you run the code?
--

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

 

[Colin Weir]

Ah - I see.

The form "Totals" is the form I have copied to be "Forms New" and I am adapting to make these changes. Does that mean it's trying to reference the previous form? Is this something I can change?

Regards

Colin

 

[Colin Weir]

I have deleted the part of the form that related to "Category" and this has sorted it. So I know where the issue was arrising, however I would prefer not to do it this way.

Thanks

Colin

 

[Colin Weir]

I've worked around it so thank you!

Colin

 

[Douglas J Steele]

Your regional settings may be dd/mm/yyyy, but Access is not going to accept
it. To you, 1/10/2011 may be 1 Oct, 2011, but to Access it will ALWAYS be
interpretted as 10 Jan, 2011. Read what Allen Browne has in
http://www.allenbrowne.com/ser-36.html

"Colin Weir" wrote in message

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