By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Query Parameter using Function

P: n/a
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!frmExpedite_Criteria.optClass
intReportType = Forms!frmExpedite_Criteria.optReport

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."

----------
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Reggie wrote:
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? []Const conRPoolCVN = """A"" Or ""B"" Or ""J"""

[]
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
Nov 12 '05 #2

P: n/a
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" <ma*********@wowway.com> wrote in message
news:ru********************************@4ax.com...
Reggie wrote:
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 itappears 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 recordsare returned. Am I missing something here?

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

[]
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.