Solved

3 failed attempts rule - failed

Posted on 2006-11-15
37
364 Views
Last Modified: 2008-03-17
The following code was recently worked on....but I believe it still need some tweaking....
if user enters an invalid date in frmGetdateFiling.txtGetdateFiling, I recieved an Invalid date msgbox,
hoever the code is expected to give me 3 other attempts before shutting down db..this is not havppening.

Instead I get a message that I have no attempt left even though user has only invalid date once. How do I correct this?
' *************
'Title for message boxes, change as u see fit
Private Const FRM_TITLE = "Filing Date"

'Define the number of days for your grace period here
Private Const GRACE_PERIOD = 14

'Define the max number of attemps possible
Private Const MAX_FAILED_ATTEMPTS = 3

'This variable is used to record the number of times the date entered is invalid
Dim m_iNoAttempts As Integer

Public Function GracePeriodValidation(ByVal dValDate As Date, ByVal iQQ As Integer, ByVal iYY As Integer) As Boolean

     Dim iGracePeriod As Integer
    Dim dLastDate As Date
    Dim dCheckDate As Date
       
    'Get the quarter to file. If quarter 1 then for previous quarter, we need to subtract the  year also
    'Here we define the grace period
    'It might be useful if u define is 14 as a constant
    'or store it in sum lookup table
    iGracePeriod = GRACE_PERIOD
   
    'Get last date, calculated using DateSerial
    'Pass in current year, then month is calculated by multiplying current quarter by 3
    'then we add 1, so we end up the following month
    'the last parameter is 0, which means we take away one day
    'thus giving us the last day of the previous month i.e. end of quarter
    dLastDate = DateSerial(iYY, (iQQ * 3) + 1, 0)

    'Check current date is within the filing date period
    'Return false if invalid, TRUE otherwise
    If dValDate < dLastDate Or dValDate > DateAdd("d", GRACE_PERIOD + 1, dLastDate) Then      '<<-- I changed this line from 14  to 15
        GracePeriodValidation = False
    Else
        GracePeriodValidation = True
    End If

End Function
0
Comment
Question by:billcute
  • 19
  • 18
37 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17946277
I thought the 3 failed attempts rule was at when u enter an invalid date and it was working
what went wrong?


this is the last bit of code I did for u - and its been slightly modified to handle the Null error (I apologise for that!!!)


Private Sub txtGetDateFiling_BeforeUpdate(Cancel As Integer)

    Dim sAttemptsLeft As String
   
   
    'Check date entered, if not, tell user its a required field
    If IsNull(Me.txtGetDateFiling) Then
        MsgBox "Date is Required", vbInformation, FRM_TITLE
       
        'set cancel to true to ensure user stays on that field
        Cancel = True
    Else
   
        'Validate date entry, ensure date entered is within the grace period
        If GracePeriodValidation(Me.txtGetDateFiling, Me.txtQtr, Me.txtYear) = False Then
           
            'Build up the "number of attempts" message
            If MAX_FAILED_ATTEMPTS - m_iNoAttempts = 1 Then
                sAttemptsLeft = "1 attempt"
            ElseIf MAX_FAILED_ATTEMPTS - m_iNoAttempts = 0 Then
                sAttemptsLeft = "no more attempts"
            Else
                sAttemptsLeft = MAX_FAILED_ATTEMPTS - m_iNoAttempts & " attempts"
            End If
           
            MsgBox "Sorry but the date entered is not within the Grace Period. " & vbCrLf & vbCrLf & "You have " & sAttemptsLeft & " left.", vbInformation, FRM_TITLE
            m_iNoAttempts = m_iNoAttempts + 1
   
            'IF NO MORE ATTEMPTS THEN EXIT DATABASE
            If MAX_FAILED_ATTEMPTS - m_iNoAttempts = 0 Then
               MsgBox "This application will now close.", vbInformation, FRM_TITLE
               Me.Undo
               Application.Quit
            Else
                'set cancel to true to ensure user stays on that field
                Cancel = True
            End If
        End If
    End If
   
End Sub

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17946384
Bill, tell me the exact steps u did so I can try reproduce your problem
0
 
LVL 4

Author Comment

by:billcute
ID: 17946503
okay...
(1). first delete any update for the 3rd Qtr in tblFilingdates...

