UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount + Datediff - Limit Result To The Next 30 Days, Access 2016    
 
   
Vaupell
post Sep 6 2018, 11:34 AM
Post#1



Posts: 11
Joined: 6-September 18



Hi Gentlemen

I'm working on a small setup where i have a table containing | ItemNo | ItemDesc | InspectionDate | NextInspection
And i wish to short them into
- Items past due
- Items to be inspected within the next 30 days
and ignore the items beyond 30 days from now.

The problem is my sorting of items to be sorted within the next 30 days, instead of only showing the next items it shows all future items.
I would like to limit it to only show those within the next 30 days.

Please advice,,

For this i have setup a table Attached File  a2.jpg ( 31.75K )Number of downloads: 2


And a form Attached File  a1.jpg ( 19.93K )Number of downloads: 0


And some code.
CODE
Private Sub Form_Load()

Dim WarningDate As Integer
Dim Urgent As Integer
Dim PastDue As Integer
    
'   Days before alert
    WarningDate = 30
    
'   Sort table rows, based on next inspection date, compared to current date.
'   If the next inspection day is less than WarningDate then call as urgent.

    Urgent = DCount("*", "ItemsDatabase", "DateDiff('d', [NextInspection], Date()) < " & 30)
    PastDue = DCount("*", "ItemsDatabase", "DateDiff('d', [NextInspection], Date()) >= " & 0)
    
' Debug label
    Me.DebugLabel.Caption = "DEBUG>> Warning: " & WarningDate & " - Urgent: " & Urgent & " PastDue:" & PastDue & " - Date: " & Date
    
If Urgent > 0 Then
    Me.UrgentLabel.Caption = "You have " & Urgent & " URGENT Issue(s)"
    Me.UrgentLabel.ForeColor = Heighlight
    Me.UrgentLabel.FontWeight = 700
ElseIf Urgent = 0 Then
    Me.UrgentLabel.Caption = "You have no urgent issues"
    Me.UrgentLabel.ForeColor = vbBlack
    Me.UrgentLabel.FontWeight = 400
End If

If PastDue > 0 Then
    Me.PastDue.Caption = "You have " & PastDue & " PAST DUE Issue(s)"
    Me.PastDue.ForeColor = Heighlight
    Me.PastDue.FontWeight = 700
ElseIf Urgent = 0 Then
    Me.PastDue.Caption = "You have no PAST DUE issues"
    Me.PastDue.ForeColor = vbBlack
    Me.PastDue.FontWeight = 400
End If

End Sub
Go to the top of the page
 
theDBguy
post Sep 6 2018, 11:37 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,723
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

To get a list of items due in the next 30 days, you should be able to create a query with a date range criteria. For example:

SELECT * FROM TableName WHERE NextInspection Between Date() And Date() + 30

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Vaupell
post Sep 6 2018, 11:38 PM
Post#3



Posts: 11
Joined: 6-September 18



This sounds good, however i have zero luck running SQL commands in the VBA enviroment.

Normally i would setup a string with my query and then execute the query.
However VBA does not seem to comply with how i would run it in a php or aspx enviroment.

Maybe someone can enlighten me on who to run the query in VBA?

To work around my own incompetence i added a "Boolean" column to the table called Urgent

Then i tryed to execute this on "formload"
CurrentDb.Execute "UPDATE ItemsDatabase SET Urgent '" & True & "' WHERE NextInspection BETWEEN Date() And Date() + 30"

Wanting to just set a check if the date is in range, because then its easy to store and locate again.
This post has been edited by Vaupell: Sep 6 2018, 11:50 PM
Go to the top of the page
 
MadPiet
post Sep 7 2018, 12:18 AM
Post#4



Posts: 3,171
Joined: 27-February 09



Why not create a subform on your form and just set the form's recordsource to a query like the one DBGuy showed? Then it would filter for only the stuff that's due in the next 30 days.
Go to the top of the page
 
Vaupell
post Sep 7 2018, 12:47 AM
Post#5



Posts: 11
Joined: 6-September 18




Solved it, by doing this.

CODE
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM ItemsDatabase WHERE NextInspection BETWEEN Date() And Date() + " & WarningDate)
    Urgent = rs.RecordCount
    rs.Close


Do i mark a topic here as resolved or ?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th July 2019 - 06:31 PM