Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access convert a bitwise string into multiple rows

Posted on 2016-08-04
5
Medium Priority
?
52 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
Dale Fye earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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