Problem with Domain Aggregate Functions (VBA) (1 Viewer)

Teggun

Registered User.
Local time
Today, 21:53
Joined
Aug 30, 2019
Messages
33
Hi, guys, and thanks for reading my post. First of all I want to say thet I've only been using VBA and Access for 1 month so my knowledge about programming is low. I've been checking several tutorials and threads on the net to get this solved, but still I could not find a solution and my headache is big enough to try to ask for help from experiencied users.

Basically my main problem is that I can't fully understand how to make work the criteria in functions (VBA), specially in the WHERE clause. :banghead:

In my particular example, what I'm trying to do is to check if it exists a particular record in a table and if so, to execute an action.

Code:
Sub Test()

Dim strIDM As String
Dim cGreen As Long
Dim cGray As Long
strIDM = 101
cGreen = RGB(157, 187, 97)
cGray = RGB(165, 165, 165)

If (DCount("[IDMaq_FK]", "tblBobinas", "[Status]= 'On production' AND [IDMaq_FK]= & strIDM") > 0 Then

Me.box101.BackColor = cGreen
Else
Me.box101.BackColor = cGray
End If

End Sub

This is the code I've been using so far and even without fully understanding when and where to use ", &, '... I've managed to make it work with simple expressions but when I try to add multiple criteria on them, several errors show up, even after trying with all the possible combinations that came up to my mind.

I apologize in advance if this thread has been exposed somewhere else, I could not find it. Also if there is any post where this kind of expressions is explained in detail I would like to check it out so I can finally understand how to build this by myself and when to use this different operators.

Thanks a lot!
 

isladogs

MVP / VIP
Local time
Today, 20:53
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF.
Your posts were moderated. This can happen when new users add code or links.
I've deleted the duplicate post

I'll return and answer question later if no one else does first. Battery on tablet about to 'die'
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:53
Joined
Feb 19, 2013
Messages
16,553
your code will be generating an error as you write it because your brackets don't match - remove the one before your dcount.

Also it is always worthwhile building your string outside of where it is being used so you can check it is correct

try

debug.print "[Status]= 'On production' AND [IDMaq_FK]=" & strIDM
if DCount("[IDMaq_FK]", "tblBobinas", "[Status]= 'On production' AND [IDMaq_FK]=" & strIDM)>0
 

isladogs

MVP / VIP
Local time
Today, 20:53
Joined
Jan 14, 2017
Messages
18,186
Assuming IDMaq_FK is a text field despite specifying a number value 101 then use text delimiters

Code:
If DCount("[IDMaq_FK]", "tblBobinas", "[Status]= 'On production' AND [IDMaq_FK]= '" & strIDM & "'") > 0 Then

However if IDMaq_FK is a number field then

Code:
Dim intIDM As Integer
intIDM = 101
.....
If DCount("[IDMaq_FK]", "tblBobinas", "[Status]= 'On production' AND [IDMaq_FK]= " & intIDM) > 0 Then
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:53
Joined
May 7, 2009
Messages
19,169
you missed 1 closing bracket at the end.

tips. don't dcount() on a field which is already on the criteria. just use Dcount("*",..,..) or DCount("1", .., ..).

Dcount or Count (sql), on field will always test if the field is null or not null.
if null it will ignore it, not null include it.
using Dcount("*",,) doesn't do any checking becoz it's not a field.
 

Teggun

Registered User.
Local time
Today, 21:53
Joined
Aug 30, 2019
Messages
33
Thanks a lot guys for your quick answers, I did manage to solve this particular expression, however some of the strings I need to use contain text as well and in that case it is not working, not sure why, because I'm treating "strE01" exact the same as "str101".

Code:
Private Sub Form_Current()

Dim cGreen As Long
Dim cGray As Long
Dim str101 As String
Dim strE01 As String

str101 = 101
strE01 = "E01"
cGreen = RGB(157, 187, 97)
cGray = RGB(165, 165, 165)

    If DCount("[IDMaq_FK]", "tblBobinas", "[IDMaq_FK] = " & str101 & " AND [Status] = 'On production'") > 0 Then
        Me.box101.BackColor = cGray
    Else
        Me.box101.BackColor = cGreen
    End If

    If DCount("[IDMaq_FK]", "tblBobinas", "[IDMaq_FK] = " & strE01 & " AND [Status] = 'On production'") > 0 Then
        Me.boxE01.BackColor = cGray
    Else
        Me.boxE01.BackColor = cGreen
    End If
    
End Sub

So first DCOUNT expression working perfectly, but second one popping up an error related to the string value.

Thanks again for your time.
 

isladogs

MVP / VIP
Local time
Today, 20:53
Joined
Jan 14, 2017
Messages
18,186
The answer is in the earlier posts which you haven't taken into account.
101 is a number not a string! "101" is a string
 

Teggun

Registered User.
Local time
Today, 21:53
Joined
Aug 30, 2019
Messages
33
The answer is in the earlier posts which you haven't taken into account.
101 is a number not a string! "101" is a string

Indeed, sorry for not paying attention. I could sort it out thanks to you guys.

Appreciate your time, have a nice day :)
 

Users who are viewing this thread

Top Bottom