obtaining fiscal year and month

[kashishsinha]

i have four forms namely file status , load file, reconciliation, and reports.

Reconciliation form has 2list boxes.. namely fiscal year and fiscal month. In that we have to choose for eg say i chose fiscal year 2012 and month as 1, then when i click open the reports form i have reports in there which displays information as per the year and the month.

what my requirement is in this report it displays fiscal year as i have chosen in the first step. But i do not want it because fiscal year 2012 and fiscal month 1 actually corresponds to actual year 2011 and month 10 i . e October. i want this to be displayed over there.

so i have four text boxes in the form. one for FY one for FM one for AY and other for AM. FY and FM gets populated with 2012 and 1, i want the other two to have 2011 and 10 respectively. the code i tried using is not working. i am not sure as to what has to be added in the form and what in the module. i also donot know how to declare textbox value as variable

Private Sub test()
If Text19.Value > 0 Then
If Text19.Value < 4 Then
Text39.Value = Text19.Value + 9
Text43.Value = Text17.Value - 1
Else
Text39.Value = Text19.Value - 3
Text43.Value = Text17.Value
End If
End If

End Sub

 

[NickHa]

I assume the text boxes are unbound and are populated manually?

I have created an example, using text boxes named txtFM, txtFY, txtAM and txtAY (I used more meaningful names than just Textnn, which I suggest you do as well. You can change the names in the controls' property sheet under 'Other').

For the two text boxes where you want to check the input values, set the AfterUpdate property to point to an event procedure (in the control properties, 'Event' tab). This will generate an event stub in the code window, something like this:

Private Sub txtFM_AfterUpdate()
 
End Sub

Do the same for the other input box (txtAM in this example).

Now, copy the following into the code window, just below the two code stubs just created:

Private Sub checkDates()
Rem first check that both input fields have a value
If Nz(Me.txtFM, 0) = 0 Then Exit Sub
If Nz(Me.txtFY, 0) = 0 Then Exit Sub
Rem check month is in first quarter
If Me.txtFM < 4 Then
  Rem first quarter
  Me.txtAM = Me.txtFM + 9
  Me.txtAY = Me.txtFY - 1
Else
  Rem second quarter and later
  Me.txtAM = Me.txtFM - 3
  Me.txtAY = Me.txtFY
End If
End Sub

Now complete the code stubs as follows:

Private Sub txtFM_AfterUpdate()
checkDates
End Sub
 
Private Sub txtFY_AfterUpdate()
checkDates
End Sub

The logic in invoked when the new value is entered in the text box and the focus is changed to a different control (you could use the 'LostFocus' event equally well in this instance).

When either of the two input boxes changes value, both are checked to see that they are not empty. If both have a value, the calculation is done as your sample, changing both output boxes.

I would suggest putting some additional checks in the subroutine to make sure the values are numeric and have sensible values (e.g. month can't be > 12; year must be > 2010(?)).

i also donot know how to declare textbox value as variable

I don't understand what you mean by this?