Replies

I only have access 97 loaded on my PC so couldn’t look at your database, but have tried to provide you with an answer anyway.

Look at the following code.

Private Sub butBuildFilter_Click()
    
    Dim ctl As Control
    Dim varItm As Variant
    Dim MyCriteria As String
    Dim ArgCount As Integer
    'initialise values
    MyCriteria = ""
    ArgCount = 0
    
    Set ctl = lstData
    For Each varItm In ctl.ItemsSelected
        If Not ArgCount = 0 Then ' previous items have been added to the list
            'Substitute your field name for the word FIELDNAME
            MyCriteria = MyCriteria & " or [Fieldname] = '"  _
                               & ctl.ItemData(varItm)  & "'"
            ArgCount = ArgCount + 1
        Else
            'Substitute your field name for the word FIELDNAME
            MyCriteria = "[Fieldname] = '"  _
               & ctl.ItemData(varItm) & "'" 'first item added.
            ArgCount = ArgCount + 1
        End If
    Next varItm
    
    'you now have a where clause that you can use to 
    'open a report, form etc, or add to a SQL
    'string for whatever use.  Or a string that you can 
    'use as a filter.
    MsgBox MyCriteria

End Sub

Your hyperlink asking if I’m the same Stewart Tanner doesn’t go anywhere, so I can’t answer that.

Edited by Charlotte to eliminate horizontal scrolling

I don’t have access 2000 but the 97 method would be something like this.
(straight from the help)

The following example prints the value of the bound column for each selected row in a Names list box on a Contacts form. To try this example, create the list box and set its BoundColumn property as desired and its MultiSelect property to Simple or Extended. Switch to Form view, select several rows in the list box, and run the following code:

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm
End Sub

I’m glad you worked it out. I’ve been off work so hadn’t seen your posts. Sorry for the delay in responding.

Use of the parameters in a query is exactly what I did but the paramater was pointing to the field on the menu, rather than asking for the value to be entered.

You can build an expression in a query. Use the syntax

ObjectionNumber: [ObjectorID] & “-” & [ObjectionID] & “/” & [PlanField]

where objectorID = 10, ObjectionID = 0001 and PlanField = AZ324 this expression would return
10-0001/AZ324

or

ObjectionNumber: [ObjectorID] & [ObjectionID] & [PlanField]

where objectorID = 10, ObjectionID = 0001 and PlanField = AZ324 this expression would return
100001AZ324

The expression wil have the name/label “ObjectionNumber”. The colon ends the naiming section.

Each field that you want can be concatenated by using the ampersand “&” between fields. To add formating characters between fields (if required) use the ampersand with the character emclosed in double quotes.

What help in particular are you looking for? Table Design, Query?

Um, have you tried the obvious? delete one of the fields and re create it. Does it now work?

Check that there is not a conflict between field name and control name.

If it works perfectly in another form the implication is that you have performed a cut and paste without completing customisation of the copy.

Use a combo box for the control with the rowsource based on a distinct list of previously enetered values.

on the main menu change the rowsource for the combo box from

SELECT DISTINCTROW [Problems].[Chapter] FROM [Problems];

to

SELECT DISTINCT [Problems].[Chapter] FROM [Problems];

This will give you 1 instance of 1 and 1 instance of 3 etc.

Glad to help.

I’ve deleted your forms and replaced them with a single form called generic. I’ve added a new table called menu options. In the table add the form description, ( i’ve added you existing forms) and the query you wish the form to be based on.

There is a new form called menu, ope it and see the options available to you. Select the form from the list box. Optionally select the Chapter from existing entries in the table, type a new chapter number if it doesn’t exist, or leave the chapter blank to view all.

Click the open form button.

Hope this helps

what about using

Amount: Format(nz([SumOfAmount],0),”””A$””0.00″)

where A$ is for Aussie Dollars. I’m sorry I don’t know what the symbol is for Israeli Shekels.

“select distinct” in your query to remove the duplicate entries.

or
‘select the report
DoCmd.SelectObject acReport, stDocName, True
‘Print the first page
DoCmd.PrintOut , 1, 1

Now i’m confused, I assumed that the primary purpose of this was to create a graphical represenation of the sumary data. Now I’m starting to think that you are using this as a supporting represenation within another report. Is this correct?

If you are looking to show the 5 lines as a primative bar chart in the report header or footer, why not create the report based on the summary graph and just insert it as a subreport? This has the advantage that you don’t need to add new text boxes or anything else via vba if the number of records reurned by the query increases.

Your welcome.

Also when I make SumOf Amount with the -nz- function the special format that
I had for the amount, that showed the sign for Israeli Shekels got lost. I
tried pasting it in the format several times but it doesn’t seem to want to
register it. I just re-checked yours and the SumOfAmount also lost its $
sign.

Change the format in the field properties to currency, or the format of the field in the form/report.

Alternately you can add a parameter to the query under the date field eg
forms!MySummaryForm!cboSelectMonth. Then when you have changed the date in
the combo box you can requery the control/subform etc that is displaying the
query output to obtain the correct value.

I didn’t quite understand this… I am a real NEWBIE and need IDIOT
language.

Have alook at query 3 in attached. Run it from the form using the command button, not Idiot language, just something that you can understand. Don’t worry we were all beginners once.

it would be nice
if they would come up as $0.00… but maybe I am asking to much

It is not to much to ask. Query 2 or 3 do this. Look at the Amount field. The NZ function is wonderful.

rather than

NewJobno = NZ( DMax(“jobno”,”tblJobs”,”jobDate=” & txtDate),0) + 1

Use

NewJobno = NZ( DMax(“jobno”,”tblJobs”,”jobDate=#” & format(txtDate,”mm/dd/yyyy”) & “#”),0) + 1

The # tells access that it is a date value. The format forces american date format as that is what VBA understands.

After all the american date format is standard all accross the world, sarcasm off now.