(2). set your pc clock back to 10/14/06 then
(3). launch frmGetdateFiling...it should give you a default value of 10/14/06 - the date you are
      trying to update tblFilingDate...
(4). Type in invalid dates such as 09/28/06, 10/15/06, 11/02/06
(5). At each stage, it should tell you an invalid date entry msgbox..and how many re-tries you have
     before PC is shut down
(6). 3 failed attempts are expected...after the 3rd failed attempt shut down without
    any further warning

This sequence has problem...if you could redesign it such that it shuts down at the 3rd and final re-try immediately...in addition...the warning msgbox after the first failed attempt should say...you have two more attempts....on the 3rd failed attempt...there should be no more msgbox other than...db will now shut down...

it's more effective this way..

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17946574
ok I did that
I entered 09/28/06 and I got "invalid date - 2 attempts left"
I then entered 15/10/06 and it accepted it , so I retried this time I entered 16/10/06
and now I see where we are screwed, oh dear
sorry, Bill u could just post in the original question, I will still fix it for ya, saves u creating a new question

ok, in the above posted code, change this line from this

If MAX_FAILED_ATTEMPTS - m_iNoAttempts = 0 Then

to this (change is  = 0 to < 0)

If MAX_FAILED_ATTEMPTS - m_iNoAttempts < 0 Then



if u only want the one message on the 3rd failed attempt, we can do this
set sAttemptsLeft to ""
then check this before msgbox
e,g.,

        'Build up the "number of attempts" message
        If MAX_FAILED_ATTEMPTS - m_iNoAttempts = 1 Then
            sAttemptsLeft = "1 attempt"
        ElseIf MAX_FAILED_ATTEMPTS - m_iNoAttempts = 0 Then
            sAttemptsLeft = ""
        Else
            sAttemptsLeft = MAX_FAILED_ATTEMPTS - m_iNoAttempts & " attempts"
        End If
       
        If sAttemptsLeft <> "" Then MsgBox "Sorry but the date entered is not within the Grace Period. " & vbCrLf & vbCrLf & "You have " & sAttemptsLeft & " left.", vbInformation, FRM_TITLE



So final code is now

Private Sub txtGetDateFiling_BeforeUpdate(Cancel As Integer)

    Dim sAttemptsLeft As String
   
   
    'Check date entered, if not, tell user its a required field
    If IsNull(Me.txtGetDateFiling) Then
        Me.txtGetDateFiling.SetFocus
        MsgBox "Date is Required", vbInformation, FRM_TITLE
       
        'set cancel to true to ensure user stays on that field
        Cancel = True
    End If
   
    'Validate date entry, ensure date entered is within the grace period
    If GracePeriodValidation(Me.txtGetDateFiling, Me.txtQtr, Me.txtYear) = False Then
       
        'Build up the "number of attempts" message
        If MAX_FAILED_ATTEMPTS - m_iNoAttempts = 1 Then
            sAttemptsLeft = "1 attempt"
        ElseIf MAX_FAILED_ATTEMPTS - m_iNoAttempts = 0 Then
            sAttemptsLeft = ""
        Else
            sAttemptsLeft = MAX_FAILED_ATTEMPTS - m_iNoAttempts & " attempts"
        End If
       
        If sAttemptsLeft <> "" Then MsgBox "Sorry but the date entered is not within the Grace Period. " & vbCrLf & vbCrLf & "You have " & sAttemptsLeft & " left.", vbInformation, FRM_TITLE
        m_iNoAttempts = m_iNoAttempts + 1

        'IF NO MORE ATTEMPTS THEN EXIT DATABASE
        If MAX_FAILED_ATTEMPTS - m_iNoAttempts < 0 Then
           MsgBox "This application will now close.", vbInformation, FRM_TITLE
           Me.Undo
           'Application.Quit
           DoCmd.Close
        Else
            'set cancel to true to ensure user stays on that field
            Cancel = True
        End If
    End If
   
End Sub




My apologies for any screw ups caused
0
 
LVL 4

Author Comment

by:billcute
ID: 17946734
rockiroads,
I tested this ..and there are still problems...
(1). the count down has been eliminated...once an invalid date entry is detected...I am now getting only one msgbox
      the Application will now shut down...instead of the 3 count daown and when it gets the last failed attempt...then
      it should give me....the Application will now shut down...
