DCount() Confusion

[Ahmed Eissa]

I am having the same problem... I am using Access 2003 SP3 and in the table, the records are:

no    BordIndex    name     signoff        comments
------------------------------------------------------
1         71       Prod1       0           -
2         71       Prod2       1           Bad
3         71       Prod3       0           -
4         71       Prod4       1           Expired
5         71       Prod5       1           Broken
6         71       Prod6       1           Bad
 

please note that this is a part from a big table that holds another "BordIndex" numbers for other Bords. I used this code to store the numbers of none signed off products which belongs to the Bord number 71:

N = DCount("SignOff", "Prod", "BordIndex = 71 and SignOff =1")

and insted of returning 4, it just return 6.

Please help me with this.

Thanks.

** Edit ** This was split from a related thread (Why is DCount expression returning wrong values?).

[NeoPa]

Ahmed, I can see that you tried hard to do things correctly, so I will simply say we don't allow new questions in existing threads. I have moved it to it's own thread for you and added links between the two threads in case it helps.

[NeoPa]

I can't easily see why this wouldn't work. Certainly it's untidy, but probably would work in normal circumstances.
Tidying the code to be explicit :

N = DCount("*", "Prod", "([BordIndex] = 71) and ([SignOff] = 1)")

I expect that one of the fields is mishandled though. What datatypes are the fields [BordIndex] and [SignOff]? If they're not both numeric then that would explain the problem.

[ADezii]

DCount() should provide the correct answer in this case:

1. Are the Data Types of the [BordIndex], and [signoff] Fields NUMERIC?
2. Try a Variation on DCount() and see what happens:
   

   N = DCount("*", "Prod", "BordIndex = 71 and SignOff =1")

[ADezii]

Sorry NeoPa, always stepping on your toes! Is this because your feet are so big? (LOL).

[Ahmed Eissa]

Hi NeoPa,

Thnaks for your solution, it works, but why it was not working before? I mean if I write the same code, which was cosing the problem in the "Immediate" box in VBA editor, it gives 4 which is currect, rather than 6 which is wrong, but only in the run-time it return 6 rather than 4. Can you explaine to me what the reson?

Sorry for putting this question in an existing thread.

Also, many many thanks to all of you, who tried to help me by writing them suggestions.

Thanks.

Ahmed

[Ahmed Eissa]

Hi ADezii,

I tried it, but it still gives 6. Both are Integers.

Thanks.

Ahmed

[Ahmed Eissa]

Hi All,

It back again... I don't know why!

It back giving me 6 rather than 4.

I think it's a bug in MS Office Access 2003.

Thanks.


Ahmed

[NeoPa]

Right! This is clearly confusing, and we have some information but could do with some more.

Firstly, as I said before, I can't see why your earlier code wouldn't have worked. It's not tidy - but nor is it wrong as far as I can see. This is particularly true as you've now confirmed that both fields are of type integer.

Next, to move forward with this we need you to paste exactly the code you're using (just the DCount() line should be adequate), then redo the data grab and confirm for us that the results still exactly match the data you shared with us in your OP (By the way - not many posters are clever enough to include the data as you did - Smart move). Try this SQL to see exactly what results you get. If they're exactly the same as the data you already posted just confirm that, but if they're different then please post the new results for us to work with :

SELECT   [No]
       , [BordIndex]
       , [Name]
       , [SignOff]
       , [Comments]
FROM     [Prod]
WHERE    ([BordIndex] = 71)
ORDER BY [No]

Obviously, leave out references to any fields which aren't actually in the table if there are any.

Lastly, it's important to say under exactly which circumstances the results are returned correctly and under which they are not.

ADezii:
Sorry NeoPa, always stepping on your toes! Is this because your feet are so big? (LOL).

That's not a polite way to talk to a Sasquatch!!

[ADezii]

It's not a Bug in Access 2003, since that is what I tested it on, and the result returned was 4.

[Ahmed Eissa]

Hi NeoPa,

Thanks for trying helping me, the code is:

Dim Cn As New Adodb.Connection
Dim Rs As New Adodb.Recordset
Dim SQL As String, LNo as Long, N as Integer
 
 
With Cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString = Application.CurrentProject.FullName 
  .Open
End With
 
SQL = "Select * from Bords"
 
With Rs
  .Open SQL, Cn, adOpenDynamic, adLockOptimistic
  .MoveFirst
  Do While Not .EOF
    LNo = .Fields("BordIndex")
    N = DCount("SignOff", "Prod", "([BordIndex] = " & LNo & ") and ([SignOff] =1)") 
 
..............
 
 

As you see, the value of N in the design time (if we run the code in "Immediate" window) will be 4, but during the execution of the code (Run time) it's return 6.


Thanks.

[NeoPa]

Ahmed, much time will be wasted if I need to point your attention back to a previous post. Please reread post #9 and respond to all of it as well as you can. Thank you for the code, but that was only one part of what I said was needed.

