DCount with column as a Variable

[cybersardinha]

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.

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]

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]

Thank you.

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

 

[Gasman]

You have "" in front of mFB ?

 

[cybersardinha]

You have "" in front of mFB ?

I know. but it also doesn't work without

 

[Eugene-LS]

Any Ideias of what might cause this?

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

 

[cybersardinha]

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

Thanks

 

[Eugene-LS]

It works

But "database" (your table name) is one of reserved words!
Giving such names to database objects is not recommanded.
Please read the article: List of reserved words in Access 2002 and in later versions of Access

 

[MajP]

But "database" (your table name) is one of reserved words

I am suprised this worked

 sTblName = "database"    ' Are You sure in that NAME ???

would have thought it would fail without
sTblName = "[database]"

 

[Pat Hartman]

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]

@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:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[cybersardinha]

@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]

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]

@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

 

[MajP]

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]

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.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[cybersardinha]

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]

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.