Form Based Cross Tab Query

  • Thread starter Thread starter Allan
  • Start date Start date
A

Allan

I am trying to run a crosstab query that uses parameters
from a form list box. Upon running the query I recive the
error "Microsoft Jet database engine does not
recognize 'xxx...' as a valid field name or expression."

Does this approach not work using a crosstab query? I
used the expression builder to assure that the text was
enter correctly for the reference to the form field and
the context is fully qualified. I followed the
examplefrom the following MS article:
http://office.microsoft.com/assistance/preview.aspx?
AssetID=HA011170771033&CTT=98

TIA,
Allan
 
Dear Allan:

In a crosstab query, the names of columns can be variable. If so,
then you cannot bind columns to controls since you don't know the
names of the columns in advance. In fact, you wouldn't necessarily
know the number of columns in advance - that can change.

However, you can reference columns as an array, find out their names
in code, and bind them at run time. This would be used to alter the
number of columns in your list box and perhaps the column headings
there too, if you want to display them.

Anyway, that's the most general information about the technique.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Your subject suggests your record source of your form is a crosstab but you
text suggests your crosstab gets criteria from a form. If the later is true,
you must select Query|Parameters and enter the exact control references and
data types ie:
Forms!frmDate!txtStartDate Date/Time
Forms!frmDate!txtEndDate Date/Time
 
The form is based on a table "tblCompanyDesc" which is
one of the tables used in the Query. I tried to set the
query criteria to have the value(s) from "forms!
frmCompanySelect!Company_List" by putting that exact
phrase in the criteria field for tblCompanyDesc!
CompanyName"

Allan
 
Thanks Duane,

One more hurdle to tackle - Unfortunately the list box
returns a null value to the query. How do you pass the
value(s) (I prefer to use a MultiSelect Listbox) to the
query?

Allan
 
I must have missed an earlier specification that stated this was a
multi-select list box. You would need to write code to loop through the
selected items in your list box and dynamically build your query sql.
 
I may not have been crystal clear in my original post
(not understanding how important that might be). I need
this to ultimately run reports. How do I structure
Dynamic SQL in such a way that can be utilized by a
report?

Allan
 
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a
list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )
 
Duane,

Thanks for the persistance. It was exactly what was
looking for.

Allan
-----Original Message-----
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a
list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )


--
Duane Hookom
MS Access MVP
--

I may not have been crystal clear in my original post
(not understanding how important that might be). I need
this to ultimately run reports. How do I structure
Dynamic SQL in such a way that can be utilized by a
report?

Allan stated
this was a which
is set
the
Click to expand...


.
Click to expand...
 
Back
Top