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

x
?
Solved

access vba code for separating lists

Posted on 2013-01-25
11
Medium Priority
?
744 Views
Last Modified: 2013-01-26
I have a text field which causes a lookup from a list;

eg the list is apples, oranges, pears, grapes.

What I want to do is make a report out of that list so that:

if the chosen list item is oranges, the report is able to make a list of the other items in the list.

How this would work:
The Report would say "Thank you for buying Oranges"

"you are now entitled to a discount against:

apples, pears, and grapes

So its a question of how the list can be split to display the exceptions.
0
Comment
Question by:topUKlawyer
[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
  • 5
  • 4
  • 2
11 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 38818254
-->> apples, pears, and grapes

If you can make do without the 'and', a simple solution might be (assuming a textbox for the chosen item):


dim strMessage as string
strMessage = "Thank you for buying " & me.txtChosen & ".  you are now entitled to a discount against: " & vbcrlf  & replace (replace(strList,  me.txtChosen  & "," & ""), Me.txtChosen, "")

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818293
This will actually split the string and format the message with the AND before the last item and commas between the rest:

Function getMessage(strList As String, strChosen As String) as string
    Dim s() As String
    Dim sTemp As String
    Dim sListTemp As String
    Dim I As Integer
    
    sTemp = "Thank you for purchasing " & strChosen & "! You are now entitled to a discount on: " & vbCrLf
    s = Split(strList, ",")
    
    For I = 0 To UBound(s)
        If Trim(s(I)) = Trim(strChosen) Then
            s(I) = ""
        Else
            Select Case I
                Case UBound(s) - 1
                    s(I) = Trim(s(I)) & " AND"
                Case UBound(s)
                    s(I) = Trim(s(I)) & "."
                Case Else
                    s(I) = Trim(s(I)) & ","
            End Select
        End If
    Next
    sListTemp = Replace(Join(s, " "), "  ", " ")
                    
    
    getMessage = sTemp & sListTemp
    
    
End Function

Open in new window


Call it like this:

msgBox getmessage("Apples, Oranges, Bananas, Pears","Oranges")

Open in new window


or

dim strList as string
dim strChosen as string

strList = "Apples, Oranges, Bananas, Pears"
strChosen = "Oranges"

msgBox getmessage(strList,strChosen)

Open in new window

0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 1000 total points
ID: 38819090
Are you familiar with using not-in outer-join queries?  You can easily query for the items that aren't the one selected. You could use the query in your report directly or in a subreport.

Wrote a paper on it entitle:
Microsoft Access Outer Join Query: Finding All Records in One Table but Not Another and Creating "Not In" Queries
http://www.fmsinc.com/MicrosoftAccess/query/outer-join/index.html

It's part of our Microsoft Access Query Help Center: http://www.fmsinc.com/MicrosoftAccess/query/help-center.html

Hope this helps.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:topUKlawyer
ID: 38819367
Thanks LukeChung-

My cup is overflowing with help.

Will look at this too.
0
 

Author Comment

by:topUKlawyer
ID: 38820747
LukeChung

I have looked at your article but I cant see how that would work for me if I have one list item in a field (as opposed to a table) selected and I want to group together in a Report the ones that have not been selected.

Mbizup, is there anything in what LukeChung is saying? Your additional comment would be welcome.
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 38820793
If it's one item, you can simply have a query that retrieves all records except that item.

No need for an outer join in that case. I thought you had more than one selection that you needed to exclude.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38820852
<<
Mbizup, is there anything in what LukeChung is saying?
>>

Absolutely.

From the way you posted your question, I assumed you wanted/needed to work with a comma delimited list.  The code I posted does do that (tested).

However, using a table and a query gives you more flexibility.
0
 

Author Comment

by:topUKlawyer
ID: 38821589
Thanks guys thts helpful but I dont know how to separate using a query ad the current stored value of the textfield




any more help please?
0
 

Author Comment

by:topUKlawyer
ID: 38821653
OK progress.

I have a query which shows the record and this shows the right field for apples
call the field slected fruit type if you like and another filed in the query which shows fruit type 2
which is a list of all the fruit.

now I gather hopefully correctly that I have to creat an outer join and that will give me a list of those items not in the slected fruit list ie the remainder.

HOWEVER when I join them with an outer join it says that the query is ambiguous and I should create a first query but I Have no idea wat to do to run or make that.

help please
0
 

Author Comment

by:topUKlawyer
ID: 38821710
I have now worked out how to do a Not in Outer join. thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38822034
Wow! Different time zones!  It looks like you got a lot done while the US East Coast was sleeping.

Glad you worked it out...
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

704 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