(2)...the db does not shut down in my last test...it closes just the frmgetdateFiling.

New Error Run-time error 2108:
"You must save the field before executing the GoToControl Action ...or SetFocus method

....on...
   Me.txtGetDateFiling.SetFocus

To simulate this error....do the following:

(a). open frmGetdateFiling when you receive the default date, clear it and enter nothing, click the btnCancel instead you will now get thsi error...

when I rem out the error line... and repeated the simulation error again...

I now got Invalid use error...Run-time error 94...(addressed in one of my other listing)...

on....

If GracePeriodValidation(Me.txtGetDateFiling, Me.txtQtr, Me.txtYear) = False Then

so...two errors in one...

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17946786
ok, regarding first one, sorry I modified it to close form, as a test. Forgot to remove that
see the code

           'Application.Quit
           DoCmd.Close

get rid of DoCmd.Close
and uncomment Application.Quit


and this line
Me.txtGetDateFiling.SetFocus

I thought I got rid of it, it shouldnt be there as u are already on the field! Get rid of it and try again

and it was a copy/paste from the older code, add in the Else bit


here u are, sorry mate

Private Sub txtGetDateFiling_BeforeUpdate(Cancel As Integer)

    Dim sAttemptsLeft As String
   
   
    'Check date entered, if not, tell user its a required field
    If IsNull(Me.txtGetDateFiling) Then
        MsgBox "Date is Required", vbInformation, FRM_TITLE
       
        'set cancel to true to ensure user stays on that field
        Cancel = True
   
    Else
        'Validate date entry, ensure date entered is within the grace period
        If GracePeriodValidation(Me.txtGetDateFiling, Me.txtQtr, Me.txtYear) = False Then
           
            'Build up the "number of attempts" message
            If MAX_FAILED_ATTEMPTS - m_iNoAttempts = 1 Then
                sAttemptsLeft = "1 attempt"
            ElseIf MAX_FAILED_ATTEMPTS - m_iNoAttempts = 0 Then
                sAttemptsLeft = ""
            Else
                sAttemptsLeft = MAX_FAILED_ATTEMPTS - m_iNoAttempts & " attempts"
            End If
           
            If sAttemptsLeft <> "" Then MsgBox "Sorry but the date entered is not within the Grace Period. " & vbCrLf & vbCrLf & "You have " & sAttemptsLeft & " left.", vbInformation, FRM_TITLE
            m_iNoAttempts = m_iNoAttempts + 1
   
            'IF NO MORE ATTEMPTS THEN EXIT DATABASE
            If MAX_FAILED_ATTEMPTS - m_iNoAttempts < 0 Then
               MsgBox "This application will now close.", vbInformation, FRM_TITLE
               Me.Undo
               Application.Quit
            Else
                'set cancel to true to ensure user stays on that field
                Cancel = True
            End If
        End If
    End If
   
End Sub


0
 
LVL 4

Author Comment

by:billcute
ID: 17946791
Your code posted in the other post...(see below) rectified the Run-time error messages only if we comment out....
this line completely as shown here....

'  Me.txtGetDateFiling.SetFocus
' ************

here is your code from the other post....on RT 94.

'............................
' ..........................
 Dim sAttemptsLeft As String
 
    'Check date entered, if not, tell user its a required field
    If IsNull(Me.txtGetDateFiling) Then
        MsgBox "Date is Required", vbInformation, FRM_TITLE
       
        'set cancel to true to ensure user stays on that field
        Cancel = True

'ADD THIS LINE HERE   '<<<----- Corrected line that fix the error but absent in your last comment for this post
    Else
   
        'Validate date entry, ensure date entered is within the grace period
        If GracePeriodValidation(Me.txtGetDateFiling, Me.txtQtr, Me.txtYear) = False Then
' ................................
' ..............................
' ********************

If you could incorporate this correction in your final post on this subject it would be great.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17946805
I guess we both posted at the same time...

ok..ignore my last comment..

Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17946849
rockiroads,
One last glitch....10/15/06...is an "Invalid date".....it's not supposed to be accepted as it falls outside the "grace" period".....

My test just revealed that txtGetDateFiling is accepting it as a valid date... can we fix this?

Also the 3 attempt warning before the final shut down is not working.....the db is closing after one failure.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17946969
Did u restart the form before u tested as its not closing for me

