AND Problem

R

Rod

I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
 
S

Shane S via AccessMonster.com

Hey Rod,

Is this how your code appears? or did you do a type-o cause in the DCount,
after the AND, looks like you meant to use NZ like on your first DCount,
before the AND but NZ isn't there.

Should be:
Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And Nz(DCount([CALL_RESULTS],
"tblCandidates",
[CALL_RESULTS] = "Message"), 0)

Shane
I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
Click to expand...
 
R

Rod

I still get the cryptic Run-time error '2001':
You canceled the previous operation.

Thoughts?

Shane S via AccessMonster.com said:
Hey Rod,

Is this how your code appears? or did you do a type-o cause in the DCount,
after the AND, looks like you meant to use NZ like on your first DCount,
before the AND but NZ isn't there.

Should be:
Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And Nz(DCount([CALL_RESULTS],
"tblCandidates",
[CALL_RESULTS] = "Message"), 0)

Shane
I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
Click to expand...
Click to expand...
 
R

Rod

Found it. I had a reference to the wrong record.

Shane S via AccessMonster.com said:
Hey Rod,

Is this how your code appears? or did you do a type-o cause in the DCount,
after the AND, looks like you meant to use NZ like on your first DCount,
before the AND but NZ isn't there.

Should be:
Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And Nz(DCount([CALL_RESULTS],
"tblCandidates",
[CALL_RESULTS] = "Message"), 0)

Shane
I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
Click to expand...
Click to expand...
 
D

David C. Holley

You obviously haven't learn English, or you'd be able to translate the
message. In English 'You canceled the previous operation' translates
roughly to one or more parameters in a domain function is incorrect -
most likely that a field name or domain name is misspell meaning that
Access cant field the target table and/or target fields.
I still get the cryptic Run-time error '2001':
You canceled the previous operation.

Thoughts?

:

Hey Rod,

Is this how your code appears? or did you do a type-o cause in the DCount,
after the AND, looks like you meant to use NZ like on your first DCount,
before the AND but NZ isn't there.

Should be:
Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And Nz(DCount([CALL_RESULTS],
"tblCandidates",
[CALL_RESULTS] = "Message"), 0)

Shane
I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

It is not the And statement. You don't need the nexted Dcounts (not even
sure that would work). I don't have a way to test this, so I can't guarantee
you wont have a syntax error. I am also concernted about the Format(Date...
Why do you need to format it? If it is an Access mdb table and the field is
a date type, then the second example should work:

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#") & " And [CALL_RESULTS] = 'Message'"),0)

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Date & " And [CALL_RESULTS] = 'Message'"),0)
 
D

David C. Holley

I would not recommend using nested DCounts() since each time you nest a
statement within another, you add a layer of complexity and thus
increase the possiblity of an error.
It is not the And statement. You don't need the nexted Dcounts (not even
sure that would work). I don't have a way to test this, so I can't guarantee
you wont have a syntax error. I am also concernted about the Format(Date...
Why do you need to format it? If it is an Access mdb table and the field is
a date type, then the second example should work:

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#") & " And [CALL_RESULTS] = 'Message'"),0)

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Date & " And [CALL_RESULTS] = 'Message'"),0)

:

I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
Click to expand...
Click to expand...
 
R

Rod

BAM! Right on the money first shot! Thanks for the constructive feedback!

Klatuu said:
It is not the And statement. You don't need the nexted Dcounts (not even
sure that would work). I don't have a way to test this, so I can't guarantee
you wont have a syntax error. I am also concernted about the Format(Date...
Why do you need to format it? If it is an Access mdb table and the field is
a date type, then the second example should work:

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#") & " And [CALL_RESULTS] = 'Message'"),0)

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Date & " And [CALL_RESULTS] = 'Message'"),0)

Rod said:
I can not seem to find the problem with this AND statement (everything before
the AND works fine):

Me.tbxMsgToday = Nz(DCount([CALLED_ON], "tblCandidates", "[CALLED_ON] = " &
Format(Date, "\#mm\/dd\/yyyy\#")) And DCount([CALL_RESULTS], "tblCandidates",
[CALL_RESULTS] = "Message"), 0)

The point of this is to count how many records for the current day have
"Message" as the CALL_RESULTS and store that value in tbxMsgToday.

I suspect the usage for the "AND" is incorrect, but I have looked at this
all day and can not find it (perhaps I have been looking at it too long).

Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DCount Difficulty 3
Simple Update Q Problem 1
Date Summary Report 4
How to add day to PWA 2016 date format? 0
Summary Report 2
Query for Daily, Next 7day Totals, Month-to-Date, and access Outlo 0
Simply DLookup Problem 2
Publisher is filling blank lines with data 2

Top