Replies

I am revisiting this issue and have a particular problem. I use the function SelectedClients() described above to create a string of CLIENT IDs. that looks like this.

SelectedClients = “12345W Or 23455C Or 34567T”

I use this statement to open the report.

DoCmd.OpenReport stReportName, acViewPreview, ,[CLIENT]=SelectedClients()

And I always get the following error message.

Microsoft Access can’t find the field ‘I’ referred to in your expression.

I can’t figure out what field is being refered to in the error message. Can you tell from looking at this what the problem might be?

First of all, thanks for your suggestions.
What is the best way to pass the users selections from this multi-select list box as parameters to the query that is the record source for a report? Below is info on my application.

The form is called frmSelectClients
It contains a listbox called lstSelectClients where users select one or more clients.
The form also has a command button called cmdOpenReportSummary used to open a report called rptClientSummaryReport.

The query (qrySelectReportData) below is the record source for the report
I have tried the following with no luck.

Field: ClientNo, some other fields…
Table: qrySelectByRecoveryPctSvcLine
Total: GroupBy
Sort:
Show: (checked)
Criteria: [forms]![Performance Analysis Client Selection Form]![lstSelectClients] <—WHAT GOES HERE?

I have also tried writing a function called SelectedClients() that returns a string of client numbers "Or"d together like this "12345W" Or "23234C" Or "44322S" and tried using this as the query parameter as follows:
Field: ClientNo
…
Criteria: SelectedClient()
It works well if I select one client from the list but returns no data when multiple selections are made.

The code for the function follows:

Public Function SelectedClients() As String
'create filter for selected records
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intStringLength As Integer
Dim strAster As String 'I tried to use this to pass a wildcard to the query to select all records
Dim strQuote As String 'I used this instead of enclosing quotation marks inside of quotation marks

Set ctlSource = Forms![Performance Analysis Client Selection Form]!lstSelectClients
'evaluate number of items on list to make the filter query
'processes more than one item
strQuote = Chr(34)
For intCurrentRow = 0 To ctlSource.ListCount – 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & strQuote & " Or "& strQuote
End If
Next intCurrentRow

'remove the last "Or" from the search string
intStringLength = Len(strItems)
If intStringLength = 0 Then
strAster = Chr(42)
strItems = strAster
Else
strItems = Left(strItems, (intStringLength – 4))
End If
'pass value to function
SelectedClients = strItems

'Reset destination control's RowSource property.
Set ctlSource = Nothing

End Function

Or, is it better to use an SQL statement as the report data source and where would I put that SQL Statement? Would it be in the "FilterName" part of the DoCmd.OpenReport line or some other place?
As always, your suggestions are appreciated.

Thanks Charlotte and Mark. I have read posts in this forum related to distributing run time versions to users with other versions of Access on their pc. I have come to find out that of the copies that we need to distribute, some have Access 97 and one Access 2000. I am investigating known issues.
Thanks for your help.

We need to distribute databases to customers who have older versions of Access. We believed that MDE files would run on PCs regardless of wheather the user had Access or not. Can you tell me more about the differences in a VB executable and and MDE? We have done some development in Access XP (2002 format) and need to distribute it to a customer who has an older version of Access (97). I made an MDE file thinking that it would run on the user’s pc and it did not. He got the “…unrecognized format…” message. I then thought about making a Visual Basic Executable that connects to the Access db that the user could run. Before I begin the process of making the user interface in Visual Basic, I want to find out if this is a workable solution. Also, we tried to convert the XP back to Access97 and have some debugging issues related to Microsoft Graph.

Any thoughts?
Thanks,
aap2

Can you point me to some of the resources that detail the known issues when going from Office 97 to XP or specifically from Access97 to AXP? I have found some articles in MS Knowledge base but am interested in any other resources.
Many thanks,
aap2

Thank you all, problem solved.

Thanks for your assistance. Do you mean that in the underlying queries I should add a field like this?

Field: StartDate:[forms]![frmSelectData]![Combo6]
and
Field: EndDate:[forms]![frmSelectData]![Combo8]
Is this what you mean when you said to add them to the query parameters? And are you referring to the underlying queries or the final crosstab or both?

Thanks,
aap2

I have changed the query Total line to “Where” as you suggested and had the same result.

In the query design grid of the query that does not work, the only reference to date is as follows:
Field: Expr1: “Qtr ” & Format([qryComparisonSearchStep1].[Date],”q”)
Table:
Total: Group By
Crosstab: Column Heading

This query is based on another query in which my combo boxes are referenced as:
Field: Date
Table: tblTransactionDates
Total: Group By (I changed this to Where, as you suggest and had the same result)
Sort:
Criteria: Between [forms]![frmSelectData]![Combo6] And [forms]![frmSelectData]![combo8]

Pat,

Option Explicit is there and I will create a recordset. Thanks for your assistance. I’ll let you know how it works out.
aap2

Below is a more detailed description of what I am attempting.

The form has a combo box (cboSelectTable) where the user selects an item. The AfterUpdate event of the combo box is what I wish to use. The value of the item selected will determine which table will be searched in the query. The combo box RowSource Type is “Table/Query” and the
SELECT DISTINCTROW tblClientLookUpTablesIndex.TableName, Row Source looks like this
tblClientLookUpTablesIndex.TableDescription FROM tblClientLookUpTablesIndex;
the TableDescription field contains valid table names.

