Dcount with multiple criteria using AND between criteria

[Rx_]

Hats off to Jdraw's example. Solved my immediate problem, but would really like to know the code for the alternate statement.

Jdraw shows two examples to get the same result, I got the code to work one way, but I could not get the code to work using the AND between the last two criteria.

NewPadNumber is a proposed number - that is checked to determine if it has been used within th ID_Area.

Debug.Print  DCount("Pad_Number", "Wells_PAD_Name", "ID_Area & Pad_Number = " & Me.ID_Area & NewPadNumber) 
   '   returned   1   ' this one works great

Many Credits to Jdraw - see the last example with the AND between two criteria:
http://www.access-programmers.co.uk/forums/showthread.php?t=191885&highlight=dlookup+criteria

 

[MSAccessRookie]

Hats off to Jdraw's example. Solved my immediate problem, but would really like to know the code for the alternate statement.

Jdraw shows two examples to get the same result, I got the code to work one way, but I could not get the code to work using the AND between the last two criteria.

NewPadNumber is a proposed number - that is checked to determine if it has been used within th ID_Area.

Debug.Print  DCount("Pad_Number", "Wells_PAD_Name", "ID_Area & Pad_Number = " & Me.ID_Area & NewPadNumber) 
   '   returned   1   ' this one works great

Many Credits to Jdraw - see the last example with the AND between two criteria:
http://www.access-programmers.co.uk/forums/showthread.php?t=191885&highlight=dlookup+criteria

I believe that the statement might not evaluate the way you want it to when both ID_Area & NewPad_Number are both numbers. Try changing "ID_Area & Pad_Number" to "cStr(ID_Area) & cStr(NewPad_Number)" and see if that changes anything.

 

[vbaInet]

Also try:

DCount("*", "Wells_PAD_Name", "ID_Area = " & Me.ID_Area & " AND Pad_Number = " & NewPadNumber)

 

[Rx_]

Great point, but...here is my theory as to why it it will work OK... and why it might break either way

Function CheckAmperstand(X As Integer, Y As Integer) As Integer
Dim Answer As Integer
Answer = X & Y
CheckAmperstand = Answer
 ' x = 7   Y  = 9  returns 79
End Function

So, one column is a region that starts a pad 1,2,3,4 sequence for each region
Checking region 7, the test is that the next number in sequence "9" does not already exist.
The Dlookup brings back 79 - a unique number the same as converting to a string.

What you made me realize is (and thank you for that)

What if the Region 11 and Pad 9 return 119
And Region 1 and Pad 19 get returned?

While me thinks the convert to string is not a problem, the hashing of two small numbers has a much higher probability of a collision!

So the question just changed!!
I need to add a third criteria to put inbetween. Perhaps a column with a default value of 101.

Function CheckAmperstand(X As Integer, Y As Integer) As Long
Dim Answer As Long
Dim CollisionPreventer As Integer
CollisionPreventer = [B]101[/B]
Answer = X & CollisionPreventer & Y
CheckAmperstand = Answer
 ' x = 1   Y  = 19  returns 1[B]101[/B]19
End Function

This would prevent the collision and provide a unique number.

 

[vbaInet]

To be honest, I will just use an AND operator as was given because it does the job, it follows sql syntax and it's more readable. Having a "collision preventer" wouldn't make add value to the accuracy of the returned value. Why add more complexity to the computation?

 

[Rx_]

Given what they are doing, the collision prevention is probably necessary for this code -maintenance application. The code is used to determine if a combination of the column Region and column Pad exist or if a new combination can be created.
If Region 1 and Pad 11 exist - the result is 111
If Region 11 and Pad 1 try to get created, the function find 111 and inform the user that it already exist.

Will start testing and see how this might be avoided with out a third column.

 

[vbaInet]

The AND will still perform the same operation. It really doesn't need to be concatenated in the DCount because it wouldn't make a difference to the result.

 

[Rx_]

the coffee worked!
the AND instead of the Concant!
So sorry for being dense today LOL

 

[vbaInet]

Yay! The only time it will matter wether to concat or not is when you're comparing the value in just one field.

I've already had my dose of coffee today