Using LIKE with a function

Please can someone tell me if its possible to use LIKE and a function as a query criteria?

My function is:

Public Function User()

User = VBA.Environ("UserName") End Function

And my query is:

SELECT tbl_REF_Reminder.Type, tbl_REF_Reminder.PIC, tbl_REF_RMOPIC.[UN Code], tbl_REF_RMOPIC.[AG Code], tbl_REF_Reminder.Agent, tbl_REF_Reminder.DueDate, tbl_REF_Reminder.RemindStatus, tbl_REF_UID.UserID, tbl_REF_RMOPIC.[Agency Name] AS Agency

FROM (tbl_REF_Reminder INNER JOIN tbl_REF_RMOPIC ON tbl_REF_Reminder.Agent = tbl_REF_RMOPIC.[UN Code]) INNER JOIN tbl_REF_UID ON tbl_REF_RMOPIC.[PIC Name] = tbl_REF_UID.Name WHERE (((tbl_REF_Reminder.PIC) Like User()) AND ((tbl_REF_Reminder.DueDate) Between Format(Now()-2,'dd/mm/yyyy') And Format(Now(),'dd/mm/yyyy')) AND ((tbl_REF_Reminder.RemindStatus)=-1));

When I try as per above I get no results. If I add *User()* I get an invalid operator error.

I need to use LIKE as there may be multiple users in the PIC field

Sponsored Links:

Related Topics

Where called from
I have several functions.say a, b, c, d. Each one calls the same function.say X. How do I get function X to know which function called it.without putting the specific calling function's name in the call to function X ?


Want function to get current function name
I am developing an all-purpose error handler. As I see it, the "ideal" function would look like this:

Public Function Foo()
On Error GoTo er
'Code goes here.
ex: 'Cleanup code, closing open objects, etc.
Exit Function
er:
erf 'An error handling function, which writes this function's name
'and error stats to a table, then returns here.
MsgBox Err.Description 'Allows stepping through code in this function.
Resume ex 'Gets cleanup code.
End Function

The only issue I'm having is having to pass the current function name in to my error handler. Does anyone know a way to get this systematically


Multiple Controls on one sub
I have 10 check boxes. I want all of them to run a function after update. Is there a way to put all 10 on one sub, instead of having to have a sub for each box?

Instead of

Sub Chk1_AfterUpdate() Function End Sub Sub Chk2_AfterUpdate() Function End Sub

etc.
Could I have something like

Sub Chk1 through Chk10_AfterUpdate() Function End Sub


Want a brainteaser? Here's one...
Imagine functions (as in coding): there is a function call, to each function there are parameters and whereas the names and the amount of parameters is specific to a function, the value of that parameters is specific to each individual function call.

For example:
Add (Number1,Number2)
ADD = function
Number1 & Number2 = parameters specific to the function

Add (5,3) = function call
where the numbers 5 and 3 are parameters specific to the function call

-

What I would like is a datamodel that can do this: if I add a new function, it should present me with the parameters specific for that function, but the values for those parameters should be different for each 'function call'. I'm really struggeling with this one and I'm in need of some


Want a brainteaser? Here's one...
Imagine functions (as in coding): there is a function call, to each function there are parameters and whereas the names and the amount of parameters is specific to a function, the value of that parameters is specific to each individual function call.

For example:
Add (Number1,Number2)
ADD = function
Number1 & Number2 = parameters specific to the function

Add (5,3) = function call
where the numbers 5 and 3 are parameters specific to the function call

-

What I would like is a datamodel that can do this: if I add a new function, it should present me with the parameters specific for that function, but the values for those parameters should be different for each 'function call'. I'm really struggeling with this one and I'm in need of some


=CurrentUser()
Trying to get us onto accdb from mdb and of course have to resolve the user login.

Can I take ownership of the above function?
I understand this is a function Access "owns" or am I

Nearly all of our problems would be resolved if I can get into this function and have it look to the new login Form I will create to get the CurrentUser() rather then the mdw file I assume it currently looks for.

If I can't do this then I must seach for all references to this function and replace same with a new function I create say, =TeamMemberLogged


How do you use a Function inside another Function?
Forum,

I have created a Function to perform a task (delete records) and want this to be actioned within another function.

How do I get this to happen ? DoCmd ?

Here is an extract of the main procedure and the function I want to get to work is FuncDeleteRecord(some variables)


If RepayFreq = "Fortnightly" Then
'FuncDeleteRecord("TblLateFeeCalculated", "LDPK", "LoanID")
Do Until Date <= CommenceDate

Appreciate any advice


InsertAtCursor function
Allen Browne created a VBA Function named[LINK] By pressing a key combination it will insert text at the cursor position on a form. Does anyone know of a simple database that uses this function? I would like to view the coding and try to figure out how to incorporate this function into a database. I am having difficulty with Arguments, Compile Errors, etc.


Passing a reference to a control on a subform to a function
I was wondering if someone could give me a hand with this one.