cboSelectTable_AfterUpdate()
‘create variable to hold the valid table name
dim mstrLUTable as string
Here is the part I am wondering about. How do I make this assignment so that the variable
“mstrLUTable” holds a valid table name such as “tblLU0301”. And, can you use a variable name in a SQL statement?

‘assign the value of the users selection to the variable name
????mstrLUTable = cboSelectTable.value or something like this????

Then the varialbe mstrLUTable is filled into this SQL Statement.

SELECT mstrLUTable.Group, mstrLUTable.CBO, mstrLUTable.DIVISION, [LU Program Info].[SERVICE LINE], mstrLUTable.[HOSPITAL ID], mstrLUTable.[HOSPITAL NAME], [LU Program Info].[PLACEMENT NO], [LU Program Info].[PLACEMENT DATE], [LU Program Info].STATE, Stats.[Client No], Stats.[Client Name], Sum(Stats.[MTD # Listed]) AS [SumOfMTD # Listed], Sum(Stats.[MTD $ Listed]) AS [SumOfMTD $ Listed], Sum(Stats.[MTD $ Collected]) AS [SumOfMTD $ Collected], Sum(Stats.[MTD $ Fees]) AS [SumOfMTD $ Fees], Sum(Stats.[YTD $ Listed]) AS [SumOfYTD $ Listed], Sum(Stats.[YTD $ Collected]) AS [SumOfYTD $ Collected], Sum(Stats.[YTD $ Fees]) AS [SumOfYTD $ Fees], Avg(Stats.Age) AS AvgOfAge, Stats.Date
FROM (mstrLUTable LEFT JOIN Stats ON mstrLUTable.[CLIENT NO] = Stats.[Client No]) LEFT JOIN [LU Program Info] ON mstrLUTable.[CLIENT NO] = [LU Program Info].[CLIENT NO]
GROUP BY mstrLUTable.Group, mstrLUTable.CBO, mstrLUTable.DIVISION, [LU Program Info].[SERVICE LINE], mstrLUTable.[HOSPITAL ID], mstrLUTable.[HOSPITAL NAME], [LU Program Info].[PLACEMENT NO], [LU Program Info].[PLACEMENT DATE], [LU Program Info].STATE, Stats.[Client No], Stats.[Client Name], Stats.Date
ORDER BY [LU Program Info].[SERVICE LINE];

Once the SQL statement is built, based on the user selection, I want to use that statement in the On Click event of a command button.

Private Sub cmdRunSearch_Click()
On Error GoTo Err_cmdRunSearch_Click

Dim stSQLstatement As String

stSQLStatement = the sql statement above

Run the search

End Sub

Many thanks,
aap2

Thanks for your help. The macro works. I had one other question. Because you declared this as “Public Sub…” does that mean that I can run it from a command button on a form or toolbar? I would like to build this into a command button that cleans up the monthly dataset.

aap2

These are two very good questions. As we pull these data from one system, it is consistent that the last record is not #NA. However, to be safe, I suppose I should put something in place that evaluates the last record to be sure.
Thanks,
aap2

Thank you for your suggestion. Can you educate me a little here please? Have a look at my comment lines and please tell me if I understand what you are doing here.
Many thanks,
aap2

Public Sub CleanUp()
Dim oCell As Range
Dim I As Long, J As Long, lRowMax As Long

‘ I think this line tells me the range of cells that will be operated on. A1 to A65536.
‘ Is 65536 an excel default for the maximum number of rows that can be in
‘ a column or did you pick a high number hoping to include the end of data?
‘ does the .End(xlUp) make the program proceed from the bottom up?
lRowMax = Worksheets(“Sheet1”).Range(“A65536”).End(xlUp).Row

‘ start at the A1 cell and do some stuff
With Worksheets(“Sheet1”).Range(“A1”)

‘ set up an iterative process to systematically go through each cell one at a time and check
‘ for “IsError”. By the way, why the -1 on the lRowMax?
For I = 0 To lRowMax – 1

‘ IsError would indicate an empty cell I think.
If IsError(.Offset(I, 0)) Then

‘ if an empty cell is found, the IsError is true and this second search happens.
For J = I + 1 To lRowMax – 1
If Not IsError(.Offset(J, 0)) Then

‘ if this is not an empty cell, check to see if this is a valid hospital ID.
If Left(.Offset(J, 0), 1) = “1” Then

‘ if it is a valid hospital ID, the set the value in the previously found cell .Offset(I,0) to the
‘ value of the valid hospital ID .Offset(J,0). this keeps looping around until lRowMax – 1 is reached.
.Offset(I, 0) = .Offset(J, 0)

‘ exit the inner for statement that looped around until lRowMax – 1 was reached
Exit For

‘ exit the If statement that looked for an empty cell
End If
End If
Next J
End If
Next I
End With
End Sub

yes, I believe this was the problem.
thank you.
aap2

Charlotte,
Thanks. I am not running either MS Repository/Visual Studio or MS Team Manager. I did close some of the other applications that were running in the background and ran the query again and it worked.

aap2