G
GREGORY HICKMOTT
Can I add a where statement to a query at run time an what is the syntex
with opening the query
with opening the query
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
You can reassign the query statement by setting the SQL property of the
QueryDef.
This kind of thing:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...
That is probably more efficient than creating another query into the first,
i.e.:
SELECT * FROM Query1 WHERE ...
QueryDef.
This kind of thing:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...
That is probably more efficient than creating another query into the first,
i.e.:
SELECT * FROM Query1 WHERE ...
G
Guest
query design window under the field you want to search-----Original Message-----
Can I add a where statement to a query at run time an what is the syntex
with opening the query
.
put your criteria in [ ] on the criteria line of the
G
GREGORY HICKMOTT
I have tryed but get errors in my sql i think im gettig close
'SQL = "UPDATE procedurespersamplebyclientsub " & "WHERE
procedurespersamplebyclientsub.ProceduresId = -1606758480"
'SQL = "UPDATE procedurespersamplebyclientsub " & "WHERE
procedurespersamplebyclientsub.ProceduresId = -1606758480"
A
Andrew Backer
I use CurrentDb.QueryDefs("queryname").Sql = SOMESQL
What is the benefit to using dbEngine(0)(0)? Does that equate to
Workspaces(0)? Benefits/drawbacks?
I keep see that method mentioned, as well as the workspaces method,
but I'm not sure why.
..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--
What is the benefit to using dbEngine(0)(0)? Does that equate to
Workspaces(0)? Benefits/drawbacks?
I keep see that method mentioned, as well as the workspaces method,
but I'm not sure why.
You can reassign the query statement by setting the SQL property of the
QueryDef.
This kind of thing:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...
That is probably more efficient than creating another query into the first,
i.e.:
SELECT * FROM Query1 WHERE ...
..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--
A
Allen Browne
Gregory, if you just want to supply the -1606758480 at runtime, then you
could follow the advice in the other thread, and just enter this in the
Criteria row of your original query under the ProceduresID field:
[Enter the ProcedureID:]
In what context are you executing your update query?
As an example, enter a dummy value in your original update query, and then
switch it to SQL View (View menu when you are in query design). Could you
build a string that emulates what you see, and then Execute the string?
could follow the advice in the other thread, and just enter this in the
Criteria row of your original query under the ProceduresID field:
[Enter the ProcedureID:]
In what context are you executing your update query?
As an example, enter a dummy value in your original update query, and then
switch it to SQL View (View menu when you are in query design). Could you
build a string that emulates what you see, and then Execute the string?
A
Allen Browne
Hi Andrew
Either will do. The difference is the dbEngine(0)(0) refers to the default
database that is already open in the default workspace. Advantage: very
quick to reference.
Disadvantage: may not be up to date if you have recently changed the data
structure (creating tables, fields, querydefs etc).
CurrentDb() creates a new database variable, and points it to
dbEngine(0)(0). Before it assigns it, it flushes all the collections in the
default workspace, so it is always up to date. The disadvantage is that it
flushing all the collections takes time, so it is slower, but safer.
It is important to understand that you create a new database variable with
each call to CurrentDb(). For exmaple, this will not work:
CurrentDb.Execute "DELETE FROM MyTable;"
Debug.Print CurrentDb.RecordsAffected
This will always return zero, because the 2nd call creates a new database
variable, which means the RecordsAffected property is zeroed.
Either will do. The difference is the dbEngine(0)(0) refers to the default
database that is already open in the default workspace. Advantage: very
quick to reference.
Disadvantage: may not be up to date if you have recently changed the data
structure (creating tables, fields, querydefs etc).
CurrentDb() creates a new database variable, and points it to
dbEngine(0)(0). Before it assigns it, it flushes all the collections in the
default workspace, so it is always up to date. The disadvantage is that it
flushing all the collections takes time, so it is slower, but safer.
It is important to understand that you create a new database variable with
each call to CurrentDb(). For exmaple, this will not work:
CurrentDb.Execute "DELETE FROM MyTable;"
Debug.Print CurrentDb.RecordsAffected
This will always return zero, because the 2nd call creates a new database
variable, which means the RecordsAffected property is zeroed.
G
GREGORY HICKMOTT
-1606758480"
this is just a sample of data that will change with a combobox selection
i looked at the sql view and got the syntex that worked
now i just have to replace my sample with the combobox tag
thanks for the help
this is just a sample of data that will change with a combobox selection
i looked at the sql view and got the syntex that worked
now i just have to replace my sample with the combobox tag
thanks for the help
J
John Vinson
-1606758480"
this is just a sample of data that will change with a combobox selection
i looked at the sql view and got the syntex that worked
now i just have to replace my sample with the combobox tag
The syntax would be something like
WHERE fieldname = [Forms]![your-form-name]![your-combo-name]