SELECT QUERY to REPORT / DATA VIEW within ACCESS

  • Thread starter Thread starter SUB
  • Start date Start date
S

SUB

does anyone know a way of doing this in Access.

If I perform a select query in VBA..i.e.

SELECT * FROM DATA..

is there a way in Access 2000 or higher to then send these
results into a report view / data view, this would then
give me greater control over the data's criteria

cheers in advance for any ideas

SUB
 
Save your query and use it as the source for the report or form.... You can
then select any fields you want to show.

Damon
 
Yes, have already done that and works fine up until you
try the following...

when the criteria on the query references a text box on a
form and the criteria passed are like the examples below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't work, just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
 
You would need
[forms]![myform].[level]=A Or [forms]![myform].[level]=B ...
Or
[forms]![myform].[level] In ("A" ,"B", "C")

--
Duane Hookom
MS Access MVP


SUB said:
Yes, have already done that and works fine up until you
try the following...

when the criteria on the query references a text box on a
form and the criteria passed are like the examples below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't work, just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
-----Original Message-----
Save your query and use it as the source for the report or form.... You can
then select any fields you want to show.

Damon




.
Click to expand...
Click to expand...
 
Arrggghh, so close....

this works fine in query
criteria = In ("A", "B", "C") but I think I am missing
something on this, my query now reads.
Criteria = In ([forms]![myform].[level])

and then the text box on the form is used for the criteria
detail, but I dont think this is correct, any further
ideas???

cheers SUB
-----Original Message-----
You would need
[forms]![myform].[level]=A Or [forms]![myform].[level] =B ...
Or
[forms]![myform].[level] In ("A" ,"B", "C")

--
Duane Hookom
MS Access MVP


Yes, have already done that and works fine up until you
try the following...

when the criteria on the query references a text box on a
form and the criteria passed are like the examples below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't work, just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
-----Original Message-----
Save your query and use it as the source for the report or form.... You can
then select any fields you want to show.

Damon

does anyone know a way of doing this in Access.

If I perform a select query in VBA..i.e.

SELECT * FROM DATA..

is there a way in Access 2000 or higher to then send these
results into a report view / data view, this would then
give me greater control over the data's criteria

cheers in advance for any ideas

SUB


.
Click to expand...
Click to expand...


.
Click to expand...
 
You would have to modify the SQL since as you see the reference to the form
control will not work in this instance. I'm not sure if this will meet your
needs or not but this is some information I have in an FAQ on a related web
site:
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


SUB said:
Arrggghh, so close....

this works fine in query
criteria = In ("A", "B", "C") but I think I am missing
something on this, my query now reads.
Criteria = In ([forms]![myform].[level])

and then the text box on the form is used for the criteria
detail, but I dont think this is correct, any further
ideas???

cheers SUB
-----Original Message-----
You would need
[forms]![myform].[level]=A Or [forms]![myform].[level] =B ...
Or
[forms]![myform].[level] In ("A" ,"B", "C")

--
Duane Hookom
MS Access MVP


Yes, have already done that and works fine up until you
try the following...

when the criteria on the query references a text box on a
form and the criteria passed are like the examples below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't work, just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
-----Original Message-----
Save your query and use it as the source for the report
or form.... You can
then select any fields you want to show.

Damon

message
does anyone know a way of doing this in Access.

If I perform a select query in VBA..i.e.

SELECT * FROM DATA..

is there a way in Access 2000 or higher to then send
these
results into a report view / data view, this would then
give me greater control over the data's criteria

cheers in advance for any ideas

SUB


.
Click to expand...


.
Click to expand...
Click to expand...
 
the only bit I'm struggling with is getting the Access
Query to accept a criteria that is based on a form else
where, it works fine on single items i.e. if item="c"
then..

but when you try entering multiples into the textbox that
feeds the query, Access no longer recognises it as a quey.

ok, below is an example that should explain the problem
properly.

if you enter a criteria in the query itself and save it,
of code= "E" OR "C" OR "F" OR "G"
this works fine.

if you enter in a form!textbox the same criteria as above
and have the query look to the textbox, it no longer knows
what to do with the query and returns no rows?????

I have tried all variations on this that I can think of
but the query just doesn't recognise criteria which has
been entered from an external source.


cheers for the help so far... I appreciate it.

SUB

-----Original Message-----
You would have to modify the SQL since as you see the reference to the form
control will not work in this instance. I'm not sure if this will meet your
needs or not but this is some information I have in an FAQ on a related web
site:
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


Arrggghh, so close....

this works fine in query
criteria = In ("A", "B", "C") but I think I am missing
something on this, my query now reads.
Criteria = In ([forms]![myform].[level])

and then the text box on the form is used for the criteria
detail, but I dont think this is correct, any further
ideas???

cheers SUB
-----Original Message-----
You would need
[forms]![myform].[level]=A Or [forms]![myform].[level] =B ...
Or
[forms]![myform].[level] In ("A" ,"B", "C")

--
Duane Hookom
MS Access MVP


Yes, have already done that and works fine up until you
try the following...

when the criteria on the query references a text box
Click to expand...
on
a
form and the criteria passed are like the examples below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't work, just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
-----Original Message-----
Save your query and use it as the source for the report
or form.... You can
then select any fields you want to show.

Damon

message
does anyone know a way of doing this in Access.