I have referenced a control on a form from a function in a module using this:
Forms(frm.Name).Controls(ctl.Name).Value

The function looks like this:
Function(frm as form, ctl as control)

The function call looks like this:
Call Function(me, me.ctl)

This all works fine but I cannot work out how to do the same thing except referencing the control in a subform, i.e. what do you pass to the function and how do you reference it in the function?

I have tried everything I can think of, stuff like.

Forms(frmParent.name).Forms(frmSubform.name).Contr ols(ctl.name).value

and passing it like this.

Call Function(me.parent, me, me.ctl)
Function(frmParent as form, frmSubform as form, ctl as control)


Collection function
I am trying to figure out what a below function would be doing:

Public Function Clrclct(clct As Collection)
For I = 1 To clct.Count
clct.Remove 1
Next
End Function

I can only add that it's later being used while exporting tables from access to excel.


Does not recognize Function Name
I have a function in a module called ComplexQueries()

When I create a macro and use RunCode and run the macro, I get the error

The expression you entered has a function name that Microsoft Access can't find.

If I change the function name to CQs() and enter that in the macro, then it's fine.


How to declare constant optional paramenters for function or sub
I was trying to find a way to name constant optional parameters for a function. I have function A(optional myParameter as Integer) as Boolean.
When I make a function call I want to be able to see predefined legit values for myParameter that my "function A" can accept. I would think it should be relatively easy to define but can't find a way to do so.


Calling Custom User Function in Query
Is it possible to use a Custom/User function that uses more than one input variable? e.g., A function to determine the value of X depends on inputs from A, B, and C. A, B, and C are calculated fields in the Query.

Is it possible to pass values from A, B, and C to the function from the query to calculate X in the function. I have been able to use and call functions using one variable with a CASE statement.

But not sure if it is possible to use/call a function in a query that uses multiple conditions based on calculated fields in the query, e.g., A, B, and C.


undefined function in query
I have used the following ( [LINK] ) function to calculate workdays and exclude holidays in a query.

I have saved the function under the folder Modules in VB,

[CODE]

every time I try to run the query, it gives me the Undefined function "workdays" in expression.
my question is if I have saved the function correctly in VB?


Function from a query
How do I get specific information from a function that I run from a query? I will pass 2 parameters to the function but I want to get some text in return.

I use this function for populating text boxes from other forms but it has the same logic structure so I would like to also use it for the query.

Anyway, how do I get information from the function back to the query (as in returning a certain value)?

Right now, the function looks like this when called from a form (Current Event).

Function Priorities(strPriority As String, OpenDate As Date, ctrl As Control)

When calling from the query, the ctrl part will not be used so I believe that I can put the Optional keyword in front of it and just the strPriority and OpenDate will be passed. I then need to return info based on what the function decides to do with that info

Example: strPriority = "Priority 2" and OpenDate = "12-20-2010"

Query runs calling that function and would return "6" (sometimes it might need to say "Immediately


Passing Errors from Functions to Containing Sub
I'm using MS Access 2003. I have some validation code that I'd like to make into a function because the code will be used in multiple places. I'm thinking that if an error happens in the validation function, I want the calling sub to know about that.

So what is the best way to let the container sub (that called the function) know about the error that happened in the function? And should the error message be generated in the function or the container sub?

I cannot seem to get the container sub to see the error generated by the function.

Or is there a better way?


Numbers instead of text
I am designing a qry which gets values from a table manager and a table function. I get the data from function and managername from tbl.manager and get the function per manager in text (datasheetview), when I execute the query and create a report it gives the function innumber, although I reference to the same tblManager.function (tried al the others didn't get the right values), now I get the right values only in number and not in text
fe 2 for assistant manager, 3 for general manager...


Where is this function used?
MZtools has a magnificent feature that tells you where a function/procedure is used. It is like the Find (control-f) but rather than jumping to each occurence it shows all the results in a window. Giving you an overview of the function/procedure's usage.
Since I am unable to use MZTools on the company laptop I will try to write this functionality myself.

Using a shortcut (e.g. control-s) the code must start searching for the function/procedure I am currently in. Which means that I am in the Code window and editting some function or procedure.

The activated code must start searching for the function or procedure in all the code and sql statements.

Now here's the question:
How can I distinguish a regular string from a function or procedure?

In time it will find its way in the code repository


Using DMax Function in a query
I am having trouble getting DMax Function to work in a query. I am looking to pull the most recent ID for one table. The DMax function will be used in 2 queries.

Currently this is what the query looks like without the DMax function:
....
I had a column for the DMax function in the query but it would not run. I had Expr1: DMax ("EmpInfoID").


Custom function inside SQL query (Access VBA)
I'm trying to use a custom (user-defined) function inside an SQL query. When the function is in a normal module, the code works fine, but when the function is in a Class Module, Note: I have made sure the Function is Public in the Class Module. The Routine that creates and executes the SQL command is in the Class Module.

Is it forbidden to call a function residing in a Class Module from an SQL query, or am I missing something?