dcount syntax

[kaosyeti]

hey.. i have a control in a report that needs to count the records from a
table that match the parameters that the user selects when creating the
report. the report is created with formquerybuilder and the parameters are
department, upsource, and date. i can't get the syntax down for this dcount:

=DCount("*","tblcontactdata","[date] Between #" & [txtboxstartdate] & "# And
#" & [txtboxenddate] & "#" & "' AND '" & "'[upsource]='" & "'forms!
formquerybuilder!cboxupsource'" & "' AND '" & "'[department]='" & "'forms!
formquerybuilder!cboxdepartment'")

what have i got wrong?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Jana]

Try this instead:
=DCount("*","tblcontactdata","[date] Between #" & [txtboxstartdate] &
"# And #" & [txtboxenddate] & "# AND [upsource]='" &
forms!formquerybuilder!cboxupsource &
"' AND [department]='" forms!formquerybuilder!cboxdepartment & "'")

Some notes about figuring out the proper syntax:
1. Date values must be wrapped with # signs, which you did properly.
ex: [date] = #01/01/2005#
2. Text values must be wrapped with ' signs (single quotes).
ex: [upsource] = 'my source'
You had single quotes around the field names [upsource] and
[department] which were unnecessary, as well as around the AND after
the txtboxenddate.
3. Numerical values do NOT require any wrapping.
ex: [MyNumber] = 12
4. When referring to the value of a field on a form or report, do
not put single quotes around them, or the function will put the text
within the quotes rather than the value of the field.
ex: If the cboxupsource field has text in it:
"[upsource] = '" & forms!fromquerybuilder!cboxupsource & "'"
(note the placement of the single quotes and double quotes)

Hope that helps,
Jana

If the cboxupsource field as a number in it:
"[upsource] = " & forms!formquerybuilder!cboxupsource

If the cboxupsource field has a date in it:
"[upsource] = #" & forms!formquerybuilder!cboxupsource & "#"

[Jana]

sorry, got my name in the middle of that last part...

[kaosyeti]

Jana,
hrm. i almost have it working. this is my new expression:

=DCount("*","tblcontactdata","[date] Between #" & [txtboxstartdate] & "# And
#" & [txtboxenddate] & "# AND [upsource]=" & Chr(34) & Forms!formquerybuilder!
cboxupsource & Chr(34) & " AND [department]=" & Chr(34) & Forms!
formquerybuilder!cboxdepartment & Chr(34))

first, let me say that my use of chr(34) instead of the single-quote method
is not a reflection on you or your answer, but just lets me see more clearly.
this is something i saw in other posts while looking for answers to my
problem. to me, '" looks too close to "' for my taste.

anyhow, it seems to work fine. except that (and i forgot to mention this
earlier) these fields may be null (only the start and end dates are required).
the form that creates the report doesn't NEED the user to specify a
department. if no department is specified, the report runs on a combined
department total. how can i get this to work with an idea similar to:

iif(isnull(forms!formquerybuilder!cboxdepartment=t rue),"","[department]=" &
Chr(34) & Forms!formquerybuilder!cboxdepartment & Chr(34) &)

that way, if the field is null, the expression would look like this:

[date] between #10/1/05# and #11/30/05# and ""

the idea being that concatenating the where clause with "" will do nothing
but if the department IS specified, then it would look like this:

[date] between #10/1/05# and #11/30/05# and [department]="Electronics"

the same would go for upsource as well. i hope i explained everything so it
makes sense.

Jana wrote:

Try this instead:
=DCount("*","tblcontactdata","[date] Between #" & [txtboxstartdate] &
"# And #" & [txtboxenddate] & "# AND [upsource]='" &
forms!formquerybuilder!cboxupsource &
"' AND [department]='" forms!formquerybuilder!cboxdepartment & "'")