also regarding the 15th, could this have anything to do with adding the extra day in the DateAdd within GracePeriodValidation?

In the function, there is this line

    If dValDate < dLastDate Or dValDate > DateAdd("d", GRACE_PERIOD + 1, dLastDate) Then      '<<-- I changed this line from 14  to 15

before it was hardcoded to 14, do u remember changing it to 15?
I then changed the code to include the constant

    If dValDate < dLastDate Or dValDate > DateAdd("d", GRACE_PERIOD, dLastDate) Then


See if that makes a difference




0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17946998
One issue we have, I just realised is that if u enter an invalid date then hit cancel, the beforeupdate function still gets triggered

Let me think about this, I will get back to you on this
0
 
LVL 4

Author Comment

by:billcute
ID: 17948357
rockiroads
As for the grace period and date report was filed...I modified your code as soon below ...tested it and obtained the following results..

It seems to me that further tweaking is required for the code to work properly.

TESTING:
I set PC date to 10/14/06 and then tried opening frmGetdateFiling...

I received a message that "grace period has passed"...and as you are aware 10/14/06 is part of the grace period and ought to be accepted....

I also tested it by typing in 09/30/06....as this date is part of the grace period...it is not working right as frmGetdatefiling did "not" open
.
I received msgbox that "Grace period has lapsed or expired... (we need tweaking for 09/30/06.

A test from Oct. 1st through 14th was successful as frmGetdateFiling opened up as expected...

take note that:
frmGetdateFiling is expected to open as from 09/30/06 thru 10/14/06.....

Also note..when frmGaetdateFiling opens and I typed in 10/15/06...it accepted the date....10/15/06 is an Invalid date...it should not accept that input.

When I set the PC clock to 10/15/06 and tested opening the form...frmGetdatefiling did not open as expected....

in this test the message "grace period has lapsed /expired" was displayed.. ...

***********In the above test, result obtained were all based on the code settings below:********.

from:
Private Const GRACE_PERIOD = 14

to...  

Private Const GRACE_PERIOD = 15                             '<<--- set to15

and...
 from :
If dValDate < dLastDate Or dValDate > DateAdd("d", GRACE_PERIOD +1, dLastDate) Then

to....

If dValDate < dLastDate Or dValDate > DateAdd("d", GRACE_PERIOD, dLastDate) Then   '<<----- removed +1 here
' ****************

Here is he complete code:

'Title for message boxes, change as u see fit
Private Const FRM_TITLE = "Filing Date"

'Define the number of days for your grace period here
Private Const GRACE_PERIOD = 15                             '<<--- set to 15

'Define the max number of attemps possible
Private Const MAX_FAILED_ATTEMPTS = 3

'This variable is used to record the number of times the date entered is invalid
Dim m_iNoAttempts As Integer

Public Function GracePeriodValidation(ByVal dValDate As Date, ByVal iQQ As Integer, ByVal iYY As Integer) As Boolean

     Dim iGracePeriod As Integer
    Dim dLastDate As Date
    Dim dCheckDate As Date
   
    'Get the quarter to file. If quarter 1 then for previous quarter, we need to subtract the  year also
    'Here we define the grace period
    'It might be useful if u define is 14 as a constant
    'or store it in sum lookup table
    iGracePeriod = GRACE_PERIOD
   
    'Get last date, calculated using DateSerial
    'Pass in current year, then month is calculated by multiplying current quarter by 3
    'then we add 1, so we end up the following month
    'the last parameter is 0, which means we take away one day
    'thus giving us the last day of the previous month i.e. end of quarter
    dLastDate = DateSerial(iYY, (iQQ * 3) + 1, 0)

    'Check current date is within the filing date period
    'Return false if invalid, TRUE otherwise
    If dValDate < dLastDate Or dValDate > DateAdd("d", GRACE_PERIOD, dLastDate) Then      '<<-- I changed this line from 14  to 15                                                     ^^-------- I removed +1 from this line code here
        GracePeriodValidation = False
    Else
        GracePeriodValidation = True
    End If
End Function
' ********

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17953961
rockiroads,
If you have time, please check this out for me with a sample
http://www.experts-exchange.com/Databases/MS_Access/Q_22062057.html

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17955966
rockiroads,
Any luck with this thread?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17959200
rockiroads,
I realize that you may be very busy...
What do you suggest here for this listing? close and refund points?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17959890
ok, looking at it more closely

