Reduce the code ?

[illusion]

i don't know if this is the right section but i need some help with my code.
i am wondering if it's possible to reduce this code or if it's the right structure..

i have a lot of if statments... check the attachment

 

[Uncle Gizmo]

It is good practice to add the following to the beginning of your code :

Option Compare Database
Option Explicit

====================================================
I cannot find "FormatCount As Integer" used anywhere in your procedure. You should remove this, if you do not intend using it.

=====================================================
Select Case Month(Date)

Be careful with "reserved words " any word that you would associate with a program is usually a reserved word. This means that if you use that word in your own code, you could fall victim to unusual happenings due to a misinterpretation of what the program expects to happen. "date" is certainly a reserved word, I am not sure about "month" but it may well be. To avoid any conflicts in your code check variable names and control names against a list of reserves words.
Alternatively, as it is possible (although unlikely) new reserved words may occur in future Releases of the software, always prefix your words with a code. the best way is to use a naming convention for example a date variable would be named something like dtMyDate, an integer would be named something like intMyInterger.

========================================================
I believe you can write the following code:

  Case 9
If (Sumofmonths < 9) Then
Me.lbl1.Visible = True
i = i + 1
End If

Case 10
If (Sumofmonths < 9) Then
Me.lbl1.Visible = True
i = i + 1
End If

More simply as something along the lines of :

  Case 9 , 10
If (Sumofmonths < 9) Then
Me.lbl1.Visible = True
i = i + 1
End If
End If

There are a few listings that you can apply that to.

 

[illusion]

thanks for the reply.

It is good practice to add the following to the beginning of your code :

Option Compare Database
Option Explicit

================================================== ==
I cannot find "FormatCount As Integer" used anywhere in your procedure. You should remove this, if you do not intend using it.

i don't know a lot about access or vb coding. i don't know what those are used for.. anw

Select Case Month(Date)

this is for current month
i changed the code to what you told me

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
i = 0

Select Case Month(Date)
Case 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8
If (Sumofmonths < 9) Then
Me.lbl1.Visible = True
i = i + 1
End If

Case 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8
If (Sumofmonths < 19) Then
Me.lbl2.Visible = True
i = i + 1
End If

the same patern is used to the rest of code but now is not working. it doesn't make visible any of the labels as was done with my previous example in the attachment.

 

[dt01pqt]

I cannot find "FormatCount As Integer" used anywhere in your procedure. You should remove this, if you do not intend using it.

If this is a subroutine for an instanced event on a form then the format does not usually allow for shortening. This is because a number is past into FormatCount for use in the procedure if you need it. Just like KeyPress the keyAscii is passed into the procedure.

 

[dt01pqt]

I would do it this way. what is sumofmonths?

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
Dim Mnth as Integer
i = 0
Mnth = Month(Date)
Select Case Mnth 
Case 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8
    If (Sumofmonths < 9) Then
        Me.lbl1.Visible = True
        i = i + 1
    End If

Case 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8
   If (Sumofmonths < 19) Then
       Me.lbl2.Visible = True
       i = i + 1
   End If

snip...

End Select

edit: hmm it still seems a bit dodgy. What exactly are trying to do? For complex logic it is often better to use arrays otherwise you end up spending all your time working out every possible condition and you write reams of code. Having said that I was once challenged by a tutor to code this shifter game in Pascal (not a visual language) using only one array for movement rather than two. I did it with none pure logic. The movement section alone was over 90 pages long and the grid was only 6x6. Trust me only half the paper was wasted on printing out a hard copy but it was worth it seeing the look on her face.

 

[Uncle Gizmo]

If this is a subroutine for an instanced event on a form then the format does not usually allow for shortening. This is because a number is past into FormatCount for use in the procedure if you need it. Just like KeyPress the keyAscii is passed into the procedure.

Opps! I didn't spot that, although "Cancel as Integer" should have waved a red flag at me!

Sorry about that!!!

 

[illusion]

Opps! I didn't spot that, although "Cancel as Integer" should have waved a red flag at me!

Sorry about that!!!

should i remove those lines ?

still not working with dt01pqt's way.

what is sumofmonths?

is an expression in a query, this report has to do with paymets, showing which months(lbl1,lbl2....) member hasn't payed and counting the months that hasn't payed (i=i+1)

 

[Uncle Gizmo]

should i remove those lines ?

No, I mistakenly thought your code was a function that you had created. However it is a subroutine of a report. It is important that you leave the following as it is:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

 

[Uncle Gizmo]

I am not doing very well today! It must be the red wine.....

>>>> Select Case Month(Date) <<<<<

Is perfectly OK as it is Because you are using the reserved words!

dt01pqt suggestion of adding the following line would make the code more readable, and for that reason is the much better.

Mnth = Month(Date)
Select Case Mnth

As for the numbers in the case statement, if the numbers are in the first case, then they are ignored in the second case. That may be why your code is not working correctly. So I would suggest you go back to your "If Statement's" for now, same old adage really, if it's working leave well alone!

And another adage I've just made up "don't post when you've had a drink" I remember this is what happened when I posted last time I had a drink, a right balls up!

 

[Mile-O]

Select Case Month(Date)
Case 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8
If (Sumofmonths < 9) Then
Me.lbl1.Visible = True
i = i + 1
End If

Case 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8
If (Sumofmonths < 19) Then
Me.lbl2.Visible = True
i = i + 1
End If

The second Case will never execute as the code will visit the first one on all instances of 1 through 12.

 

[illusion]

ok thank you Uncle Gizmo,
cheers

SJ McAbney i was wondering y that happened and then realise that it's case. thnx

 

[dt01pqt]

As for the numbers in the case statement, if the numbers are in the first case, then they are ignored in the second case. That may be why your code is not working correctly. So I would suggest you go back to your "If Statement's" for now, same old adage really, if it's working leave well alone!

I would agree, even with a stack of if statements it is not likely to be shorter. However if illusion encounters a problem of this magnitude again I would seriously suggest using arrays, even if it means creating your own abstract from a visual diagram. Sometimes it is impossible to work out every eventuality in a case by case basis so you have to approach it a different way. Humans are not supercomputers they just build them.