Query by Form for field by value or all values

I have a form named FindData that has a field on it named txtUID. If a
person types in an ID and clicks the search button, I want the query to
find all the records in the table MAIN that have the matching ID in a
column named "UserID"... no problem there. If they don't key in an ID
on the form, I want it to find all the records in the table MAIN that
have something entered into the "UserID" field, but omit the records
that have blank UserID fields...

I've tried dozens of alternatives in the query builder

This is what's under the UserID field in the query:

IIf([Forms]![FindData]![txtUID] Is Null,Not Is
Null,[Forms]![FindData]![txtUID])

Help.

re: Query by Form for field by value or all values

Jim wrote:[color=blue]
> I have a form named FindData that has a field on it named txtUID. If a
> person types in an ID and clicks the search button, I want the query to
> find all the records in the table MAIN that have the matching ID in a
> column named "UserID"... no problem there. If they don't key in an ID
> on the form, I want it to find all the records in the table MAIN that
> have something entered into the "UserID" field, but omit the records
> that have blank UserID fields...
>
> I've tried dozens of alternatives in the query builder
>
> This is what's under the UserID field in the query:
>
> IIf([Forms]![FindData]![txtUID] Is Null,Not Is
> Null,[Forms]![FindData]![txtUID])
>
> Help.
>[/color]
You could do something like this

Dim strFilter As String
strFilter = "UserID " & _
IIF(Not IsNull(txtUID)," = " & txtUID,"Is Not Null")
Me.Filter = strFilter
Me.FilterOn = True

re: Query by Form for field by value or all values

Thanks salad... it worked!

re: Query by Form for field by value or all values

Here's another alternative ---
Put the following expression in the criteria:
[Forms]![FindData]![txtUID] Or ([Forms]![FindData]![txtUID] Is Null)

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
resource@pcdatasheet.com




"Jim" <jlrehmann@gmail.com> wrote in message
news:1142718993.162016.6730@e56g2000cwe.googlegrou ps.com...[color=blue]
>I have a form named FindData that has a field on it named txtUID. If a
> person types in an ID and clicks the search button, I want the query to
> find all the records in the table MAIN that have the matching ID in a
> column named "UserID"... no problem there. If they don't key in an ID
> on the form, I want it to find all the records in the table MAIN that
> have something entered into the "UserID" field, but omit the records
> that have blank UserID fields...
>
> I've tried dozens of alternatives in the query builder
>
> This is what's under the UserID field in the query:
>
> IIf([Forms]![FindData]![txtUID] Is Null,Not Is
> Null,[Forms]![FindData]![txtUID])
>
> Help.
>[/color]

re: Query by Form for field by value or all values

"PCD" <notmy@email.com> schreef in bericht news:7a1Tf.6468$sL2.3867@newsread2.news.atl.earthl ink.net...

-- [color=blue]
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications 'Resource ????
> Over 1150 users have come to me from the newsgroups requesting help '1150 users ????
> resource@pcdatasheet.com
>
> [/color]

--
To Steve:
Over 525!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R