dcount of date range in report not working

[terri]

I'm so confused
what did i do wrong here?

I am trying to count questions that have been answered for a audit's date
range.

I want to know the total of questions that have question 4a=1 as its answer
and where 4b has '2' as its answer, from a certain hospital and where the
audit date is a date range that the user inputs.
this is what i have and it gives me #Error

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] = Between " & [Beginning Date] & " and " & [End Date])

 

[Ofer Cohen]

1. Adding criteria for date need # before and after the value
2. There is no need for the equal sign before the between

Try
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date] & "#")

3. If the 4a and 4b are numeric, you need to remove the single quote. Try

=DCount("[4a]","DATABASE","[4a]=1 And [4b]=1 And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date] & "#")

4. If still no luck, then where the [Beginning Date] and [End Date] are
comming from?

 

[Allen Browne]

Terri, there are lots of things that could go wrong here.

Let's verify the assumptions:
a) This goes in the Control Source of a text box on a form.
b) The same form has text boxes named:
Beginning Date
End Date
c) If you open the table in design view, 4a, 4b, and Hospital are all Text
fields, an auditdate is a Date/TIme field.
d) All these fields exist in a table named DATABASE.

If that's all correct, you must:
- delimit the literal dates with #
- handle the possiblity that the text boxes could be null (blank.)
- handle the possiblity that auditdate contains a time as well as a date.
- ensure the user's dates get into the string in the format JET expects.

So, try:

=DCount("*","[DATABASE]", "([4a]='1') AND ([4b]='1')
AND ([Hospital] = 'sngh') AND ([auditdate] >= " &
Format(Nz([Beginning Date],#1/1/1900#), "\#mm\/dd\/yyyy\#") &
") AND ([auditdate] < " &
Format(Nz([End Date],#1/1/2299#)+1, "\#mm\/dd\/yyyy\#") & ")"

Other comments:
1. DATABASE is a reserved word. If that really is the table name, you may
need to change it. For a list of the names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

2. Brackets are optional, but helpful.

3. If you really have fields named 4a, 4b, etc, you probably don't have a
relational structure here. You might want to look at this example of how to
do surveys in Access:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

4. Make sure the Name property of this text box is not the same as the name
of any fields on your form.

5. If it still fails, open the Immediate Window (Ctrl+G) and try a similar
expression there, building it up a little at a time until you get it right.

 

[terri]

sorry Allen...that doesn't work.

and Ofer that doesn't work either.

Yes it is in the textbox on a report.
The audit date is in the table called database, as are answers for 4a and 4b.
which are all text fields, as is hospital.
this report is based on a qry which asks for the audit date range only.
all are required fields and the date/time is date only.
and this textbox is in the report header.

i have another report based on a qry that asks for the hospital and the month.
so this works for that:
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = ' " &
[Hospital] & " ' And [ReviewMonth] = ' " & [reviewmonth] & " ' ")


t.






--
terri

1. Adding criteria for date need # before and after the value
2. There is no need for the equal sign before the between

Try
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date] & "#")

3. If the 4a and 4b are numeric, you need to remove the single quote. Try

=DCount("[4a]","DATABASE","[4a]=1 And [4b]=1 And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date] & "#")

4. If still no luck, then where the [Beginning Date] and [End Date] are
comming from?

--
Good Luck
BS"D

I'm so confused
what did i do wrong here?

I am trying to count questions that have been answered for a audit's date
range.

I want to know the total of questions that have question 4a=1 as its answer
and where 4b has '2' as its answer, from a certain hospital and where the
audit date is a date range that the user inputs.
this is what i have and it gives me #Error

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] = Between " & [Beginning Date] & " and " & [End Date])

 

[terri]

gosh but it DOES work Ofer!!
i had not put the last # in there when i did it and i see it now !!
thank you , thank you, thank you !!!
--
terri

sorry Allen...that doesn't work.

and Ofer that doesn't work either.

Yes it is in the textbox on a report.
The audit date is in the table called database, as are answers for 4a and 4b.
which are all text fields, as is hospital.
this report is based on a qry which asks for the audit date range only.
all are required fields and the date/time is date only.
and this textbox is in the report header.

i have another report based on a qry that asks for the hospital and the month.
so this works for that:
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = ' " &
[Hospital] & " ' And [ReviewMonth] = ' " & [reviewmonth] & " ' ")


t.






--
terri

1. Adding criteria for date need # before and after the value
2. There is no need for the equal sign before the between

Try
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date] & "#")

3. If the 4a and 4b are numeric, you need to remove the single quote. Try

