Query Parameter using Function

[Reggie]

Hi and TIA, I have the criteria for one of my fields set to the return
value of this function. When I view the result in the immediate window it
appears to be the exact value I would use if I directly typed it in the
criteria section. However, if I do type it in I get the results I expect,
but if I set the criteria = to the return value of the function no records
are returned. Am I missing something here? Any help is appreciated.
Thanks!

Option Explicit
Const conRPoolCVN = """A"" Or ""B"" Or ""J"""
Const conRPoolL = """A"" Or ""C"" Or ""D""Or ""U"""
Const conAVDLRCVN = "<>""A"" And <>""B"" And <>""J"""
Const conAVDLRL = "<>""A"" And <>""C"" And <>""D"" And <>""U"""

Public Function SetPoolCode() As String
Dim intSiteType As Integer
Dim intReportType As Integer
dim str as String
intSiteType = Forms!frmExpedi te_Criteria.opt Class
intReportType = Forms!frmExpedi te_Criteria.opt Report

Select Case intReportType
Case 1 'AVDLR
Select Case intSiteType
Case 1 'CVN
str = conAVDLRCVN
Case 2 'L-Class
str = conAVDLRL
Case 3 'Other
str = ""
End Select
Case 2 'R-Pool
Select Case intSiteType
Case 1 'CVN
str = conRPoolCVN
Case 2 'L-Class
str = conRPoolL
Case 3 'Other
str = ""
End Select
End Select
SetPoolCode = str
Debug.Print str
End Function


--
Reggie

"Half this game is 90% mental."

----------

[Marshall Barton]

Re: Query Parameter using Function

Reggie wrote:
[color=blue]
>Hi and TIA, I have the criteria for one of my fields set to the return
>value of this function. When I view the result in the immediate window it
>appears to be the exact value I would use if I directly typed it in the
>criteria section. However, if I do type it in I get the results I expect,
>but if I set the criteria = to the return value of the function no records
>are returned. Am I missing something here?[/color]
[][color=blue]
>Const conRPoolCVN = """A"" Or ""B"" Or ""J"""[/color]
[]


Yes, you are missing something. What you enter in a
criteria cell in query design view is not valid SQL. The
query design thingy does a lot of work to translate the
query design grid into an equivalent SQL statement. If you
feel adventureous, check Help for the BuildCriteria
function, which does this for criteria.

In the case of your criteria, you have to repeat the name of
the field for each comparison. E.g.

Const conRPoolCVN = "[somefield] = ""A"" Or [somefield] =
""B"" Or [somefield] = ""J"""





--
Marsh

[Reggie]

Re: Query Parameter using Function

Marshal, Thanks! I looked at the SQL statement and was wondering about the
field name before each comparison and thought I may have to do as you
suggested. I'll give it a try. Thanks again!

--
Reggie

"Half this game is 90% mental."

----------
"Marshall Barton" <marshbarton@wo wway.com> wrote in message
news:rurdnv89kd 8t9acqo9ktc39cc borp87lpa@4ax.c om...[color=blue]
> Reggie wrote:
>[color=green]
> >Hi and TIA, I have the criteria for one of my fields set to the return
> >value of this function. When I view the result in the immediate window[/color][/color]
it[color=blue][color=green]
> >appears to be the exact value I would use if I directly typed it in the
> >criteria section. However, if I do type it in I get the results I[/color][/color]
expect,[color=blue][color=green]
> >but if I set the criteria = to the return value of the function no[/color][/color]
records[color=blue][color=green]
> >are returned. Am I missing something here?[/color]
> [][color=green]
> >Const conRPoolCVN = """A"" Or ""B"" Or ""J"""[/color]
> []
>
>
> Yes, you are missing something. What you enter in a
> criteria cell in query design view is not valid SQL. The
> query design thingy does a lot of work to translate the
> query design grid into an equivalent SQL statement. If you
> feel adventureous, check Help for the BuildCriteria
> function, which does this for criteria.
>
> In the case of your criteria, you have to repeat the name of
> the field for each comparison. E.g.
>
> Const conRPoolCVN = "[somefield] = ""A"" Or [somefield] =
> ""B"" Or [somefield] = ""J"""
>
>
>
>
>
> --
> Marsh[/color]