QueryDef Headache

  • Thread starter Thread starter Guy
  • Start date Start date
G

Guy

I have an EXISTING querydef in my Access 2000 database.
How do I write the code to open it and set a filter on it?

I normally use coded queries without bothering with Querydef at all but I
have a situation that is forcing me to use a saved Querydef.

the query in this querydef was written by a 3rd party, uses virtual fields
and barcodes which makes it simpler to just filter the saved query if I could
just access it.
 
Just write the code to query the query:
Dim strSql As String
strSql = "SELECT * FROM Query1 WHERE SomeExpression = True;"

That way you can add your criteria (filter expression) without changing the
original.
 
I believe I stated in my post that I could not do that as I normally would.
I intended to keep my question short and simple but evidently that was a bad
idea.
This query has so many things to work out it isnt worth the time:

SELECT Material_List.Material, Material_List.[material description],
Material_List!Material & " " & Material_List!Plnt & " " & Material_List!SLoc
AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin, [plnt]
& "-" & [plnt description] AS [Plant and description]
FROM Material_List
ORDER BY Material_List.bin, Material_List.Material, Material_List.[material
description];

First there are the 2 places where this appears: some text & " " & some more
text"

I was able to use: " some text & " & Chr(34) & " " & Chr(34) " & some more
text"
to get past that.

Then there's this section: "Material_List.bin, [plnt] & "-" & [plnt
description] AS [Plant and description]" which contains virtual a virtual
field "Plant and description" which errors out in code but works just fine in
the QueryDef.

Then there is the problem that the Barcode generated by the coded query does
not scan. The one in the QueryDef works just fine.

So I can spend a lot of time working out errors and MAYBE get it to work in
code or I can do something that SHOULD be easy: Just use the existing
operational QueryDef and forget it.

which brings me back to my original question:
How do you open an EXISTING querydef and send it a parameter. The examples
in the help file only deal with creating new querydefs.
 
strSQL = "SELECT Material_List.Material, Material_List.[material
description], " & _
"Material_List!Material & "" "" & Material_List!Plnt & "" "" &
Material_List!SLoc " & _
"AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin,
[plnt] " & _
"& ""-"" & [plnt description] AS [Plant and description] " & _
"FROM Material_List " & _
"ORDER BY Material_List.bin, Material_List.Material, " & _
"Material_List.[material description];"

although that can be simplified to

strSQL = "SELECT Material, [material description], " & _
"Material & "" "" & Plnt & "" "" & SLoc AS Barcode, " & _
"[plnt] & ""-"" & [plnt description] AS [Plant and description] " & _
"FROM Material_List " & _
"ORDER BY bin, Material, [material description]"

Essentially, unless you've declared the query to have a parameter, you can't
do anything with it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Guy said:
I believe I stated in my post that I could not do that as I normally would.
I intended to keep my question short and simple but evidently that was a
bad
idea.
This query has so many things to work out it isnt worth the time:

SELECT Material_List.Material, Material_List.[material description],
Material_List!Material & " " & Material_List!Plnt & " " &
Material_List!SLoc
AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin,
[plnt]
& "-" & [plnt description] AS [Plant and description]
FROM Material_List
ORDER BY Material_List.bin, Material_List.Material,
Material_List.[material
description];

First there are the 2 places where this appears: some text & " " & some
more
text"

I was able to use: " some text & " & Chr(34) & " " & Chr(34) " & some more
text"
to get past that.

Then there's this section: "Material_List.bin, [plnt] & "-" & [plnt
description] AS [Plant and description]" which contains virtual a virtual
field "Plant and description" which errors out in code but works just fine
in
the QueryDef.

Then there is the problem that the Barcode generated by the coded query
does
not scan. The one in the QueryDef works just fine.

So I can spend a lot of time working out errors and MAYBE get it to work
in
code or I can do something that SHOULD be easy: Just use the existing
operational QueryDef and forget it.

which brings me back to my original question:
How do you open an EXISTING querydef and send it a parameter. The examples
in the help file only deal with creating new querydefs.


Allen Browne said:
Just write the code to query the query:
Dim strSql As String
strSql = "SELECT * FROM Query1 WHERE SomeExpression = True;"

That way you can add your criteria (filter expression) without changing
the
original.
Click to expand...
Click to expand...
 
I sloved the problem with why it would not work in code:

"& "-" & needed the " on each side to be coded as Chr(34) which is a " simbol.
I now do not need to access the querydef

I would still like to know how to do so.

Douglas J. Steele said:
strSQL = "SELECT Material_List.Material, Material_List.[material
description], " & _
"Material_List!Material & "" "" & Material_List!Plnt & "" "" &
Material_List!SLoc " & _
"AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin,
[plnt] " & _
"& ""-"" & [plnt description] AS [Plant and description] " & _
"FROM Material_List " & _
"ORDER BY Material_List.bin, Material_List.Material, " & _
"Material_List.[material description];"

although that can be simplified to

strSQL = "SELECT Material, [material description], " & _
"Material & "" "" & Plnt & "" "" & SLoc AS Barcode, " & _
"[plnt] & ""-"" & [plnt description] AS [Plant and description] " & _
"FROM Material_List " & _
"ORDER BY bin, Material, [material description]"

