Solved

MS Access convert a bitwise string into multiple rows

Posted on 2016-08-04
5
48 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 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

634 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