If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a custom function in a query
Let's say I have a function in a global module called
GetNames (which returns something like 'ARM','CAT') and I want to run a query like the following: SELECT * FROM TableA WHERE Name IN (NAMELIST) where NAMELIST is filled in with value returned by GetNames function. Am I correct in assuming that the syntax would be SELECT * FROM TableA WHERE Name IN (GetNames()) For some reason this does not seem to work...just want to make sure I'm going about the right way. |
#2
|
|||
|
|||
Using a custom function in a query
Hi Bob,
I have never found a way to "program" a list that will work within IN. It just plain wants a literal string. I once used something like below: Public Function fInList(Fld, strList As String) As Boolean If InStr(strList, "'" & Fld & "'") 0 Then fInList = True Else fInList = False End If End Function SELECT * FROM TableA WHERE fInList([Name],GetNames()); the advantage of function is that you could add error checking and input validation. but you can see that you might just use: SELECT * FROM TableA WHERE InStr(GetNames(), "'" & [Name] & "'") 0; This may be "expensive" depending on what is going on in GetNames(). If you have already saved the list string in a global var, and GetNames() is just a wrapper to return this global var, it might help. or it may be to your advantage in this case to "combine" GetNames() with InStr() in another function. You know your data best. Good luck, Gary Walter "BobRoyAce" wrote Let's say I have a function in a global module called GetNames (which returns something like 'ARM','CAT') and I want to run a query like the following: SELECT * FROM TableA WHERE Name IN (NAMELIST) where NAMELIST is filled in with value returned by GetNames function. Am I correct in assuming that the syntax would be SELECT * FROM TableA WHERE Name IN (GetNames()) For some reason this does not seem to work...just want to make sure I'm going about the right way. |
#3
|
|||
|
|||
Using a custom function in a query
Hi Bob,
I forgot to mention the most obvious solution (if possible). In some event, just write the SQL using GetName(). For example, if query is used for recordsource of a report, in report's open event: Dim strSQL As String strSQL = "SELECT * FROM TableA " _ & "WHERE Name IN (" & GetNames() & ")" Me.Recordsource = strSQL If you need to use as stored query, then you can just redefine the SQL of the query in the event where you will need it using Duane's ChangeSQL function. Function ChangeSQL(pstrQueryName As String, pstrSQL As String) On Error GoTo Err_ChangeSQL Dim db As DAO.Database Dim qd As DAO.QueryDef Set db = CurrentDb Set qd = db.QueryDefs(pstrQueryName) qd.SQL = pstrSQL qd.Close db.Close Exit_ChangeSQL: Set qd = Nothing Set db = Nothing Exit Function Err_ChangeSQL: MsgBox Err.Description Resume Exit_ChangeSQL End Function So...say you have button to open a form bound to this query, in button's OnClick event Dim strSQL As String strSQL = "SELECT * FROM TableA " _ & "WHERE Name IN (" & GetNames() & ")" ChangeSQL "yourqueryname", strSQL DoCmd.OpenForm..... I think you probably understand. Good luck, Gary Walter "Gary Walter" wrote in message ... Hi Bob, I have never found a way to "program" a list that will work within IN. It just plain wants a literal string. I once used something like below: Public Function fInList(Fld, strList As String) As Boolean If InStr(strList, "'" & Fld & "'") 0 Then fInList = True Else fInList = False End If End Function SELECT * FROM TableA WHERE fInList([Name],GetNames()); the advantage of function is that you could add error checking and input validation. but you can see that you might just use: SELECT * FROM TableA WHERE InStr(GetNames(), "'" & [Name] & "'") 0; This may be "expensive" depending on what is going on in GetNames(). If you have already saved the list string in a global var, and GetNames() is just a wrapper to return this global var, it might help. or it may be to your advantage in this case to "combine" GetNames() with InStr() in another function. You know your data best. Good luck, Gary Walter "BobRoyAce" wrote Let's say I have a function in a global module called GetNames (which returns something like 'ARM','CAT') and I want to run a query like the following: SELECT * FROM TableA WHERE Name IN (NAMELIST) where NAMELIST is filled in with value returned by GetNames function. Am I correct in assuming that the syntax would be SELECT * FROM TableA WHERE Name IN (GetNames()) For some reason this does not seem to work...just want to make sure I'm going about the right way. |
#4
|
|||
|
|||
Using a custom function in a query
Thanks Gary:
This raises another question for me...is it possible to directly reference global variables in queries? For example, SELECT * FROM TableA WHERE TheName = gsCurrentName where gsCurrentName is a global variable. I will, of course, try this and one of us can post the answer for the benefit of others. So, if I understand your last suggestion, if it made sense to do so, I could, say, have a function called IsAChosenName(ByVal sNameToFind) which returns True if the name passed is contained in a global variable which stores a comma separated list of selected names and False otherwise...correct? I'm going to try that as I am currently storing the list of selected names this way in a global variable. |
#5
|
|||
|
|||
Using a custom function in a query
I wish that I could do this, but due to the fact that the
primary query involved references other queries and tables (4 layers deep!), and since it is a couple of layers deep that the need to use the function exists, it would be problematic at best. The ChangeSQL function would be perfect if it wasn't for the fact that this is a multi-user application and it is possible that multiple individuals could be running this query at the same time with different names. Will keep that in mind, though, for other applications. Thanks... |
#6
|
|||
|
|||
Using a custom function in a query
"BobRoyAce" wrote This raises another question for me...is it possible to directly reference global variables in queries? For example, SELECT * FROM TableA WHERE TheName = gsCurrentName where gsCurrentName is a global variable. The only way I know to use a global variable in a query is to wrap it in a code module function. Option Compare Database Option Explicit Public gsCurrentName As Variant Public Function GetCurrentName() As Variant GetCurrentName = gsCurrentName End Function Public Function SetCurrentName(pCurrentName As Variant) gsCurrentName = pCurrentName End Function SELECT * FROM TableA WHERE TheName = GetCurrentName() .. Gary Walter |
Thread Tools | |
Display Modes | |
|
|