Access DCount() Error Woes

The following code is taken from a Private Sub where the record source is the tEvents table (fields designated e_). The purpose is to ascertain how many cancelled booking are on the tBookings table (fields designated b_) that match the EventID.

Dim lngNoCanx As Long 'variable to store the DCount result
Dim strStatus As String 'variable to store 'CANX' that is in the b_Status (text) field
                                     '(if a booking is not CANX then the field contains 'OK'
Dim lngEventID As Long 'variable to store the Event ID (long integer) field

lngEventID = Me.e_EventID 'get the e_EventID
strStatus = "CANX" 'store the string sought in the match

lngNoCanx = DCount("*", "tBookings", "b_Status=" & strStatus)

'I get Run-time error '2001' You cancelled the previous operation

'Actually what I want to do is combine the following together
'(and therefore achieve what I set out to do at the top of this page).

lngNoCanx_pt1 = DCount("*", "tBookings", "b_Event=" & lngEventID)
lngNoCanx_pt2 = DCount("*", "tBookings", "b_Status=" & strStatus)

'into something like

lngNoCanx = DCount("*", "tBookings", "("b_Event=" & lngEventID) And ("b_Status"= & strStatus)) 'but I can't get the syntax right!

BTW I use DCount to count the matches in other fields in the same table and they work - e.g. the following all work:

lngNoBookings = DCount("*", "tBookings", "b_Full=True And b_Event=" & lngEventID)
lngNoConfirms = DCount("*", "tBookings", "b_Confirmed=True And b_Event=" & lngEventID)
lngReserved = DCount("*", "tBookings", "b_Reserved=True And b_Event=" & lngEventID)

MikeDTEAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
For the single field, take another look at my very first post.  Text needs quote delimiters.  Ie: the Chr(34) in the code...
0
 
mbizupCommented:
Text/strings need quotes as delimiters:

lngNoCanx = DCount("*", "tBookings", "b_Status=" & chr(34) strStatus & chr(34))

0
 
mbizupCommented:
Again:

lngNoCanx = DCount("*", "tBookings", "("b_Event=" & lngEventID) And ("b_Status"= & chr(34) & strStatus & chr(34)))


Quotes to delimit text; numbers don't need delimiters
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mbizupCommented:
Trying this again:

lngNoCanx = DCount("*", "tBookings", "("b_Event=" & lngEventID & ") And (b_Status"= & chr(34) & strStatus & chr(34) & ")")

0
 
mbizupCommented:
Sorry that last syntax is still off.  This should do it:

lngNoCanx = DCount("*", "tBookings", "b_Event=" & lngEventID & " And b_Status = " & Chr(34) & strStatus & Chr(34) )
0
 
MikeDTEAuthor Commented:
Hi mbizup

So code line is:

lngNoCanx = DCount("*", "tBookings", "b_Status1=" & " strStatus ")

This fails with "Run-time error '2001': You cancelled the previous operation

Sorry
0
 
MikeDTEAuthor Commented:
Hi mbizup

Your last code worked!!  I guess the CHR(34) is needed in the code otherwise it fails the syntax check ... Ah just got your last message.  The points are yours my friend.  Thank you!
0
 
MikeDTEAuthor Commented:
Brilliant and quick response - glad I'm a member.  This has saved much frustration and given me a fix for other similar issues
0
 
mbizupCommented:
<< I guess the CHR(34) is needed in the code otherwise it fails the syntax check  >>

In explanation, CHR(34) is the character code for double quotes.  So if your status (strStatus) is CLOSED and EventID is 1, your criteria in the following:

lngNoCanx = DCount("*", "tBookings", "b_Event=" & lngEventID & " And b_Status = " & Chr(34) & strStatus & Chr(34) ) 

Open in new window

Will work out to be this

b_Event=1 And b_Status = "CLOSED" 

Open in new window

An alernative syntax is to use single quote delimiters (you can see the single quotes around strStatus if you look really closely):

lngNoCanx = DCount("*", "tBookings", "b_Event=" & lngEventID & " And b_Status = '" & strStatus  & "'" ) 

Open in new window

I don't like the single quote approach as much as CHR(34), however.  While it would most likely work for any value you might have for a Status field, the same syntax could be problematic in something like a Name field where Irish names such as O'Brien and O'Malley would cause errors (the apostrophes would be mis-interpreted by the code as end-quotes).  You don't run into this problem with CHR(34).

Glad that worked out.

 
0
 
MikeDTEAuthor Commented:
A related question

I have 5 other similar bits of code that would benefit from adding the code to only includ an "OK" booking status.  I tried to apply the lesson learned in teh first of these but I got an incorrect result.  Can I be cheeky and ask to to rewrite teh lines to include a check for b_Status="OK" please.  It would mean a few hours saved which will make up for some of teh lost time I have suffered already.  Thanks in advance.

'Build Confirmed total
'condition: a booking with payment made (be it deposit or full payment) and b_Status = “OK”
lngNoConfirms = DCount("*", "tBookings", "b_Confirmed=True And b_Event=" & lngEventID)
     
'Build Observers total
'count bookings summing b_Observer and b_Status = “OK”
lngNoObservers = DSum("b_Observer", "tBookings", "b_Event=" & lngEventID)
       
'Build Participants total
'condition: a booking with b_Full = True and b_Status = “OK”
lngNoParticipants = DCount("*", "tBookings", "b_Full=True And b_Event=" & lngEventID)
   
'Build Partners total
'count bookings summing b_People less Participants and observers and b_Status = “OK”
lngNoPartners = DSum("b_People", "tBookings", "b_Event=" & lngEventID)
e_NoPartners = lngNoPartners - (lngNoParticipants + lngNoObservers)
   
'Build Reserves total
'condition: a "booking" where b_Reserved = True and b_Status = “OK”
lngReserved = DCount("*", "tBookings", "b_Reserved=True And b_Event=" & lngEventID)
       
0
 
mbizupCommented:
Okay - if you are embedding hardcoded text (assuming it contains no apostrophes) the simplest way is to use that alternative (single-quote) syntax I gave you:

lngNoConfirms = DCount("*", "tBookings", "b_Confirmed=True And b_Event=" & lngEventID & " and b_status = 'OK'")

Open in new window

Is that what you are looking for?

0
 
mbizupCommented:
Alternatively, using CHR(34) - uglier, but equally effective:

lngNoConfirms = DCount("*", "tBookings", "b_Confirmed=True And b_Event=" & lngEventID & " and b_status = " & CHR(34)  & "OK" & CHR(34)) 

Open in new window

0
 
MikeDTEAuthor Commented:
Hi mbizup

No not exacly.  I store the text into a variable strStatus.

So in the first of the code snips I would change

lngNoConfirms = DCount("*", "tBookings", "b_Confirmed=True And b_Event=" & lngEventID)

(which gives me a correct result of 2 - i.e. there are indeed 2 uncancelled bookings on the chosen event)

to the following :

lngNoConfirms = DCount("*", "tBookings", "b_Confirmed=True And b_Event=" & lngEventID & " And b_Status=" & Chr(34) & strStatus & Chr(34) )

But when I do I get a 0 result


0
 
MikeDTEAuthor Commented:
Hi (again) mbizup

Your latest post suggest I use the string "OK" rather than the variable "strStatus" but therwise the code is the sam and results in a 0

Just one thing - the field value in the table is "CANX" and "OK  " (i.e. its a Text field of 4 characters) - does the trailing spaces make a difference?
0
 
MikeDTEAuthor Commented:
OK hand on I've go it!

In my rush to get the code in place I have forgotten to reinitialise strStatus

strStatus = CANX when I'm looking for the number of cancelled bookings.  When in the other DCount and DSum calls I'm looking for strStatus ="OK"

I'll correct this and test.  If I have any more issues with this I'll post, otherwise please accept my apologies for mucking you about and thanks once more.
0
 
mbizupCommented:
That means that there are no records that meet the specified criteria.

< I store the text into a variable strStatus.>

How are you actually setting the value for strStatus?

And is the resuting strStatus a) what you expect it to be and b) present in the b_Status field of your table confirmed (b_confirmed = true) for that eventID?

