function criteria

  • Thread starter Thread starter jhugo54
  • Start date Start date
J

jhugo54

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.
 
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
 
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.
 
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
Click to expand...
Click to expand...
 
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
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
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.
Click to expand...
Click to expand...
Click to expand...
 
'HOW' is my question at this point. I've tried creating a table and using
dlookup. I've tried creating a function. But ""44001" Or "TAC"" gets
returned intstead of just "44001" Or "TAC".

This is not for a Report, btw. Just a simple Query.

Thanks for your time.

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.
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Really basic question: Why not just have those values in a table and do
a simple join?

SELECT PROJECTS.PN, PROJECTS.CategoryID, PROJECTS.RequestDate, PROJECTS.DeptNo
FROM PROJECTS inner join SelectedDepartments
on SelectedDepartments.DeptNo = PROJECTS.DeptNo;

Clifford Bass
 
Back
Top