Varaiable

[Acropolis]

Hi

I want to check the 16 inputs of a device we have. Each of these inputs are stored in a single row in the DB at the moment.

The way I had wanted to do it was similar to below;

    Increase = 1
    
    InboxMeterP(Increase) = rs2!InboxMeterSerialP(Increase)
    
    Increase = Increase + 1

Increase is Long
InboxMeterP() is String

I have discovered this doesn't work. If I replace (increase) with 1.... 16 etc it works fine, but I would like to step through.

Any ideas?

Thanks

 

[pr2-eugin]

So at what point are you thinking about changing the DB Structure?

The above code makes little sense, show a bit more of code you have. If you have no code, can you please give another shot at explaining the problem?

 

[Acropolis]

The structure will be changing the next few months, its a load of rubbish and needs rebuilding.... Something we inherited, its not by design.

I have a list of serial numbers for meters, which are in a meter table, which are assigned to sites. What I need to do, is check that the serial number that comes in from the device matches the serial number of a meter assigned to the site for which the device is at.

I have created an array of the meters at the site, and what I wanted to do was loop through the inputs InboxMeterSerialP1-P8 and InboxMeterSerialD1-D8 and check them against the values in the array. If they match great, carry on to the next check, if they don't, flag as not matching, then carry on to the next check that I carry out.

From the qry you helped me with yesterday, I have all the fields for the record is rs2

Set rs2 = db.OpenRecordset("SELECT * FROM (SELECT MAX(InBoxRecNo) AS MaxInBoxRecNo FROM inboxdata_v2 WHERE InboxOboxRecno = " & OboxRecNo & " " & _
                           "AND InboxTransmitTime < " & Format(DateToCheck + 1, "\#mm\/dd\/yyyy\#") & ") As tempTbl " & _
                           "INNER JOIN inboxdata_v2 ON inboxdata_v2.InBoxRecNo = tempTbl.MaxInBoxRecNo")

For P1 to P8 only the number changes on the end, so I wanted to increase the number by 1 for each loop. When it has looped for the number of meters there are for the site stop.

Hope that makes a bit more sense.

 

[pr2-eugin]

Your design makes a lot more sense to you. I have no clue what InBoxMeterSerialP or InBoxMeterSerialD means. Just adding them to this post, did not help. It actually made me Anyway,

Dim iCtr As Long, inBoxArr(1 To 8) As String

For iCtr = 1 To 8
    inBoxArr(iCtr) = rs2.Fields("InboxMeterSerialP" & iCtr)
Next

Something along the above lines should do the trick !

 

[namliam]

You CAN do something like:
rs2.Fields("InboxMeterSerialP" & Increase)

or a little more simple if you know the exact column number(s)
rs2.Fields(Increase)

But yeah 16 columns of the same data, that DOES scream bad design

Edit: Paul "beat" me to it as always

 

[namliam]

Dunno if Paul gave you that SQL, but I am sure Paul knows the importance of keeping code readable.... Your SQL is far from that TBQH

Set rs2 = db.OpenRecordset(" SELECT * " & _
                           " FROM   ( SELECT MAX(InBoxRecNo) AS MaxInBoxRecNo " & _
                                    " FROM inboxdata_v2 " & _
                                    " WHERE InboxOboxRecno = " & OboxRecNo & " " & _
                                      " AND InboxTransmitTime < " & Format(DateToCheck + 1, "\#mm\/dd\/yyyy\#") & ") As tempTbl " & _
                           " INNER JOIN inboxdata_v2 ON inboxdata_v2.InBoxRecNo = tempTbl.MaxInBoxRecNo")

 

[Acropolis]

Thanks guys got it working as I wanted now.

The code was a direct copy and paste I have to say, but I will be going through once I have things working and tidying up and making sure the comments are good and right.

The design is a nightmare to work with, the names along are confusing as anything and it desperately needs sorting out. That is the next project coming along to re-do it all, which will be a nightmare. I am quite new to DB's and even more so Access, and I have spotted several problems within the set up that have caused issues along the way, and it was designed by a person who "knew what they were doing".... yeah

Thanks again guys for the help, very much appreciated.