=DCount("[4a]","DATABASE","[4a]=1 And [4b]=1 And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date] & "#")

4. If still no luck, then where the [Beginning Date] and [End Date] are
comming from?

--
Good Luck
BS"D

I'm so confused
what did i do wrong here?

I am trying to count questions that have been answered for a audit's date
range.

I want to know the total of questions that have question 4a=1 as its answer
and where 4b has '2' as its answer, from a certain hospital and where the
audit date is a date range that the user inputs.
this is what i have and it gives me #Error

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] = Between " & [Beginning Date] & " and " & [End Date])

 

[Douglas J. Steele]

Just a comment. In case there's a chance that one or more of your users
might have their regional settings set so that date is displayed as
dd/mm/yyyy, you'd be better off using

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] Between " & Format([Beginning Date], "\#yyyy\-mm\-dd\#") & "
and " & Format([End Date], "\#yyyy\-mm\-dd\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

gosh but it DOES work Ofer!!
i had not put the last # in there when i did it and i see it now !!
thank you , thank you, thank you !!!
--
terri

sorry Allen...that doesn't work.

and Ofer that doesn't work either.

Yes it is in the textbox on a report.
The audit date is in the table called database, as are answers for 4a and
4b.
which are all text fields, as is hospital.
this report is based on a qry which asks for the audit date range only.
all are required fields and the date/time is date only.
and this textbox is in the report header.

i have another report based on a qry that asks for the hospital and the
month.
so this works for that:
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = ' " &
[Hospital] & " ' And [ReviewMonth] = ' " & [reviewmonth] & " ' ")


t.






--
terri

1. Adding criteria for date need # before and after the value
2. There is no need for the equal sign before the between

Try
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] =
'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date]
& "#")

3. If the 4a and 4b are numeric, you need to remove the single quote.
Try

=DCount("[4a]","DATABASE","[4a]=1 And [4b]=1 And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date]
& "#")

4. If still no luck, then where the [Beginning Date] and [End Date]
are
comming from?

--
Good Luck
BS"D


:

I'm so confused
what did i do wrong here?

I am trying to count questions that have been answered for a audit's
date
range.

I want to know the total of questions that have question 4a=1 as its
answer
and where 4b has '2' as its answer, from a certain hospital and where
the
audit date is a date range that the user inputs.
this is what i have and it gives me #Error

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] =
'sngh'
And [auditdate] = Between " & [Beginning Date] & " and " & [End
Date])

 

[Bob Quintal]

I'm so confused
what did i do wrong here?

I am trying to count questions that have been answered for a
audit's date range.

I want to know the total of questions that have question 4a=1 as
its answer and where 4b has '2' as its answer, from a certain
hospital and where the audit date is a date range that the user
inputs. this is what i have and it gives me #Error

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] =
'sngh' And [auditdate] = Between " & [Beginning Date] & " and " &
[End Date])

And [auditdate] = Between has an equals sign that's confusing
Access.
And [auditdate] Between x And y is the correct way to get your
results.

 

[terri]

thanx Douglas.....i have noted that
--
terri

Just a comment. In case there's a chance that one or more of your users
might have their regional settings set so that date is displayed as
dd/mm/yyyy, you'd be better off using

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = 'sngh'
And [auditdate] Between " & Format([Beginning Date], "\#yyyy\-mm\-dd\#") & "
and " & Format([End Date], "\#yyyy\-mm\-dd\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

gosh but it DOES work Ofer!!
i had not put the last # in there when i did it and i see it now !!
thank you , thank you, thank you !!!
--
terri

sorry Allen...that doesn't work.

and Ofer that doesn't work either.

Yes it is in the textbox on a report.
The audit date is in the table called database, as are answers for 4a and
4b.
which are all text fields, as is hospital.
this report is based on a qry which asks for the audit date range only.
all are required fields and the date/time is date only.
and this textbox is in the report header.

i have another report based on a qry that asks for the hospital and the
month.
so this works for that:
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] = ' " &
[Hospital] & " ' And [ReviewMonth] = ' " & [reviewmonth] & " ' ")


t.






--
terri


:

1. Adding criteria for date need # before and after the value
2. There is no need for the equal sign before the between

Try
=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] =
'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date]
& "#")

3. If the 4a and 4b are numeric, you need to remove the single quote.
Try

=DCount("[4a]","DATABASE","[4a]=1 And [4b]=1 And [Hospital] = 'sngh'
And [auditdate] Between #" & [Beginning Date] & "# and #" & [End Date]
& "#")

4. If still no luck, then where the [Beginning Date] and [End Date]
are
comming from?

--
Good Luck
BS"D


:

I'm so confused
what did i do wrong here?

I am trying to count questions that have been answered for a audit's
date
range.

I want to know the total of questions that have question 4a=1 as its
answer
and where 4b has '2' as its answer, from a certain hospital and where
the
audit date is a date range that the user inputs.
this is what i have and it gives me #Error

=DCount("[4a]","DATABASE","[4a]='1' and [4b]='1' And [Hospital] =
'sngh'
And [auditdate] = Between " & [Beginning Date] & " and " & [End
Date])