Function that reads fisloaded() and value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Derek Wittman - 27 Sep 2006 14:49 GMT

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

Reply to this Message

Tom Wickerath - 28 Sep 2006 10:25 GMT

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
__________________________________________

> Good morning,
> I'm working with the fisloaded() function (thanks to the MVPs for their
[quoted text clipped - 21 lines]
>
> Derek Wittman

Reply to this Message

Derek Wittman - 28 Sep 2006 12:49 GMT

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

> Hi Derek,
>
[quoted text clipped - 52 lines]
> >
> > Derek Wittman

Reply to this Message

Douglas J. Steele - 28 Sep 2006 13:13 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Good morning, Tom. While I'd really like to say, "Yes, thanks", I
> honestly
[quoted text clipped - 76 lines]
>> >
>> > Derek Wittman

Reply to this Message

Derek Wittman - 28 Sep 2006 14:09 GMT

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

> If you're using the query grid, presumably you're putting fdept() in the
> Criteria row under the appropriate field. Put
[quoted text clipped - 85 lines]
> >> >
> >> > Derek Wittman

Reply to this Message