As for the code you've posted, I see no problem with it. I would use "*" for the first parameter (as suggested), but there are no scenarios where you would not get the same result when using "SignOff" instead.

You say the Runtime returns 6 and the code, when run in the immediate window, returns 4, but what if the code is run in the MDB (or ACCDB) form but without the IDE (within which is the Immediate Pane) even open?

[Ahmed Eissa]

Hi NeoPa,

Sorry for not reading your previous reply compleatley.

Yes, I can confirm that the SQL query you provide is giving the same result I provide.

Basicly, I am using MS Access 2003, with VBA code, and as I said, the value of N in the design time (if we run the code in "Immediate" window) will be 4, but during the execution of the code (Run time) it's return 6. In other words, if I run a query to get the number of non-signed off products, it return 4 (in Queries tab -> New Query), and if I typed in the Immediate window the DCount line, it also return 4. But only when the VBA code is being executed and the result of the DCount is being stored in the variable N, it will give 6.

I am using Windows XP Pro SP3, with MS Access 2003 SP3 linked on a domain controller network.

I've wrote this code to replace the DCount, and it works, but (As you know) it will take a long time in a large number of records.

Private Function CalcErrBdx(ByVal BordIndex As Long) As Long
    Dim GTCN As New ADODB.Connection, GTRs As New ADODB.Recordset, GTSQL As String, Rees As Variant
    With GTCN
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = Application.CurrentProject.FullName
        .Open
    End With
 
    Rees = 0
    GTSQL = "select * from prod" 
 
    With GTRs
        .Open GTSQL, GTCN, adOpenDynamic, adLockOptimistic
        If .EOF = True And .BOF = True Then
            Rees = 0
        Else
            .MoveFirst
            DoEvents
            Do While Not .EOF
                If .Fields("BordIndex") = BordIndex Then
                    If .Fields("SignOff") = 1 Then Rees = Rees + 1
                End If
                .MoveNext
            Loop
        End If
        .Close
    End With
 
    Set GTRs = Nothing
    GTCN.Close
    Set GTCN = Nothing
    CalcErrBdx = Rees
End Function
 

By the way, there are 2 tables, the first one is PROD and the second one is BORDS, and [BordIndex] is the brimary key for the BORDS table, linked with a foregn key [BordIndex] in PROD table. the construction code for both tables are:

Create Table Bords
(
BordIndex     Integer  Primary Key,
BordLocation  Varchar(20),
Manager    Varchar(20)
);
 
Create Table Prod
(
No     Integer  Primary Key,
BordIndex  Integer  Referances Bords(BordIndex),
Name    Varchar(20),
SignOff   Integer,
Comments  Varchar(100)
);
 
 

and the data in the tables are:

Bords Table:
BordIndex    BordLocation     Manager
-------------------------------------- 
70           North East       Bob
71           South West       James 
72           North West       Alan
 
 
Prod Table:
no    BordIndex    name     signoff        comments 
------------------------------------------------------ 
1         71       Prod1       0           - 
2         71       Prod2       1           Bad 
3         71       Prod3       0           - 
4         71       Prod4       1           Expired 
5         71       Prod5       1           Broken 
6         71       Prod6       1           Bad 
 

Thanks and sorry if I didn't reply with compleatley what you want in reply #9.

Thanks.

[Ahmed Eissa]

Hi ADezii,

Please see:http://support.microsoft.com/kb/945674

[NeoPa]

Ahmed, I can see that you're doing your best to answer, so I'm happy with that. Unfortunately there are still some things I need from you. I will be as clear as I can :

1. Runtime. I think maybe you are not using this word with it's normal meaning. Maybe if I explain that you will understand better and be able to answer.
   
   The Runtime is a version of the Access application itself which doesn't allow viewing or updating of the database design at all (See Understanding the Microsoft Access Runtime Engine).
   
   I think when you use the word Runtime you are talking about when the project runs at full speed without any debugging interference. Is that right?
2. One of the main things I wanted to see from post #9 is the results of the query whose SQL I posted. Without this I don't know what I'm working with data-wise.
   
   ** Edit **
   I've just seen your updated post. Is the data simply a repeat of what you posted earlier, or is it what I requested - an up-to-date run of that SQL (from post #9)? The data doesn't look much like the sort of real-life data you might find in a real table, but maybe that's simply because it's a test table with test data in it.

I'm sure if you can respond to these two points we can move forwards.

[Ahmed Eissa]

Hi All,

Thanks for your effort, I did submit this question in MSDN forum, and I got the answer.
http://social.msdn.microsoft.com/For...5-1c6daacecfbd

Thanks for all of you.

Ahmed Eissa

[NeoPa]

An interesting work-around Ahmed. Some good advice from what I saw, and clearly it helped you.

It's not an answer to the question you asked though (That's not a problem - I just wanted to make it clear for those coming along afterwards with a similar problem to the question you asked).

Good luck for the rest of your project.