Using LIKE with a function

[LB79]

Morning all,

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

My function is:

Public Function User()[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]User = VBA.Environ("UserName")[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]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[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]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[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Arial]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.

Thanks

 

[jdraw]

Can you tell us in a line or 2 exactly what you're trying to accomplish?
Are you getting an error message?
Perhaps you could show the entire query SQL you are using.

 

[LB79]

Sorry I didnt realise the code had split like that.

For example, I have a table with fields Task and AssignedTo.
The task may be assigned to multiple people which shows like: Person1/Person2/Person3.
I have used this before with a combobox as query criteria and it works well but I now want the criteria to be the user name. So the basic question is: In a query, I have have the criteria = function, but how can I have the criteria LIKE function.

Thanks

 

[Brianwarnock]

I don't think that you can do that but you could move the Like test into the function and test the outcome.

Brian

 

[gemma-the-husky]

you need to wrap the target of the like in "" characters

try like & chr(34) & user() & chr(34)

note that your date criteria may also fail - dates need to be wrapped in # characters.

 

[vbaInet]

You can also use:

LIKE '*' & User() & '*'

Only because the chances of a username with a single quote in it is slim.

Also, I hope your function was placed in a Standard Module?

 

[gemma-the-husky]

actually, like *username* will find records where the username is part of the string. if the field consists JUST of the name, you can just use the simpler user()

 

[LB79]

Excellent! Thanks guys - Just what I needed!