IF and Then Statement to pull records based on date match in table

[chineloogbonna]

Good Afternoon,
Hoping someone can assist with this simple code. I cannot get the If then statement to work.

I have a table called "NIGO_Data" on the table there is a field called "SubDate" which holds the date of the record.

I want my if then statement to search the NIGO_Data Table and only count the recodes if the SubDate field matches Todays Date. So far I have this, but its not working. Please help!

Private Sub form_current()
On Error Resume Next
Dim CurrDate As Date
If (CDate(Forms!NIGO_Data!SubDate) <> CurrDate) Then
Exit Sub
Else

Me.txtCompleted = DCount("1", "NIGO_Data", "WorkType='Completed'")
Me.txtHandled = DCount("1", "NIGO_Data", "WorkType_fld='handled'")
Me.txtNoAction = DCount("1", "NIGO_Data", "WorkType_fld='NoAction'")
Me.txtWIP = DCount("1", "NIGO_Data", "WorkType_fld='WIP'")
Me.txtSuspend = DCount("1", "NIGO_Data", "WorkType_fld='Suspended/Serv Case'")
Me.txtFalseNIGO = DCount("1", "NIGO_Data", "WorkType_fld='FalseNIGO'")
Me.txtResort = DCount("1", "NIGO_Data", "WorkType_fld='Resort'")
End If

End Sub
​

Thank you!

 

[CJ_London]

what does 'not working' mean? you get an error, nothing happens, the wrong data is returned?

remove the on error resume next until you understand what your code is doing

only thing I can suggest at the moment is you have declared currdate but not assigned a value

 

[Galaxiom]

I have a table called "NIGO_Data" on the table there is a field called "SubDate" which holds the date of the record.

I want my if then statement to search the NIGO_Data Table and only count the recodes if the SubDate field matches Todays Date. So far I have this, but its not working.

You are currently testing the date on a form then either running the series of commands or not based on that test. The whole process needs to be a query.

Furthermore your DCounts are operating on a field named "1" which would not be right.

Try this query:

SELECT Count(somefield), WorkType_fld
FROM NIGO_Data
WHERE WorkType="Completed"
GROUP BY WorkType_fld

Use Count(*) if you don't care about Nulls. Count of a field will leave out Nulls in that field.

Using a series of Domain functions as you have is very inefficient.

Create a subform with your textboxes and use the query as the RecordSource.

 

[chineloogbonna]

Hi sorry, I am new. It's not finding the current date. If I take out the "if then statement" the Countit wrks perfect. It's not filtering the date by date.

Thanks
Chinelo

 

[Mark_]

To get you started, rather than using the table NIGO_Data, create a QUERY based off of NIGO_Data that has all of it's fields. In the criteria for the 'Date' field you want to work with, set it to DATE(). Make sure this returns ONLY the records for today. From there you can start working on having it match a specific date if needed.

Now do all of your DCount()s off of the query you added.

Once you get it working at all, start looking at using Galaxiom advice to make it much more efficient.

My suggestion is based off of your stating your 'new' so I figure start with things you should be able to figure out rather quickly. Setting a simple filter on a query should be one of the first things you'll want to learn and it makes using DCount() very easy.

 

[Galaxiom]

I didn't know how you were using the dates so I didn't include it before.
Maybe this is what you need.

SELECT Count(somefield), WorkType_fld
FROM NIGO_Data
WHERE WorkType="Completed"
AND [datefield] = Date()
GROUP BY WorkType_fld

What you definitely don't need is If/Then.
The selection criteria must be in the criteria of a query.

 

[chineloogbonna]

Thank you Galaxiom! I think your reply would work the best. However, what is "somefield" in the code below. The field I want to count is the "WorkType" field.

SELECT Count(somefield), WorkType_fld
FROM NIGO_Data
WHERE WorkType="Completed"
AND [datefield] = Date()
GROUP BY WorkType_fld
​

 

[Mark_]

If you want to count WorkType entries where WorkType = "Completed" and DateField = Date(), then replace "Somefield" with "WorkType".

 

[chineloogbonna]

Thank you to both of you! I just ended up doing a query then using a Subform.

 

[Pat Hartman]

I hope you used a query that looks like this:

SELECT Count(*) As RecCount, WorkType_fld
FROM NIGO_Data
WHERE SubDate=Date()
GROUP BY WorkType_fld

That gives you ALL the counts in a single query. There are two obvious advantages to this.
1. it is much more efficient than running separate domain functions for each possible value of WorkType_fld
2. It doesn't require maintenance if some new WorkType is added.

Just for everyone's edification:

Count(somefield)
counts the non-null values for somefield and using this method forces a full table scan.

Count(*)
Counts the rows in the domain and can frequently be satisfied by examining just the index rather than the entire table.

Count(*) with a Where Somefield Is Not Null would do the same thing as the first example, if that is really what you want and would probably be more efficient.

 

[Galaxiom]

I hope you used a query that looks like this:

SELECT Count(*) As RecCount, WorkType_fld
FROM NIGO_Data
WHERE SubDate=Date()
GROUP BY WorkType_fld

That gives you ALL the counts in a single query.

Yes that is what I was getting at in my first post but got misled by the OP's typo (their omission in Red) when I copied some of their code to base the query on.

Me.txtCompleted = DCount("1", "NIGO_Data", "WorkType[COLOR=red][B]_fld[/B][/COLOR]='Completed'")
Me.txtHandled = DCount("1", "NIGO_Data", "WorkType_fld='handled'")
Me.txtNoAction = DCount("1", "NIGO_Data", "WorkType_fld='NoAction'")