Like "* " and function to pass parameters into a query

[barret bondon]

I have a function in a module ( a very simple one that works well :

Function GetMyVariable()
GetMyVariable = VCODE
End Function

)

that I use to pass parameters into queries .
I now need to tell the query to show ALL records , and I pass in: Like"*"

It blows up, shows no records !

 

[John W. Vinson]

I have a function in a module ( a very simple one that works well :

Function GetMyVariable()
GetMyVariable = VCODE
End Function

)

that I use to pass parameters into queries .
I now need to tell the query to show ALL records , and I pass in: Like"*"

It blows up, shows no records !

Please post the SQL of the query. The function doesn't seem to be the issue!

However, you should probably be explicit with the typing:

Public Function GetMyVariable() As Variant

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[barret bondon]

SELECT contacts.ID, contacts.METER, contacts.code
FROM contacts
WHERE (((contacts.code)=GetMyVariable()))
ORDER BY contacts.METER;


and from the immediate window:
? getmyvariable()
Like '*'

I've also just tried passing in "is not null"

Could be a refresh issue of some kind. when it fails the first time (list
box is empty) I run it again with a code, the list box populates, and then I
run the like "*" or not null again and I get what looks like good data.

 

[John W. Vinson]

SELECT contacts.ID, contacts.METER, contacts.code
FROM contacts
WHERE (((contacts.code)=GetMyVariable()))
ORDER BY contacts.METER;


and from the immediate window:
? getmyvariable()
Like '*'

I've also just tried passing in "is not null"

Could be a refresh issue of some kind. when it fails the first time (list
box is empty) I run it again with a code, the list box populates, and then I
run the like "*" or not null again and I get what looks like good data.

The function can return a value... but not a part of the SQL such as the LIKE
keyword.

Try either setting VCODE to * and using

SELECT contacts.ID, contacts.METER, contacts.code
FROM contacts
WHERE (((contacts.code LIKE GetMyVariable()))
ORDER BY contacts.METER;

or pick some nonused code such as {ALL} as a standin for getting all records
and use

SELECT contacts.ID, contacts.METER, contacts.code
FROM contacts
WHERE (((contacts.code)=GetMyVariable()))
OR GetMyVariable() = "{ALL}"
ORDER BY contacts.METER;
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[barret bondon]

Thanks much. Two clever ideas. "ALL" seems to be holding it's own in my
tests.

Thanks !