Some notes about figuring out the proper syntax:
1. Date values must be wrapped with # signs, which you did properly.
ex: [date] = #01/01/2005#
2. Text values must be wrapped with ' signs (single quotes).
ex: [upsource] = 'my source'
You had single quotes around the field names [upsource] and
[department] which were unnecessary, as well as around the AND after
the txtboxenddate.
3. Numerical values do NOT require any wrapping.
ex: [MyNumber] = 12
4. When referring to the value of a field on a form or report, do
not put single quotes around them, or the function will put the text
within the quotes rather than the value of the field.
ex: If the cboxupsource field has text in it:
"[upsource] = '" & forms!fromquerybuilder!cboxupsource & "'"
(note the placement of the single quotes and double quotes)

Hope that helps,
Jana

If the cboxupsource field as a number in it:
"[upsource] = " & forms!formquerybuilder!cboxupsource

If the cboxupsource field has a date in it:
"[upsource] = #" & forms!formquerybuilder!cboxupsource & "#"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[Jana]

No offense taken, it was a point worth making :)

Let's try this instead:

=DCount("*","tblcontactdata","[date] Between #" &
[txtboxstartdate] & "# And #" & [txtboxenddate] & "#" &
IIF(IsNull(Forms!formquerybuilder!cboxupsource) = True, "",
" AND [upsource]=" & Chr(34) & Forms!formquerybuilder!cboxupsource &
Chr(34)) &
IIF(IsNull(Forms!formquerybuilder!cboxdepartment) = True, "",
" AND [department]=" & Chr(34) & Forms!formquerybuilder!cboxdepartment
& Chr(34)))

Rather than worrying about whether the Where clause will like it if it
ends in And "", why not make that part of the conditional text? That's
what I've done here. Give it a shot, let me know how it works out.

Good luck,
Jana

[Jana]

Also, you might need to use Chr(39), which is a single quote, rather
than Chr(34) which is a double quote. I'm not sure if that will make a
difference, though.

Jana

[kaosyeti]

it worked perfectly. this was the final expression:

=DCount("*","tblcontactdata","[date] Between #" & [txtboxstartdate] & "# And
#" & [txtboxenddate] & "#" & IIf(IsNull(Forms!formquerybuilder!cboxupsource)
=True,""," AND [upsource]=" & Chr(34) & Forms!formquerybuilder!cboxupsource &
Chr(34)) & IIf(IsNull(Forms!formquerybuilder!cboxdepartment)= True,""," AND
[department]=" & Chr(34) & Forms!formquerybuilder!cboxdepartment & Chr(34)))

my only question is, and this is just for my own curiosity, why do people in
the know (you and others on this and other forums) use And, AND + & as if
they had different uses at different times. for example, you have:

....[txtboxstartdate] & "# And #" & [txtboxenddate]...

in one spot and:

....)=True,""," AND [upsource]=" & Chr...

in another. not to mention the ampersands all over the place. i mean, i
know we need them to get the expression right but why choose one type over
the other? or is there a difference? anyhow, thanks for all of your help.

Jana wrote:

Also, you might need to use Chr(39), which is a single quote, rather
than Chr(34) which is a double quote. I'm not sure if that will make a
difference, though.

Jana

--
Message posted via http://www.accessmonster.com

[Jana]

Here's the deal with AND vs. And vs. &:
There is no difference between AND and And, we just use the uppercase
convention within an SQL statement to indicate that it is a reserved
word in the SQL language. SQL is not case specific, so lowercase "and"
would work, too! So, a typical SQL would be written like this: "SELECT
* FROM tblMyTable WHERE fldMyField = "blah" AND fldMyOtherField = "blah
blah", even though "select * from tblmytable where fldmyfield = "blah"
and fldmyotherfield = "blah blah" would work just as easily. As you
can see, using the uppercase convention for reserved words makes it
easier to read an SQL statement.

The & is used for string concatenation. So, when you want to string
together a bunch of pieces of text into one string, you would do
something like this:
MyString = "This is " & "my string concatenation " & "example!"
This stores into the variable MyString "This is my string concatenation
example!" We had to use the & in your item because we were using the
IIF function to determine what text to add to your criteria for your
query because we were building the statement on the fly rather than
having criteria that was static. Here's another example that uses some
logic (in Visual Basic) to determine the contents of a string:
MyString = "This person is "
If MyAge < 21
MyString = MyString & "a minor."
Else
MyString = MyString & "over 21."
End If

Hope that takes some of the mystery out of it :)

Jana