Function that reads fisloaded() and value

[Derek Wittman]

Good morning,
I'm working with the fisloaded() function (thanks to the MVPs for their
fantastic site!) to determine a query's criteria. I need to see if the form
is open (obviously). If it is NOT, then I don't want criteria in the query
field. If it is open, I want to check to see what the combobox (cmbdept)
value is. If it's NULL (one of the choices has not been made), I want to
again remove criteria from the query field. If there IS a value in the
cmbdept, I want to use that value for my criteria.

Since the fisloaded() is on the MVP site, I won't bother putting it in here.
Here's my secondary function (fdept) that will be called up in the query's
criteria. Problem is that I cannot get it to compile. And no, I'm not
recent on my use of ifs, thens, and elses.

Thanks in advance for your help!

Function fdept() As String
If fIsLoaded("frmdepartment") = 0 Then
fdept = ""
Else: If Forms!frmdepartment.cmbDept Is Null Then not fdept
Else: fdept = Forms!frmdepartment.cmbDept
End If
End Function

Derek Wittman

 

[Tom Wickerath]

Hi Derek,

Since the fisloaded() is on the MVP site, I won't bother putting it in here.

However, it would be nice to provide a hyperlink to it, for the benefit of
others who might be reading your message, and not know where to find this
function:
http://www.mvps.org/access/forms/frm0002.htm


Does this help?

Function fdept() As String
If fIsLoaded("frmdepartment") = 0 Then
fdept = ""
Else
If IsNull(Forms!frmdepartment.cmbDept) Then
fdept = ""
Else
fdept = Forms!frmdepartment.cmbDept
End If
End If

End Function




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Derek Wittman]

Good morning, Tom. While I'd really like to say, "Yes, thanks", I honestly
cannot say YES (although thanks for trying). Setting fdept = "" seems to
give the query the impression that I'm seeking out field values with
zero-length strings and not the more accurate Like "*" criterion.

Unfortunately, if I set fdept to "Like *", Access accepts this as a string
and not the actual use of Like...

I expensed a VBA book and have lent it to a colleague. I'm going to see if
I can get it back!

Thanks anyway - I'm stumped.
Derek

 

[Douglas J. Steele]

If you're using the query grid, presumably you're putting fdept() in the
Criteria row under the appropriate field. Put

Like fdept()

instead, and pass * instead of "".

Like works the same as = when there's no wild card.

 

[Derek Wittman]

Doug! THAT seems to be working. Who'd have thought Access's query interface
would take LIKE with a function?!? Very cool.

Thanks to you for the suggestion and to Tom for correcting my code. The
combincation of the two was exactly what I was looking for!

Derek