Solved

Access DCount() Error Woes

Posted on 2011-09-21
22
467 Views
Last Modified: 2013-12-25
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)

0
Comment
Question by:MikeDTE
  • 13
  • 8
22 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36572401
Text/strings need quotes as delimiters:

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

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36572407
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36572413
Trying this again:

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

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 61

Expert Comment

by:mbizup
ID: 36572432
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
 

Author Comment

by:MikeDTE
ID: 36572466
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36572511
For the single field, take another look at my very first post.  Text needs quote delimiters.  Ie: the Chr(34) in the code...
0
 

Author Comment

by:MikeDTE
ID: 36572519
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
 

Author Closing Comment

by:MikeDTE
ID: 36572521
Brilliant and quick response - glad I'm a member.  This has saved much frustration and given me a fix for other similar issues
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36572648
<< 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
 

Author Comment

by:MikeDTE
ID: 36573115
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36573162
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36573202
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
 

Author Comment

by:MikeDTE
ID: 36573214
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
 

Author Comment

by:MikeDTE
ID: 36573236
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
 

Author Comment

by:MikeDTE
ID: 36573258
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36573296
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36573301
We cross-posted... let me know how it goes.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36573322
<< does the trailing spaces make a difference? >>

Very likely...

You can use the TRIM() function to get rid of leading/trailing spaces.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36573352
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
 
LVL 57
ID: 36573380
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
 

Author Comment

by:MikeDTE
ID: 36573409
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36573439
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question