Visual Basic/VBA+Access

Advertisement


Question
Generated Report
Generated Report  
Hi there,

I am using Microsoft Access Program (MDB)for data Entry, I got the source of the Microsoft with Database ready and i want to modify some exist Module, this Module is programmed for us per work requirements, my Problem with the Due_Date function as programmed lately, the current formula as programmed in the Module of (Due_Date) is to give the Due_Date result 6 days and 12 days from date of submission or date of first data entry as per Class Field if changed.

P.s. Formula Programmed to be linked also to give the remaining days (See attached Photo for more explanation for the print out of report required)

Anyway, to make my inquiry so clear find below the Module Formula and i want the result to be 21 days for all over Due_Date instead of 6 days and 12 days , you will find also the comment that showing of last modification by date 20-Nov-12.  

-------------------------------


Attribute VB_Name = "DUE DATE"
Option Compare Database
Function calc_due(rec, class, subm, action)
Dim days, n, K, dt, wd, holi
'Stop
'If action Or IsNull(rec) Or IsNull(class) Or IsNull(subm) Then
'    calc_due = Null
'ElseIf class > 4 Or subm > 3 Then
'    calc_due = Null
'Else
   'K = (class - 1) * 6 + (subm - 1) * 2 + 1
   'days = Val(Mid("140606140606140606140606", K, 2))

days = IIf(subm = 1, 12, 6)
  
   dt = rec - 1
   n = 0
   While n <= days
       wd = Weekday(dt)
       If Not (wd = 5) Then
         holi = DLookup("holiday", "holidays", "holiday=#" + Str(dt) + "#")
         If (IsNull(holi)) Then
         n = n + 1
         End If
       End If
       dt = dt + 1
   Wend
   calc_due = dt
'End If
         
End Function
'programme by D.Variany
'reply days has been change to 12 and 6 for all classes as per new agreement 20-NOV-12

Function remdays(DueDate, rec, daysrem)

'days = DueDate
'dt = DATE

   'n = 0
   'While n <= days
   '    wd = Weekday(dt)
   '    If Not (wd = 5) Then
   '    n = n + 1
   '    End If
   '  dt = dt + 1
   'MsgBox dt
   'Wend
   'remday = dt

'Dim Counter
'Counter = DueDate    ' Initialize variable.
'While Counter > DueDate    ' Test value of Counter.
'    Counter = dt + 1    ' Increment Counter.
'Wend    ' End While loop when Counter > 19.
'    remday = Counter
'Debug.Print Counter    ' Prints 20 in the Immediate window.

End Function

Function remday(sstartdate, sendate)
Dim iDays
Dim iWorkdays
Dim sDay
Dim i

iDays = DateDiff("d", sstartdate, sendate)
iWorkdays = 0
For i = 0 To iDays
'first day of the week is sunday
sDay = Weekday(DateAdd("d", i, sstartdate))
If sDay <> 1 And sDay <> 7 Then
  iWorkdays = iWorkdays + 1
  End If
  Next
remday = iWorkdays
End Function






Function old()
   If class = 1 Then
       If subm = 1 Then
         days = 12
       ElseIf subm = 2 Then
         days = 6
       ElseIf subm = 3 Then
         days = 3
       End If
   
   ElseIf class = 2 Then
       If subm = 1 Then
         days = 18
       ElseIf subm = 2 Then
         days = 10
       ElseIf subm = 3 Then
         days = 6
       End If
   
   ElseIf class = 3 Then
       If subm = 1 Then
         days = 10
       ElseIf subm = 2 Then
         days = 5
       ElseIf subm = 3 Then
         days = 3
       End If
   
   ElseIf class = 4 Then
       If subm = 1 Then
         days = 24
       ElseIf subm = 2 Then
         days = 12
       ElseIf subm = 3 Then
         days = 6
       End If
   End If
       'cond = Eval ("Dlookup(" + Chr(34) + "holiday" + Chr(34) + "," + Chr(34) + "holidays" + Chr(34) + "," + "holiday=#" + Str(dt) + "#" Chr(34) + ")")

End Function

Answer
these functions are mostly unnecessary and cumbersome. and you have 'ed most of the lines so i dont know which is existing and wheter you can alter the module code. in case you dont want to change the existing code, add another function which reverses the above and adds 21 days.
or you could make a new function to add 21 days.

if u want me to write the code you need to pay me for my time. which version of access is used?

Visual Basic

All Answers


Answers by Expert:


Ask Experts

Volunteer


Ravindra

Expertise

visual basic application programming from design to access information, sql, engineering and commercial applications. access databases, excel.(VB6) basic questions in vb 2008. Optional: 1)a large number of people want me to do work which takes some time and effort. can do projects in vb 2008. Pl.note that i would like to be paid for such work. 2) if you want me to spend quality time and do special work, i expect to be paid a reasonable price for my time. 3) if you are pleased with my reply you could consider a good amount. 4) you can visit my website http://ravindra.coolpage.biz

Experience

programming since the past 15 years. have completed a large number of projects in Industrial applications, finance,accounts, correspondence. excel macros etc

Education/Credentials
engineering, management degrees.

Past/Present Clients
project work for a Norway company,USA company and a Canadian company completed. Freelance Project work and Teaching. helping programmers with their work
teaching vb

©2016 About.com. All rights reserved.