Solved

Access DCount() Error Woes

Posted on 2011-09-21
22
457 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now