Problem With Select Case Statement 1

[ooch1]

Hello,

I'm currenlty creating an Access module that will search through a particular customers records (Gas metering)history and if the record per meter has a null date, then populate with the original date.

However I am trying to use a select case statement, but for some reason it doesn't seem to work for multiple additional test criteria.

The help menu's example shows when one variable is being tested which doe not help me.

Is there a way to test combinations of different variables in a select case statement, or can it only be done testing one at a time.

The code as it stands is

Do While Not rs.EOF 'loop from 1st row last row
    CS = Nz(rs![NA_Ctrt_STATUS], ""): E = rs![Effective From Date]: M = rs![meterpointref]: S = Nz(rs![siterefnum], "") ' store rs refs
    E2 = Nz(rs2![Effective From Date]): M2 = rs2![meterpointref]: S2 = Nz(rs2![siterefnum], ""):
    Select Case M
        Case Is = M2, (rs.AbsolutePosition + 1) <> rsEnd, F <> True, IsNull(E2) = True
            Edt = rs![Effective From Date].Value: F = True: rs2.MoveNext
        Case Is <> M2, (rs.AbsolutePosition + 1) <> rsEnd
            Select Case F
                Case Is = True
                    rs.Fields("effective from date") = Edt: rs.Update: rs2.MoveNext: F = False
                 Case Is = False
                    rs2.MoveNext
            End Select
        Case Is = M2, (rs.AbsolutePosition + 1) = rsEnd, E2 = ""
            Select Case F
                Case Is = True
                    rs2.Fields("effective from date") = Edt: rs2.Update: rs2.MoveLast
                Case Is = False
                    rs2.Fields("effective from date") = rs![Effective From Date].Value: rs2.Update: rs2.MoveLast
            End Select
        Case Is <> M2, (rs.AbsolutePosition + 1) = rsEnd, E2 = "", F = True
            rs2.Fields("effective from date") = Edt: F = False: rs2.MoveLast: rs2.Update
    End Select
    rs.MoveNext
Loop

As you can see as well as testing M, there are other influencing factors.

P.S - i have already tried using 'And', but this cut no ice.

I hope someone out there can help me????

OOch

 

[PaulBricker]

Well,
1, you are only testing for M in your Select Case statement and not rs.AbsolutePostion -1, F or E2. Therefore, you don't have anything in your Case Is expression to compare the values to.
2. you can't mix datatypes in your Select Case statement. i.e.
Select Case myINTEGER And myINTEGER2

will fly but

Select Case myINTEGER And mySTRING

won't. I'm not sure what M is but it looks like text/string and the other three look like Integer values.
Something like this should work

Select Case M
      Case M2
    Select Case (rs.AbsolutePostion -1) And F And E2
        Case <> 1 And 1 And 0
          Do stuff
     End Select
       Case Is <> M2
        Select Case (rs.AbsolutePOstion - 1)
           Case Is <> 1
             Do Stuff
        End Select
End Select

I think this is the logic you need to follow, grouping your values by datatypes.

Paul

 

[PaulBricker]

On some addtional testing there are some problems with some of my Select statements.
I would think an If..Then statement would be alot more functional in this situation. You can mix operators in the If statement where you can't in the Select Case.
The line
If M = M2 and (rs.AbsolutePostion-1)<> rsEnd And F = True and IsNull(E2) Then

will get you a lot further a lot faster than a bunch of decending Select statements. Just my opinion.

Paul

 

[ooch1]

Paul,

Thanks very much for the advice, i have in fact plumped for the if statement for ease, although i was not aware of the mixing of datatypes within a select statement either.

Thanks for the advice, you can have a star.

OOch