Function() wildcard in query ?

[CdRsKuLL]

I'm having trouble with a query when trying to list everything in my table. I have placed a function which passes a global variable into the query builder. The variable is selected by a combo box. This all works great when I want to only list part of the table but I am unsure how to pass a wildcard with this function..

He's my function code...

Function GetDept()
If currentdept = "SHOW ALL" Then GetDept = "" Else GetDept = currentdept
End Function

I expected when the function = "" (nothing) then it will list everything.. but it lists nothing.

I have also tried * but still no luck. Is there a wildcard value I can use ?

thanks

Steve

 

[Kryst51]

try "*" maybe

 

[georgedwilkinson]

Not sure what you're doing with your function but if you're passing it to a where clause with a "LIKE" statement, you can use the "*" in Access SQL. Use "%" for the wildcard for any other brand of SQL.

 

[CdRsKuLL]

hmm must be doing something wrong.. I'm I using the function correctly ?

I've tried using "*" and "'*'" and " LIKE '*'" but still it doesnt list anything.. but if it use's something like "Staff" it then lists all employee's who are in the department called 'Staff'

thanks again for the help

Steve

 

[georgedwilkinson]

No, you're not using it right.

Please post the SQL from this query.

 

[CdRsKuLL]

its coming out like...

SELECT tblEmployee.*, tblHoliday.*, [surname] & ", " & [firstname] AS name, tblEmployee.department, tblHoliday.holidaymonth
FROM tblEmployee INNER JOIN tblHoliday ON tblEmployee.ID = tblHoliday.employee
WHERE (((tblEmployee.department)=getdept()) AND ((tblHoliday.holidaymonth)=getdate()))
ORDER BY [surname] & ", " & [firstname];

thanks

 

[georgedwilkinson]

You cannot use an equal sign ("=") with a wild-card. You'll need to change it to "LIKE" in order to do it this way.

 

[CdRsKuLL]

That got it fantastic !!!

top man George.. thank you very much

 

[georgedwilkinson]

No problem. Always glad to lend a hand.