In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))
So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.
But I'd like to have a function to do so because I'll be building hundreds
of queries based on similar information. My problem is that my attempts only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.
Thx.
John Spencer said:Not sure what you mean.
If you want something like
Field1 = "44001" or Field1 ="Tac"
and are trying to build that criteria you need to include the field name in
each part of the or
OR you can use
Field1 In ("44001","Tac")
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John Spencer said:Still not clear what you are doing?
Are you using a parameter prompt in the query? Or are you trying to build the
query string on the fly? Or are you attempting to pass in that string as if
you were typing it in the query design view?
Post the SQL of the query and tell what you are trying to do.
If you are simply using the query as the source for a report and you are
opening the report with VBA, then you use the open report's WhereCondition
DoCmd.OpenReport "MyReport",WhereCondition:="Somefield in (""A"",""B"",""C"")"
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))
So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.
But I'd like to have a function to do so because I'll be building hundreds
of queries based on similar information. My problem is that my attempts only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.
Thx.
John Spencer said:Not sure what you mean.
If you want something like
Field1 = "44001" or Field1 ="Tac"
and are trying to build that criteria you need to include the field name in
each part of the or
OR you can use
Field1 In ("44001","Tac")
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
jhugo54 wrote:
Is it possible to create a function to return multiple 'Or' criteria string
values such as "44001" Or "TAC"? I've tried creating a table and using
dlookup but end up returning ""44001" Or "TAC"", which is incorrect.
SELECT PROJECTS.PN, PROJECTS.CategoryID, PROJECTS.RequestDate, PROJECTS.DeptNo
FROM PROJECTS
WHERE (((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"));
I'm trying to pass in the string as if in query design view...
John Spencer said:Still not clear what you are doing?
Are you using a parameter prompt in the query? Or are you trying to build the
query string on the fly? Or are you attempting to pass in that string as if
you were typing it in the query design view?
Post the SQL of the query and tell what you are trying to do.
If you are simply using the query as the source for a report and you are
opening the report with VBA, then you use the open report's WhereCondition
DoCmd.OpenReport "MyReport",WhereCondition:="Somefield in (""A"",""B"",""C"")"
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))
So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.
But I'd like to have a function to do so because I'll be building hundreds
of queries based on similar information. My problem is that my attempts only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.
Thx.
:
Not sure what you mean.
If you want something like
Field1 = "44001" or Field1 ="Tac"
and are trying to build that criteria you need to include the field name in
each part of the or
OR you can use
Field1 In ("44001","Tac")
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
jhugo54 wrote:
Is it possible to create a function to return multiple 'Or' criteria string
values such as "44001" Or "TAC"? I've tried creating a table and using
dlookup but end up returning ""44001" Or "TAC"", which is incorrect.
John Spencer said:So HOW are you trying to pass in the strings?
One way would be to have a user defined vba function that builds the entire
query string.
Another would be to have a form that builds just a where clause and passes
that in as I mentioned in my previous post.
code snippet
Dim strWhere as String
If Mot IsNull(me.txtFindDept) Then
strWhere = StrWhere & " AND " & "[SomeFieldName]=""" & me.txtFindDept & """"
End if
If Not IsNull(me.txtStartDate) then
strWhere = StrWhere & " AND " & "[SomeDateField]=#" & me.txtStartDate & "#"
End IF
If Len(strWhere) > 0 then strWhere = Mid(strWhere,6)
DoCmd.OpenReport "MyReport",WhereCondition:=strWhere
If you need to do this with a query, you have to do things differently, but it
is possible to do similar things with a query or with a form.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
SELECT PROJECTS.PN, PROJECTS.CategoryID, PROJECTS.RequestDate, PROJECTS.DeptNo
FROM PROJECTS
WHERE (((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"));
I'm trying to pass in the string as if in query design view...
John Spencer said:Still not clear what you are doing?
Are you using a parameter prompt in the query? Or are you trying to build the
query string on the fly? Or are you attempting to pass in that string as if
you were typing it in the query design view?
Post the SQL of the query and tell what you are trying to do.
If you are simply using the query as the source for a report and you are
opening the report with VBA, then you use the open report's WhereCondition
DoCmd.OpenReport "MyReport",WhereCondition:="Somefield in (""A"",""B"",""C"")"
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
jhugo54 wrote:
In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))
So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.
But I'd like to have a function to do so because I'll be building hundreds
of queries based on similar information. My problem is that my attempts only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.
Thx.
:
Not sure what you mean.
If you want something like
Field1 = "44001" or Field1 ="Tac"
and are trying to build that criteria you need to include the field name in
each part of the or
OR you can use
Field1 In ("44001","Tac")
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
jhugo54 wrote:
Is it possible to create a function to return multiple 'Or' criteria string
values such as "44001" Or "TAC"? I've tried creating a table and using
dlookup but end up returning ""44001" Or "TAC"", which is incorrect.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.