I have found that because I was using Now() in some places, it contained the time and this was messing up the date checks. Much easier to extract date part in function that change all occurrences.

    dValDate = DateValue(dValDate)


Now the other issue with 30th Sep. Because it's september, we always get the current qtr-1, so we end up checking qtr 2. So if we change the logic so that entering the last day of the quarter is deemed current quarter, how does that sound?

So what I did was create a function that returns the last date of the quarter and also modified GracePeriodValidation function



'A function that returns the last date of the quarter
'Optional arguments, u can pass in a selected quarter, otherwise it takes it from current system date
'and year again is optional, if not specified, it takes it from system date
Public Function GetLastQuarterDate(Optional ByVal iQQ As Integer = 0, Optional ByVal iYY As Integer = 0) As Date

    Dim iUseQQ As Integer
    Dim iUseYY As Integer
   
    'Assign the fields to use based on parameters
    If iQQ = 0 Then iUseQQ = Format(Now(), "q") Else iUseQQ = iQQ
    If iYY = 0 Then iUseYY = Year(Now()) Else iUseYY = iYY
   
    'Get last date, calculated using DateSerial
    'Pass in current year, then month is calculated by multiplying current quarter by 3
    'then we add 1, so we end up the following month
    'the last parameter is 0, which means we take away one day
    'thus giving us the last day of the previous month i.e. end of quarter
    GetLastQuarterDate = DateSerial(iUseYY, (iUseQQ * 3) + 1, 0)

End Function


'Pass in a date and validate that is within the grace period of the specified quarter
Public Function GracePeriodValidation(ByVal dValDate As Date, ByVal iQQ As Integer, ByVal iYY As Integer) As Boolean

    Dim iGracePeriod As Integer
    Dim dLastDate As Date
    Dim dCheckDate As Date
   
   
    'Get the quarter to file. If quarter 1 then for previous quarter, we need to subtract the  year also
    'Here we define the grace period
    'It might be useful if u define is 14 as a constant
    'or store it in sum lookup table
    iGracePeriod = GRACE_PERIOD
   
    dLastDate = GetLastQuarterDate(iQQ, iYY)
   
    'Check current date is within the filing date period
    'Return false if invalid, TRUE otherwise
    dValDate = DateValue(dValDate)
    Debug.Print dValDate, DateAdd("d", GRACE_PERIOD, dLastDate)
    If dValDate >= dLastDate And dValDate <= DateAdd("d", GRACE_PERIOD, dLastDate) Then
        GracePeriodValidation = True
    Else
        GracePeriodValidation = False
    End If

End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17959950
Now with Form_Load as it stands now
We check current date is not last date of quarter before getting quarter to work with


'When form loads, lets go into addmode
Private Sub Form_Load()
   
    Dim iQQ As Integer
    Dim iYY As Integer
   
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If Date <> GetLastQuarterDate Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Check current date is within the grace period
    If GracePeriodValidation(Now(), iQQ, iYY) = False Then
        MsgBox "Grace period has past/expired", vbExclamation, FRM_TITLE
        DoCmd.Close
    Else

        'Initialise number of attempts to be 1
        m_iNoAttempts = 1
       
        'Initial checks passed - we are within the filing date period
   
        'Now do a Preventive measure, u could check to see if for the defaults, a record already exists
        'then u either load that record or go into addmode
        Dim rs As DAO.Recordset
       
        Set rs = Me.RecordsetClone
        rs.FindFirst "QtrNo = " & iQQ & " AND YearNum = " & iYY
        'Matching record for current qq/yy does not exist, then go in addmode
        If rs.NoMatch = True Then
            DoCmd.GoToRecord , , acNewRec
       
            'Initialise variables with defaults
            Me.txtGetDateFiling = Date
            Me.txtQtr = iQQ
            Me.txtYear = iYY
            Me.txtGetDateFiling.SetFocus
        Else
            'Load record
            Me.Bookmark = rs.Bookmark
        End If
        rs.Close
        Set rs = Nothing
    End If
End Sub

0
 
LVL 4

Author Comment