Essentially, unless you've declared the query to have a parameter, you can't
do anything with it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Guy said:
I believe I stated in my post that I could not do that as I normally would.
I intended to keep my question short and simple but evidently that was a
bad
idea.
This query has so many things to work out it isnt worth the time:

SELECT Material_List.Material, Material_List.[material description],
Material_List!Material & " " & Material_List!Plnt & " " &
Material_List!SLoc
AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin,
[plnt]
& "-" & [plnt description] AS [Plant and description]
FROM Material_List
ORDER BY Material_List.bin, Material_List.Material,
Material_List.[material
description];

First there are the 2 places where this appears: some text & " " & some
more
text"

I was able to use: " some text & " & Chr(34) & " " & Chr(34) " & some more
text"
to get past that.

Then there's this section: "Material_List.bin, [plnt] & "-" & [plnt
description] AS [Plant and description]" which contains virtual a virtual
field "Plant and description" which errors out in code but works just fine
in
the QueryDef.

Then there is the problem that the Barcode generated by the coded query
does
not scan. The one in the QueryDef works just fine.

So I can spend a lot of time working out errors and MAYBE get it to work
in
code or I can do something that SHOULD be easy: Just use the existing
operational QueryDef and forget it.

which brings me back to my original question:
How do you open an EXISTING querydef and send it a parameter. The examples
in the help file only deal with creating new querydefs.


Allen Browne said:
Just write the code to query the query:
Dim strSql As String
strSql = "SELECT * FROM Query1 WHERE SomeExpression = True;"

That way you can add your criteria (filter expression) without changing
the
original.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an EXISTING querydef in my Access 2000 database.
How do I write the code to open it and set a filter on it?

I normally use coded queries without bothering with Querydef at all but
I
have a situation that is forcing me to use a saved Querydef.

the query in this querydef was written by a 3rd party, uses virtual
fields
and barcodes which makes it simpler to just filter the saved query if I
could
just access it.
Click to expand...
Click to expand...
Click to expand...
 
DIm rsCurr As DAO.Recordset

Set rsCurr = CurrentDb.QueryDefs("NameOfQuery").OpenRecordset
rsCurr.Filter = "Material = 'xyz'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Guy said:
I sloved the problem with why it would not work in code:

"& "-" & needed the " on each side to be coded as Chr(34) which is a "
simbol.
I now do not need to access the querydef

I would still like to know how to do so.

Douglas J. Steele said:
strSQL = "SELECT Material_List.Material, Material_List.[material
description], " & _
"Material_List!Material & "" "" & Material_List!Plnt & "" "" &
Material_List!SLoc " & _
"AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin,
[plnt] " & _
"& ""-"" & [plnt description] AS [Plant and description] " & _
"FROM Material_List " & _
"ORDER BY Material_List.bin, Material_List.Material, " & _
"Material_List.[material description];"

although that can be simplified to

strSQL = "SELECT Material, [material description], " & _
"Material & "" "" & Plnt & "" "" & SLoc AS Barcode, " & _
"[plnt] & ""-"" & [plnt description] AS [Plant and description] " & _
"FROM Material_List " & _
"ORDER BY bin, Material, [material description]"

Essentially, unless you've declared the query to have a parameter, you
can't
do anything with it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Guy said:
I believe I stated in my post that I could not do that as I normally
would.
I intended to keep my question short and simple but evidently that was
a
bad
idea.
This query has so many things to work out it isnt worth the time:

SELECT Material_List.Material, Material_List.[material description],
Material_List!Material & " " & Material_List!Plnt & " " &
Material_List!SLoc
AS Barcode, Material_List.bun, Material_List.sloc, Material_List.bin,
[plnt]
& "-" & [plnt description] AS [Plant and description]
FROM Material_List
ORDER BY Material_List.bin, Material_List.Material,
Material_List.[material
description];

First there are the 2 places where this appears: some text & " " & some
more
text"

I was able to use: " some text & " & Chr(34) & " " & Chr(34) " & some
more
text"
to get past that.

Then there's this section: "Material_List.bin, [plnt] & "-" & [plnt
description] AS [Plant and description]" which contains virtual a
virtual
field "Plant and description" which errors out in code but works just
fine
in
the QueryDef.

Then there is the problem that the Barcode generated by the coded query
does
not scan. The one in the QueryDef works just fine.

So I can spend a lot of time working out errors and MAYBE get it to
work
in
code or I can do something that SHOULD be easy: Just use the existing
operational QueryDef and forget it.

which brings me back to my original question:
How do you open an EXISTING querydef and send it a parameter. The
examples
in the help file only deal with creating new querydefs.


:

Just write the code to query the query:
Dim strSql As String
strSql = "SELECT * FROM Query1 WHERE SomeExpression = True;"

That way you can add your criteria (filter expression) without
changing
the
original.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an EXISTING querydef in my Access 2000 database.
How do I write the code to open it and set a filter on it?

I normally use coded queries without bothering with Querydef at all
but
I
have a situation that is forcing me to use a saved Querydef.

the query in this querydef was written by a 3rd party, uses virtual
fields
and barcodes which makes it simpler to just filter the saved query
if I
could
just access it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top