DCount with column as a Variable (1 Viewer)

cybersardinha

New member
Local time
Today, 10:47
Joined
Mar 24, 2022
Messages
24
Hi,

I'm quite new to this VBA thing.

I have created this function so that I can count how many users have expired certifications. i need to check in diferent columns acording to the selection on a dropdown that declares the mFB variable.


Code:
Public Function CountGultig()
    mFB = "[FB_arbeitsschutz]"
    dtToday = Date
    expired = DateAdd("m", -12, Date)
    Debug.Print expired
    gultig = DCount("' & mFB & '", "database", "" & mFB & "  > #" & expired & "#")
    Debug.Print gultig
    ngultig = DCount("' & mFB & '", "database", "[FB_arbeitsschutz] < #" & expired & "#")
    Debug.Print ngultig
    Text18.Value = gultig
    Texto20.Value = ngultig
    
End Function


On this code the ngultig is calculated without a problem but the gultig doesn't work.

Any Ideias of what might cause this?

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:47
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

I am just guessing, but it sounds like you're having problems with your code because you may not have a properly normalized table structure. Can you show us your table setup?
 

cybersardinha

New member
Local time
Today, 10:47
Joined
Mar 24, 2022
Messages
24
Thank you.

I've attached the database with some sample data and the form I'm trying to make
 

Attachments

  • Database.accdb
    1.7 MB · Views: 241

Gasman

Enthusiastic Amateur
Local time
Today, 09:47
Joined
Sep 21, 2011
Messages
14,038
You have "" in front of mFB ?
 

Eugene-LS

Registered User.
Local time
Today, 12:47
Joined
Dec 7, 2018
Messages
481
Any Ideias of what might cause this?
Code:
Public Function CountGultig()
Dim mFB As String, sTblName As String
Dim dtToday As Date, dExpired As Date
Dim gultig As Long, ngultig As Long, sExpired$
  
    mFB = "FB_arbeitsschutz"
    sTblName = "database"    ' Are You sure in that NAME ???

    dtToday = Date
    dExpired = DateAdd("m", -12, Date)
    sExpired = Format$(dExpired, "\#mm\/dd\/yyyy\#")
  
    gultig = DCount("*", sTblName, mFB & " > " & sExpired) '
    Debug.Print gultig
    ngultig = DCount("*", sTblName, mFB & " < " & sExpired)
    Debug.Print ngultig
    Text18.Value = gultig
    Texto20.Value = ngultig

End Function
 
Last edited:

cybersardinha

New member
Local time
Today, 10:47
Joined
Mar 24, 2022
Messages
24
Code:
Public Function CountGultig()
Dim mFB As String, sTblName As String
Dim dtToday As Date, dExpired As Date
Dim gultig As Long, ngultig As Long, sExpired$
 
    mFB = "FB_arbeitsschutz"
    sTblName = "database"    ' Are You sure in that NAME ???

    dtToday = Date
    dExpired = DateAdd("m", -12, Date)
    sExpired = Format$(dExpired, "\#mm\/dd\/yyyy\#")
 
    gultig = DCount("*", sTblName, mFB & " > " & sExpired) '
    Debug.Print gultig
    ngultig = DCount("*", sTblName, mFB & " < " & sExpired)
    Debug.Print ngultig
    Text18.Value = gultig
    Texto20.Value = ngultig

End Function
It works :D

Thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:47
Joined
May 21, 2018
Messages
8,463
But "database" (your table name) is one of reserved words
I am suprised this worked
Code:
 sTblName = "database"    ' Are You sure in that NAME ???
would have thought it would fail without
sTblName = "[database]"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,970
This is a trivial task in a properly normalized database. You have a spreadsheet, not a table. Instead of having 65+ columns, the table has 0-65+ rows per person rather than 1. Then you can run ONE query that gives you 65 answers:) The other change you need to make is to rename the field named NAME. Access gave you a warning message when you created this table to tell you to NOT use NAME as the name of the column and you blew by the warning and ignored it. Not a good choice.