by:billcute
ID: 17960243
rockiroads,
You wrote:
So what I did was create a function that returns the last date of the quarter and also modified GracePeriodValidation function

I have just tested no difference from the last post...

I am almost sure it was because of this reason which I also brought into limelight in the post...

***Remember my last note in that comment***:
You can create function that will nclude the following consideration........

 Each quarter may be slightly different if quarter ends on weekEND, new date will be the last
     business day of that month....eg... Qtr 3 is expected to end officially on 09/30/06 but because it falls on week end
    then quarter ends officially as from 09/29/06...here frmSPermitDate.txtPermitEdate will automatically default
    users to 09/29/06 because of the weeends issue.

...same for all quarters.

If we can get this straighten out - it's likely to solve the problem.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:rockiroads
ID: 17960330
oh, I missed that, sorry
so we always go back,  yes?
no probs
one sec
0
 
LVL 4

Author Comment

by:billcute
ID: 17960524
rockiroads,
Please disregard my last post.

frmGetDateFiling always default to Date(now) no matter what condition
This form updates tblFilingDates fields.

the only requirement needed here is:
prevent user entering dates other than dates btw date qrter ends and the 14 days grace period.

.....any date entry when frmGetDateFiling is opened that is outside the grace period should  be regarded as "invalid dates"...

if quarter ends for Qtr3 is 09/29/06, then 09/28/06 and dates from 10/15/06 are "Invalid dates"

....this should do it.

Regards
Bill
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17960621
ok bill, to handle this, calculating last working day was not the problem, it was getting the logic right
if date is 30/9/2006, current code would subtract -1 therefore u end up checking q2
so if current date is >= last business date of quarter and end of month for that quarter, then consider it to be current quarter
so here is the function


'a date is within the current quarter if its >= last business date in quarter and end of month of current quarter
Public Function DateIsLastQuarterDate(ByVal dCheckDate As Date) As Boolean

    Dim iQQ As Integer
    Dim dLastQDate As Date
   
    iQQ = Format(dCheckDate, "q")
    dLastQDate = DateSerial(Year(dCheckDate), (iQQ * 3) + 1, 0)
   
    'in case of sep2006, 29th is last quarter, but 30th is within this and so considered current quarter
    'otherwise we do -1, we end up in previous quarter
   
    If DateValue(dCheckDate) >= GetLastQuarterDate And DateValue(dCheckDate) <= dLastQDate Then
        DateIsLastQuarterDate = True
    Else
        DateIsLastQuarterDate = False
    End If
End Function




Im just doing some testing and then will report back, one sec
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17960684
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17960739
ok. Bill, lets clarify something, as Ive just seen your last post

this is about grace period

At the moment, I check for 14 days by doing a dateadd based on the last business date of the quarter

previously, it was 30/9/2006 valid up to 14/10/2006
currently the code now does 29/9/2006 and 14 days is therefore 13/10/2006
can u tell me if this is correct or not?
does the grace period fall from the end of the calendar month or the business date ?




regarding this comment

frmGetDateFiling always default to Date(now) no matter what condition

If a record already exists, u get that value right or is it always current date, even if record exists or not

0
 
LVL 4

Author Comment

by:billcute
ID: 17961699
okay,
Grace always end on the 14th of the next month irrespective of the date changes from previous month...

For example, 09/30/06 falls on Saturday, therefore date changes will only change to 09/29/06 which is the last Friday of that month.....but in October...it will remain October 14th as the end of the grace period...in this case the grace period is more than 14 days...as from 10/15/06....grace period has expired...


(2). frmGetDateFiling always default to (dateow) whether or not record exist.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17962428
rockiroads,
The code and the sample db works great. everything works fine except the line code below.
With his line code left at: Private Const GRACE_PERIOD = 14

