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

Pass Parameter to Query using Function

P: n/a
Hi and TIA, I Have a query that uses a function for setting the Criteria.
When I view the return value of the finction it appears to be exactly what I
want for my criteria. The problem is if I type the criteia directly in the
query all's well however if I set it to the return value of the function I
get no records returned. I've used functions in query criteria before so I
know it's possible, but for some reason it's not working. Here's the
function I'm using. Any help is appreciated. Thanks!

Option Explicit
Const conRPoolCVN = """A"" Or ""B"" Or ""C"" Or ""D"" Or ""E"" Or ""F"" Or
""G"" Or ""H"" Or ""J"""
Const conRPoolL = """A"" Or ""C"" Or ""D"" Or ""E"" Or ""H"" Or ""M"" Or
""U"""
Const conAVDLRCVN = "<>""A"" And <>""B"" And <>""C"" And <>""D"" And <>""E""
And <>""F"" And <>""G"" And <>""H"" And <>""J"""
Const conAVDLRL = "<>""A"" And <>""C"" And <>""D"" And <>""E"" And <>""H""
And <>""M"" And <>""U"""
Dim intSiteType As Integer
Dim intReportType As Integer

Public Function SetPoolCode() As String
Dim intSiteType As Integer
Dim intReportType As Integer
intSiteType = Forms!frmExpedite_Criteria.optClass
intReportType = Forms!frmExpedite_Criteria.optReport

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

--
Reggie

"Half this game is 90% mental."

----------
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.