If I perform a select query in VBA..i.e.

SELECT * FROM DATA..

is there a way in Access 2000 or higher to then send
these
results into a report view / data view, this would then
give me greater control over the data's criteria

cheers in advance for any ideas

SUB


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
You have a couple options:
-use code to change the sql of the saved query
Currentdb.QueryDefs("YourQuery").SQL = "SELECT...WHERE Code IN (" & ....&
") ;"
-use code to build a where clause to use in the OpenReport code
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


SUB said:
the only bit I'm struggling with is getting the Access
Query to accept a criteria that is based on a form else
where, it works fine on single items i.e. if item="c"
then..

but when you try entering multiples into the textbox that
feeds the query, Access no longer recognises it as a quey.

ok, below is an example that should explain the problem
properly.

if you enter a criteria in the query itself and save it,
of code= "E" OR "C" OR "F" OR "G"
this works fine.

if you enter in a form!textbox the same criteria as above
and have the query look to the textbox, it no longer knows
what to do with the query and returns no rows?????

I have tried all variations on this that I can think of
but the query just doesn't recognise criteria which has
been entered from an external source.


cheers for the help so far... I appreciate it.

SUB

-----Original Message-----
You would have to modify the SQL since as you see the reference to the form
control will not work in this instance. I'm not sure if this will meet your
needs or not but this is some information I have in an FAQ on a related web
site:
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


Arrggghh, so close....

this works fine in query
criteria = In ("A", "B", "C") but I think I am missing
something on this, my query now reads.
Criteria = In ([forms]![myform].[level])

and then the text box on the form is used for the criteria
detail, but I dont think this is correct, any further
ideas???

cheers SUB
-----Original Message-----
You would need
[forms]![myform].[level]=A Or [forms]![myform].[level]
=B ...
Or
[forms]![myform].[level] In ("A" ,"B", "C")

--
Duane Hookom
MS Access MVP


message
Yes, have already done that and works fine up until you
try the following...

when the criteria on the query references a text box on
a
form and the criteria passed are like the examples
below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't work, just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
-----Original Message-----
Save your query and use it as the source for the report
or form.... You can
then select any fields you want to show.

Damon

message
does anyone know a way of doing this in Access.

If I perform a select query in VBA..i.e.

SELECT * FROM DATA..

is there a way in Access 2000 or higher to then send
these
results into a report view / data view, this would
then
give me greater control over the data's criteria

cheers in advance for any ideas

SUB


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
BINGO!!!!!!!!!

THATS THE BABY.........


I thank you Kind Sir.....


cheers

SUB
-----Original Message-----
You have a couple options:
-use code to change the sql of the saved query
Currentdb.QueryDefs("YourQuery").SQL = "SELECT...WHERE Code IN (" & ....&
") ;"
-use code to build a where clause to use in the OpenReport code
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


the only bit I'm struggling with is getting the Access
Query to accept a criteria that is based on a form else
where, it works fine on single items i.e. if item="c"
then..

but when you try entering multiples into the textbox that
feeds the query, Access no longer recognises it as a quey.

ok, below is an example that should explain the problem
properly.

if you enter a criteria in the query itself and save it,
of code= "E" OR "C" OR "F" OR "G"
this works fine.

if you enter in a form!textbox the same criteria as above
and have the query look to the textbox, it no longer knows
what to do with the query and returns no rows?????

I have tried all variations on this that I can think of
but the query just doesn't recognise criteria which has
been entered from an external source.


cheers for the help so far... I appreciate it.

SUB

-----Original Message-----
You would have to modify the SQL since as you see the reference to the form
control will not work in this instance. I'm not sure if this will meet your
needs or not but this is some information I have in an FAQ on a related web
site:
I hate writing the same code over and over like the
Click to expand...
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
Click to expand...
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
Click to expand...
describing
the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on
Click to expand...
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) & "
Click to expand...
In
("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim &
Click to expand...
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
Click to expand...
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


Arrggghh, so close....

this works fine in query
criteria = In ("A", "B", "C") but I think I am missing
something on this, my query now reads.
Criteria = In ([forms]![myform].[level])

and then the text box on the form is used for the criteria
detail, but I dont think this is correct, any further
ideas???

cheers SUB
-----Original Message-----
You would need
[forms]![myform].[level]=A Or [forms]![myform]. [level]
=B ...
Or
[forms]![myform].[level] In ("A" ,"B", "C")

--
Duane Hookom
MS Access MVP


message
Yes, have already done that and works fine up
Click to expand...
until
you
try the following...

when the criteria on the query references a text
Click to expand...
box
on
a
form and the criteria passed are like the examples
below.

[forms]![myform].[level]=A this works fine
[forms]![myform].[level]=B this works fine
but when you try to pass combinations it fails?????
[forms]![myform].[level]=A OR B OR C doesn't
Click to expand...
work,
just
bring back no data??????

do you know why, this has been bugging me for ages..

cheers SUB
-----Original Message-----
Save your query and use it as the source for the report
or form.... You can
then select any fields you want to show.

Damon

message
does anyone know a way of doing this in Access.

If I perform a select query in VBA..i.e.

SELECT * FROM DATA..

is there a way in Access 2000 or higher to then send
these
results into a report view / data view, this would
then
give me greater control over the data's criteria

cheers in advance for any ideas

SUB


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top