Let me explain how. (If this part of the code cannot be resolved....I can wait use this as is and then try resolving the problem in future posts.

TESTING THE LOGIC.
(a). I set the PC clock to 10/14/06
With his line code left at: Private Const GRACE_PERIOD = 14

       .......and then launch frmSewer...the form was locked  and a msgbox told me to
      it was missing date filing for Qtr 3, 2006.
     Also when I double click frmGetDateFiling, I received the same message.
***The sequence is working fine except that it was locking form on the 14th instead of the 15th.***

(b). I also set the PC clock to 01/14/07.
       also set this to: Private Const GRACE_PERIOD = 14

      .......and then launch frmSewer...the form was locked  and a msgbox told me to
      it was missing date filing for Qtr 4, 2006.
     Also when I also double click frmGetDateFiling, I received the same message.

(c). I set the PC clock to 04/14/06
      also set this to: Private Const GRACE_PERIOD = 14    
     all the logice for this tested correctly without any glitch

ANALYSES
' *******
Test (a) above changed..
     frmSewer and frmGetDatefiling opens as expected....but somehow...it affect the 09/29/06 default date
     ...default was moved to 09/30/06 "only" when Admin tries to change the date in frmGetdateFiling defaut        
         that is: (dateNow) to something else......
  Note: All other logic worked fiine.

Test (b)
....Because the original default for Qtr 4 was 12/31/06,
both frmSewer and frmGetDateFiling forms did "not" open when clicked...
( 2days lost - i.e 2 days substracted from the 14 days of grace I guess)

Note:
For these two forms to open correctly, I will have to manually set the following
            Private Const GRACE_PERIOD = 16
' ************    
Note:
Grace period for each quarter terminates on the 14th of the month...this date is constant for all quarters.
for example:
Qtr 1 - grace period must terminate on 04/14/06.
Qtr 2 - grace period must terminate on 07/14/06.
Qtr 3 - grace period must terminate on 10/14/06 (irrespective of the fact that end of 3rd Qtr
          is now defaulted to 09/29/06 - because 09/30/06 would have been Saturday
          (office does not open on weekend)
Qtr 4 - - grace period must terminate on 01/14/07 - (irrespective of the fact that end of 4rd Qtr
          will be defaulted to 12/29/06 - because 12/31/06 would have been Sunday
          (office does not open on weekend)
.' *******************

Here is my answer to your last comment:

The only time the default date is recorded in tblFilingDates in when field FDID increment by 1 and QtrNo and YerNum are all updated at the same time...

***If user hits the btnCancel, then none of these fields will be updated into tblFilingDates.****

The issue here is the default value for frmGetDateFiling.txtGetDateFiling is left "blank"

However, your current code in btnOk records date(NOW) "only" when btnOK is clicked

...that's it.

Thanks a million.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17963141
Bill
I will sort this out, no need for another question
0
 
LVL 4

Author Comment

by:billcute
ID: 17963215
ok

thanks

Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17963247
If grace period always terminates on the 14th, then we can change the rules slightly

I had assumed it was 14 days after the end of the month but because of this falling on weekends, it sort of messes that up. Can do one of two ways, simply add 14 days on last day of month or create a date for the 14th. Ive done the latter here, but I can easily change it to the former if u want. No real advantage between one or the other since both methods use the constant GRACE_PERIOD

look for the comment          'Build the 14th of the next month        and see the IF statement below it


'Pass in a date and validate that is within the grace period of the specified quarter
Public Function GracePeriodValidation(ByVal dValDate As Date, ByVal iQQ As Integer, ByVal iYY As Integer) As Boolean

    Dim iGracePeriod As Integer
    Dim dLastDate As Date
    Dim dCheckDate As Date
    Dim dMaxGracePeriodDate As Date
   
   
    'Get the quarter to file. If quarter 1 then for previous quarter, we need to subtract the  year also
    'Here we define the grace period
    'It might be useful if u define is 14 as a constant
    'or store it in sum lookup table
    iGracePeriod = GRACE_PERIOD
   
    dLastDate = GetLastQuarterDate(iQQ, iYY)
   
    'Check current date is within the filing date period
    'Return false if invalid, TRUE otherwise
    dValDate = DateValue(dValDate)
   
    'Build the 14th of the next month
    dMaxGracePeriodDate = DateSerial(iYY, Month(dValDate) + 1, GRACE_PERIOD)
   
    Debug.Print dValDate, DateAdd("d", GRACE_PERIOD, dLastDate)
    If dValDate >= dLastDate And dValDate <= dMaxGracePeriodDate Then
        GracePeriodValidation = True
    Else
        GracePeriodValidation = False
    End If

End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17963250
ah., ignore that one, not good
let me fix the bug in it
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17963262
ok, Ive reverted to the former, which I thought of doing in the first place!!! problem of latter I realised is date may be sep/oct so that messes it up
So I get last date of quarter then add 14 days to it and it must fall between there

'Pass in a date and validate that is within the grace period of the specified quarter
Public Function GracePeriodValidation(ByVal dValDate As Date, ByVal iQQ As Integer, ByVal iYY As Integer) As Boolean

    Dim iGracePeriod As Integer
    Dim dLastDate As Date
    Dim dCheckDate As Date
    Dim dLastDateOfMonth As Date
   
   
    'Get the quarter to file. If quarter 1 then for previous quarter, we need to subtract the  year also
    'Here we define the grace period
    'It might be useful if u define is 14 as a constant
    'or store it in sum lookup table
    iGracePeriod = GRACE_PERIOD
   
    dLastDate = GetLastQuarterDate(iQQ, iYY)
   
    'Check current date is within the filing date period
    'Return false if invalid, TRUE otherwise
    dValDate = DateValue(dValDate)
   
    'Build the 14th of the next month
    dLastDateOfMonth = DateSerial(Year(dValDate), (iQQ * 3) + 1, 0)
   
    If dValDate >= dLastDate And dValDate <= DateAdd("d", GRACE_PERIOD, dLastDateOfMonth) Then
        GracePeriodValidation = True
    Else
        GracePeriodValidation = False
    End If

End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17963263
Bill dont u sleep, it must be awfully late where u are. Arent u based in East side of USA or something
0
 
LVL 4

Author Comment

by:billcute
ID: 17964264
rockiroads,
The problem was not fixed.

Prform this simple tests:

(1). Set your PC to 01/14/07 then
     Open and close QtrReport, it should launch the frmGetDateFiling
 
(2). When it opens type in any of these "Invalid dates" 12/28/06 or 01/15/07

(3). If the date is not seen as Invalid dates...then the code is still not working right.

That's exactly what happened here, the last code tested and it accepted these two dates as if they were valid dates...

Let's consider this post officially closed...you can ponder on the question for awhile...if in the future you think you may have a fix....I'll be glad to re-open this topic again....

Fort now, I'll manage what I have now...it's good enough.


I appreciated all your energy on this topic.

Note:
Yes...in normal ciocumstances, I would have gone to sleep,... but I took two days off from work...as such was able to respond to these topics right away...

You know, I am just like you...when I have something on my mind...I always like to clear it ASAP. Hard work "yields" dividents so say and adage. You worked very hard to take up my questions even in the midst of other taslks...it then presents a challenge for me to respond.immediately.

I really appreciate your spending your precious time on my topics...

NB:
I still have not resolved the "date validation" issue with my login form..and I'll still want to post a question or two in the nearest future..for a fix...

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17968837
Your a hard working man Bill, u do your work when away from work also.

I would like to keep this open until we get this sorted.


ok, in GracePeriodValidation there is a line that sets dLastDateOfMonth, it is currently using the year of the date passed in. Slight tweak required.
Can u change it to use the year that is passed in pls (iYY) instead of calculating year Year(dValDate)

ie from this

    'Build the 14th of the next month
    dLastDateOfMonth = DateSerial(Year(dValDate), (iQQ * 3) + 1, 0)

to this

    'Build the 14th of the next month
    dLastDateOfMonth = DateSerial(iYY, (iQQ * 3) + 1, 0)

0
 
LVL 4

Author Comment

by:billcute
ID: 17970023
rockiroads,
This is excellent. It resolved the problem...

I noticed you are very determined to resolve the problem against all odds...that's my motto too...but I just didnt want to bother you.

Your efforts are appreciated.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17973510
rockiroads,
I have just opened a new thread for a very similar validation approach here but slightly different at:
http://www.experts-exchange.com/Databases/MS_Access/Q_22065742.html

I will appreciate your assistance at the link below:
http://www.experts-exchange.com/Databases/MS_Access/Q_22065742.html

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17977940
rockiroads,
Just checking out my last comment, I did not know that I listed the new thread twice until now...
.....sorry about that.
Did you get a chance to check it out?I dont expect it to be that involving because, I already utilized your previous code in the new thread - it only require fine tuning now. Any assistance with it will be appreciated.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 17994704
rockiroads,
 I know you are very busy, could please just take a minute to check out my other post and let me know your recommendation - it's the last one in the series.

Regards
Bill
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now