Try adding a message box to double check strStatus immediately before your DCount:

msgbox "*" & strStatus  & "*"  '<-- those *'s are a trick to make nulls and leading/trailing spaces easy to recognize if present

What are the messagebox results?
0
 
mbizupCommented:
We cross-posted... let me know how it goes.
0
 
mbizupCommented:
<< does the trailing spaces make a difference? >>

Very likely...

You can use the TRIM() function to get rid of leading/trailing spaces.
0
 
mbizupCommented:
Actually - ignore that last post...

UNLESS you have *deliberately* inserted traling spaces (which is somewhat difficult to do - you have to do this through an INSERT query, not through regular data entry), they will not be in your table even though the field length is 4 characters.  That field length is a maximum, but if your inserted text is less than that,  the actual text does not get 'padded' with spaces (it is exactly as you have inserted it)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not sure if you'll find this helpful at this point or not, but you may want to spend a few minutes skimming the article I wrote:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

  Which talks a lot about how arguments are passed, especially when a text value is involved.  mbizup has already covered most of it, but you may pick up a pointer or two.

Jim.
0
 
MikeDTEAuthor Commented:
Hi mbizup

Everything now working perfectly and so fast.  Definately better than opening a new recordset and building queries!

It worked well with strStatus="OK" so no need to use TRIM().

I read your profile - Virginia Tech - I thought I knew the name and Googled it - 2007 was a bad year!

I guess it's one end of the day or the other for you so thanks for the help from a sunny midday in the West of England.
0
 
mbizupCommented:
Glad that worked out for you!

<2007 was a bad year!>

I couldn't agree more.  That was such a sad, shocking moment in the school's history.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.