You will have probably three tables.
tblPerson
PersonID (autonumber PK)
LastName
etc.
tblCertName
CertID (autonumber PK)
CertName
etc.
tblPersonCert
PersonID (FK to tblPerson and PK field 1)
CertID (FK to tblCertName and PK field2)
ExpireDT

To get ExpiredCount
Select CertName, Count(*) as ExpiredCount
From YourTable
Where ExpDate < CalculatedExpDate
To get UnexpiredCount:
Select CertName, Count(*) as ExpiredCount
From YourTable
Where ExpDate > CalculatedExpDate

These queries ignore the expire date in the count. so one of the queries need an = to include it. i.e. <= or >= to put the expire date into one group or the other rather than ignoring it.

You can do the count in a single query also.
Select CertName, Sum(IIf ExpDate <= CalculateExpDate,1,0) as ExpiredCount, Sum(IIf ExpDate > CalculateExpDate,1,0) as ActiveCount
From YourTable

But first you have to normalize the table.

@Uncle Gizmo has a video and a sample database or code that helps you normalize your table.

I know it seems strange that the normalized version of the table has potentially so many rows per person but that is the way normalization works. It dramatically simplifies your process. Your current method requires that you run the procedure 67+ times. The normalized table lets you use either one or two queries depending on the method you like. It also means that if a new type of cert needs to be added, you don't need to do anything except add it to the table that defines the certs.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:47
Joined
Jul 9, 2003
Messages
16,243
@Uncle Gizmo has a video and a sample database or code that helps you normalize your table

Thanks for the mention Pat ...

The main problem for People moving from Excel to MS Access is that they assume it is just a more sophisticated version of Excel.

Although this is essentially true, the problem is MS Access is not Excel and that is where your problems begin...

I blogged about it on my website here:-

 

cybersardinha

New member
Local time
Today, 10:47
Joined
Mar 24, 2022
Messages
24
@Uncle Gizmo @Pat Hartman
You are absolutely right but unfortunately I'm a beginner in access.

I actually started with the structure you mentioned but I wasn't being able to populate the forms and I managed to do it with one single table.

But is definitely that seems the way to go.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,970
The sooner you normalize the data, the fewer objects you need to fix.

While you are restructuring, you might want to consider a way to group certifications to simplify the forms.
 

cybersardinha

New member
Local time
Today, 10:47
Joined
Mar 24, 2022
Messages
24
@Pat Hartman I'm trying to.

I actually have the certifications divided in 3 groups. I added on the tblCert a second column with the group

A question if you don´t mind. Using the organization you sugested I need a form with a dropdown selecting the PersonName from a query created
and then I have 1 Field for every Certification. I was able get the data for every certification.

But I wanted to be able to edit the data on this form and can't do it :S

screen.png
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:47
Joined
May 21, 2018
Messages
8,463
You need a main form based on tblperson.
Then a subform based on a query that joins tblPersonCert to tblCertName like you have it. On the subforms you can bring in
tblPersonCert.CertID
Date?
Other cert fields if you want.

in the subform tblPersonCert.CertID will be a combo box. It will show the CertName but will store CertID into tblPersoneCert. So you see and pick a certname but store the ID.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,970
Now that you have a proper many-many schema defined, I'll post a link to a sample m-m database so you can see how to build the forms. The application shows the data from each direction and it uses two different methods of displaying the "child" data. Using in one case a subform and in the other a popup. Both are correct but usually one will make more sense for a given application

Also, in the relationship window, you should define the relationships and enforce Referential Integrity.

 

cybersardinha

New member
Local time
Today, 10:47
Joined
Mar 24, 2022
Messages
24
You need a main form based on tblperson.
Then a subform based on a query that joins tblPersonCert to tblCertName like you have it. On the subforms you can bring in
tblPersonCert.CertID
Date?
Other cert fields if you want.

in the subform tblPersonCert.CertID will be a combo box. It will show the CertName but will store CertID into tblPersoneCert. So you see and pick a certname but store the ID.
Date Is the Data on the picture I send. That´s the value i need to show
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:47
Joined
May 21, 2018
Messages
8,463
So include on the subform a combo to select the certid and store in tblPersonCert (but show the cert name in the combo) and a textbox to add the date.
 
Last edited:

Users who are viewing this thread

Top Bottom