Using Is Not Null in a function (1 Viewer)

[macca the hacke]

Hi

Probably a really stupid question, but am having one of those days!

I have a function that takes data from a table to be used in queries. This works fine, but I want user to be able to put "(All)" in the table and the query to return all records.

I have tried setting value of function to is not null, but won't let me - keeps saying compile error, syntax error. I have tried different variations, such as setting function value to "", but this returns no records in query.

Below is code:

**code start**

Public Function FilterCompany()

Dim intId As Integer
Dim strFC As String
intId = 1

strFC = CurrentDb.OpenRecordset("select [usys_Tbl_Filters].[portfolio] from [usys_Tbl_Filters] where [usys_Tbl_Filters].[id] = " & intId & "").Fields("portfolio")
If strFC <> "(All)" Then
FilterCompany = strFC
Else
FilterCompany.Value is Not Null

End If

End Function

** code end **

Any help appreciated!

 

[neileg]

It's more usual to use the Like operator and the wildcard * as criteria in a query to produce this. So your criterion would become:
Like [MyTxtBox]&"*"
If [MyTxtBox] is null, the the criterion resolves to Like * which returns all records.

 

[macca the hacke]

Hi Neil

I have tried that too and still get the same error message
I am wondering if my problem is because I am using a function rather than a sub - but I need it to be a function for it to work in my queries

Any othet thoughts?

 

[boblarson]

In your function you are assigning a Recordset to a string,

strFC = CurrentDb.OpenRecordset("select [usys_Tbl_Filters].[portfolio] from [usys_Tbl_Filters] where [usys_Tbl_Filters].[id] = " & intId & "").Fields("portfolio")

which won't work. If you are going to be using a recordset, you need to assign it to a recordset object.