Solved

MS Access convert a bitwise string into multiple rows

Posted on 2016-08-04
5
43 Views
Last Modified: 2016-10-08
Ok,
I have data like so
JobID Long
ProvincialStamps Text(14)


ProvincialStamps is a string of 1's and 0's: "1100000000000" for example.
This is all very nice and I can do beautiful things like

If InStr(3, StampVal, "1") = "3" Then
   'do some stuff when the third character is a 1
End if


and

Dim myboolean As Boolean
myboolean = False
If InStr(11, StampVal, "1") = "11" Then
    myboolean = True
End If
If InStr(12, StampVal, "1") = "12" Then
    myboolean = True
End If
If InStr(13, StampVal, "1") = "13" Then
    myboolean = True
End If

If myboolean = True Then
   'do some stuff if any of 11-13 are a 1
end if


but now, sadly, I have to drive a report with one record for each place in the ProvincialStamps string.
So that involves transforming that string into 14 records.

What's a nice way of doing that?
0
Comment
Question by:Nick67
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 total points
ID: 41743247
Nick, the way I handle this is with a table and a function.  Call it tblBitWise give it a single field (intNumber) and 14 values 1-14.  Then create this function:

Public Function fnBitPosition(Stamp as string, Position as integer) as string

    fnBitPosition = Mid(Stamp, Position, 1)

End Function

Open in new window

Then use a query:

SELECT intNumber, fnBitPosition([ProvincialStamp], intNumber) as PositionVal
FROM tblBitWise
ORDER BY intNumber
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
ID: 41743292
I figured that a table would be needed.
And things are a bit more complex, because I need the data for a subreport.
So I need a PK/FK in the result for that to work.
So, I built a table, tempStampData
StampID Integer
JobId Long
Province Text(3)
Needed Yes/No

And code to empty/fill it

Private Sub BuildTempStampData()
Dim db As Database
Dim rs As Recordset
Dim x As Integer

Set db = CurrentDb

'kill existing records in tempStampData
DoCmd.SetWarnings False
db.Execute "DELETE tempStampData.* FROM tempStampData;", dbSeeChanges
DoCmd.SetWarnings True

Set rs = db.OpenRecordset("Select * from tempStampData;", dbOpenDynaset, dbSeeChanges)
Dim StampVal As String
StampVal = Nz(Me.ProvincialStamps, "0")
If StampVal = "0" Then
    Exit Sub
End If

With rs
    For x = 1 To Len(StampVal)
        .AddNew
        !StampID = x
        !JobID = Me.JobID
        If InStr(x, StampVal, "1") = x Then
            !needed = 1
        Else
            !needed = 0
        End If
        Select Case x
            Case 1
                !Province = "BC"
            Case 2
                !Province = "AB"
            Case 3
                !Province = "SK"
            Case 4
                !Province = "MB"
            Case 5
                !Province = "ON"
            Case 6
                !Province = "QC"
            Case 7
                !Province = "NB"
            Case 8
                !Province = "NS"
            Case 9
                !Province = "NL"
            Case 10
                !Province = "PE"
            Case 11
                !Province = "NT"
            Case 12
                !Province = "YT"
            Case 13
                !Province = "NU"
        End Select
        .Update
    Next x
End With
End Sub

Open in new window


I can drive a subreport with that data.
I was trying to build a string for a textbox, and while that can get done, with A2003 still in play, my formatting options left a lot to be desired.  With the subreport, I can break everything down to more controls and bold, underline, and change font size more granularly.

Anyone have a better/different suggestion?
0
 
LVL 26

Author Comment

by:Nick67
ID: 41826445
Busted bloody UI.
It wasn't abandoned as I had the last comment.
But no one else chimed in.

I'd accept Dale's comment as the answer, but there is now no UI to do so.
****Edit*****
F5 refresh made UI to accept previous comments available.
Now, clicking on any of them makes the bottom 'closing wizard pop up and then disappear.
****Edit****
A further refresh made the closing UI workable.
0
 
LVL 26

Author Closing Comment

by:Nick67
ID: 41834862
Full details of the approach are in my comment.
Dale suggested what was needed
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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