How do I set the default value so that it includes all values?

[sept2006]

I am using a form to enter criteria values in a query. I would like to make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and reports"
when designing my form, so hopefully that is something you can look at if you
need reference.

 

[Steve Schapel]

Sept,

In your query Criteria, put like this...
[Forms]![YourForm]![YourControl] Or [Forms]![YourForm]![YourControl]
Is Null

 

[sept2006]

I did that, and it works fine as long as I enter a value for every field.
However, sometimes I would only like to search for certain things such as
employee number and date, and not other things such as department number and
time spent. How can I leave the department number blank and still get
results that include all departments?

Thanks,
Sept

Sept,

In your query Criteria, put like this...
[Forms]![YourForm]![YourControl] Or [Forms]![YourForm]![YourControl]
Is Null

--
Steve Schapel, Microsoft Access MVP

I am using a form to enter criteria values in a query. I would like to make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and reports"
when designing my form, so hopefully that is something you can look at if you
need reference.

 

[Steve Schapel]

Sept,

?? ... that's exactly what I was trying to answer the first time.
What's not working for you?

 

[sept2006]

Whenever I fill out the form to perform the query, I sometimes want to leave
certain fields blank so they are not filtered. Whenever I do this, the query
returns no results.

However, when I do a query were I edit the criteria using the same values
from the form, without actually using a form (by entering them in the design
view of the query), it works fine.
Why does the form not allow me to leave fields blank and still return
results for the query?

I imagine this is a pretty tough question to answer without seeing what I am
doing, but I appreciate all the help that you are doing for me.

Thank you,
Sept

 

[Steve Schapel]

Sept,

I think I understand what you mean. When I told you:
[Forms]![YourForm]![YourControl] Or [Forms]![YourForm]![YourControl] Is Null
.... I meant you should put that in the Criteria of the query for each
field affected, and obviously you replace the 'YourForm' with the name
of the form, and the 'YourControl' with the name of the control on the
form where you enter the criteria for that field. Did you do that? Is
it still not working? Can you go to the design view of the query,
select SQL from the View menu, and copy/paste the SQL view of the query
in here?

 

[CurtainMary]

Hi, Thanks Steve I am following this with interest. I want to do a parameter
retrieve with several fields but don't always want to pick a value for all.

Sept,

I think I understand what you mean. When I told you:
[Forms]![YourForm]![YourControl] Or [Forms]![YourForm]![YourControl] Is Null
.... I meant you should put that in the Criteria of the query for each
field affected, and obviously you replace the 'YourForm' with the name
of the form, and the 'YourControl' with the name of the control on the
form where you enter the criteria for that field. Did you do that? Is
it still not working? Can you go to the design view of the query,
select SQL from the View menu, and copy/paste the SQL view of the query
in here?

--
Steve Schapel, Microsoft Access MVP

Whenever I fill out the form to perform the query, I sometimes want to leave
certain fields blank so they are not filtered. Whenever I do this, the query
returns no results.

However, when I do a query were I edit the criteria using the same values
from the form, without actually using a form (by entering them in the design
view of the query), it works fine.
Why does the form not allow me to leave fields blank and still return
results for the query?

I imagine this is a pretty tough question to answer without seeing what I am
doing, but I appreciate all the help that you are doing for me.

 

[CurtainMary]

The way I solved this is by using a dummy record in the table * which is the
wild card in searches. By using Like
[Forms]![frmSpecialMakeResults]![Combo81] and choosing the * I get all the
records applicable to the other criteria parameters.
CurtainMary

 

[sept2006]

SELECT EMPHRS.EMPNO, EMPHRS.DATE, EMPHRS.JOBNO, EMPHRS.JOBCODE, EMPHRS.DEPT,
EMPHRS.TOOLNO, EMPHRS.TIME, EMPHRS.ITEMNO, EMPHRS.JOBDESC
FROM EMPHRS
WHERE (((EMPHRS.EMPNO)=[Forms]![EMPHRS QUERY]![EMPNO]) AND ((EMPHRS.DATE)
Between [Forms]![EMPHRS QUERY]![START DATE] And [Forms]![EMPHRS QUERY]![END
DATE]) AND ((EMPHRS.JOBNO)=[Forms]![EMPHRS QUERY]![JOBNO]) AND
((EMPHRS.JOBCODE)=[Forms]![EMPHRS QUERY]![JOBCODE]) AND
((EMPHRS.DEPT)=[Forms]![EMPHRS QUERY]![DEPT]) AND
((EMPHRS.TOOLNO)=[Forms]![EMPHRS QUERY]![TOOLNO]) AND
((EMPHRS.TIME)=[Forms]![EMPHRS QUERY]![TIME]) AND
((EMPHRS.ITEMNO)=[Forms]![EMPHRS QUERY]![ITEMNO]) AND
((EMPHRS.JOBDESC)=[Forms]![EMPHRS QUERY]![JOBDESC]));

Sept,

I think I understand what you mean. When I told you:
[Forms]![YourForm]![YourControl] Or [Forms]![YourForm]![YourControl] Is Null
.... I meant you should put that in the Criteria of the query for each
field affected, and obviously you replace the 'YourForm' with the name
of the form, and the 'YourControl' with the name of the control on the
form where you enter the criteria for that field. Did you do that? Is
it still not working? Can you go to the design view of the query,
select SQL from the View menu, and copy/paste the SQL view of the query
in here?

--
Steve Schapel, Microsoft Access MVP

Whenever I fill out the form to perform the query, I sometimes want to leave
certain fields blank so they are not filtered. Whenever I do this, the query
returns no results.

However, when I do a query were I edit the criteria using the same values
from the form, without actually using a form (by entering them in the design
view of the query), it works fine.
Why does the form not allow me to leave fields blank and still return
results for the query?

I imagine this is a pretty tough question to answer without seeing what I am
doing, but I appreciate all the help that you are doing for me.

 

[sept2006]

CurtainMary,

Are you using a form when doing your parameter retrieve?

The way I solved this is by using a dummy record in the table * which is the
wild card in searches. By using Like
[Forms]![frmSpecialMakeResults]![Combo81] and choosing the * I get all the
records applicable to the other criteria parameters.
CurtainMary

I am using a form to enter criteria values in a query. I would like to make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and reports"
when designing my form, so hopefully that is something you can look at if you
need reference.

 

[Powderfinger]

I am using a form to enter criteria values in a query. I would like to make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and reports"
when designing my form, so hopefully that is something you can look at if you
need reference.

Here's how I do it, but there's probably a better way:

Dim strFilter As String

Dim stsLNAMEFilter As String
Dim strFNAMEFilter As String
Dim strCIDFilter As String
Dim strDateFilter As String


If Len(Trim(Me.LName)) > 0 Then strLastNameFilter = "[LNAME] Like '" &
Me.LName & "*'"
If Len(Trim(Me.FName)) > 0 Then strFirstNameFilter = "[FNAME] = '" &
Me.FName & "'"
If IsNumeric(Me.CID) Then strCIDFilter = "[CID] = '" & Me.CID

If IsDate(Me.StartDate) And IsDate(Me.EndDate) then strDateFilter = "[IDate]
Between #" & Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" &
Format$(Me.EndDate, "mm/dd/yyyy") & "#"

' now that I have all the filters I connect them with an AND:

If Len(Trim(strLastNameFilter)) > 0 Then strFilter = strLastNameFilter
If Len(Trim(strFirstNameFilter)) > 0 Then strFilter = strFilter & " " &
"AND" & " " & strFirstNameFilter
If Len(Trim(strCIDFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strCIDFilter
If Len(Trim(strDateFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strDateFilter


' now I have to get rid of the "AND" if it is the first three characters of
the filter

If Left(strFilter , 3) = "AND" Then
strFilter = Right(strFilter , Len(strFilter ) - 3)
End If

We have our filter with just what we want.

 

[sept2006]

My programming experience in Acess is very limited. Is there a way to do it
w/o writing in SQL or Visual Basic? I can probably do a little VB, but not
SQL as far as I know.

I am using a form to enter criteria values in a query. I would like to make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and reports"
when designing my form, so hopefully that is something you can look at if you
need reference.

Here's how I do it, but there's probably a better way:

Dim strFilter As String

Dim stsLNAMEFilter As String
Dim strFNAMEFilter As String
Dim strCIDFilter As String
Dim strDateFilter As String


If Len(Trim(Me.LName)) > 0 Then strLastNameFilter = "[LNAME] Like '" &
Me.LName & "*'"
If Len(Trim(Me.FName)) > 0 Then strFirstNameFilter = "[FNAME] = '" &
Me.FName & "'"
If IsNumeric(Me.CID) Then strCIDFilter = "[CID] = '" & Me.CID

If IsDate(Me.StartDate) And IsDate(Me.EndDate) then strDateFilter = "[IDate]
Between #" & Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" &
Format$(Me.EndDate, "mm/dd/yyyy") & "#"

' now that I have all the filters I connect them with an AND:

If Len(Trim(strLastNameFilter)) > 0 Then strFilter = strLastNameFilter
If Len(Trim(strFirstNameFilter)) > 0 Then strFilter = strFilter & " " &
"AND" & " " & strFirstNameFilter
If Len(Trim(strCIDFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strCIDFilter
If Len(Trim(strDateFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strDateFilter


' now I have to get rid of the "AND" if it is the first three characters of
the filter

If Left(strFilter , 3) = "AND" Then
strFilter = Right(strFilter , Len(strFilter ) - 3)
End If

We have our filter with just what we want.

 

[Powderfinger]

Have you tried the "Filter By Form" utility? All forms can be put in this
mode.

My programming experience in Acess is very limited. Is there a way to do it
w/o writing in SQL or Visual Basic? I can probably do a little VB, but not
SQL as far as I know.

I am using a form to enter criteria values in a query. I would like

to
make

it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and reports"
when designing my form, so hopefully that is something you can look at

if
you

need reference.

Here's how I do it, but there's probably a better way:

Dim strFilter As String

Dim stsLNAMEFilter As String
Dim strFNAMEFilter As String
Dim strCIDFilter As String
Dim strDateFilter As String


If Len(Trim(Me.LName)) > 0 Then strLastNameFilter = "[LNAME] Like '" &
Me.LName & "*'"
If Len(Trim(Me.FName)) > 0 Then strFirstNameFilter = "[FNAME] = '" &
Me.FName & "'"
If IsNumeric(Me.CID) Then strCIDFilter = "[CID] = '" & Me.CID

If IsDate(Me.StartDate) And IsDate(Me.EndDate) then strDateFilter = "[IDate]
Between #" & Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" &
Format$(Me.EndDate, "mm/dd/yyyy") & "#"

' now that I have all the filters I connect them with an AND:

If Len(Trim(strLastNameFilter)) > 0 Then strFilter = strLastNameFilter
If Len(Trim(strFirstNameFilter)) > 0 Then strFilter = strFilter & " " &
"AND" & " " & strFirstNameFilter
If Len(Trim(strCIDFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strCIDFilter
If Len(Trim(strDateFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strDateFilter


' now I have to get rid of the "AND" if it is the first three characters of
the filter

If Left(strFilter , 3) = "AND" Then
strFilter = Right(strFilter , Len(strFilter ) - 3)
End If

We have our filter with just what we want.

 

[Powderfinger]

The problem is you have your criteria in the query, I would take the
criteria (i.e. the WHERE clause) out of the query and open a form/report
with a filter.

 

[sept2006]

I have not tried the filter by form utility. As far as I know, you can only
use that to view individual records. Are you saying that I can use the
"Filter By Form" utility in unison with a query?

Have you tried the "Filter By Form" utility? All forms can be put in this
mode.

My programming experience in Acess is very limited. Is there a way to do it
w/o writing in SQL or Visual Basic? I can probably do a little VB, but not
SQL as far as I know.

I am using a form to enter criteria values in a query. I would like to
make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and
reports"
when designing my form, so hopefully that is something you can look at if
you
need reference.

Here's how I do it, but there's probably a better way:

Dim strFilter As String

Dim stsLNAMEFilter As String
Dim strFNAMEFilter As String
Dim strCIDFilter As String
Dim strDateFilter As String


If Len(Trim(Me.LName)) > 0 Then strLastNameFilter = "[LNAME] Like '" &
Me.LName & "*'"
If Len(Trim(Me.FName)) > 0 Then strFirstNameFilter = "[FNAME] = '" &
Me.FName & "'"
If IsNumeric(Me.CID) Then strCIDFilter = "[CID] = '" & Me.CID

If IsDate(Me.StartDate) And IsDate(Me.EndDate) then strDateFilter = "[IDate]
Between #" & Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" &
Format$(Me.EndDate, "mm/dd/yyyy") & "#"

' now that I have all the filters I connect them with an AND:

If Len(Trim(strLastNameFilter)) > 0 Then strFilter = strLastNameFilter
If Len(Trim(strFirstNameFilter)) > 0 Then strFilter = strFilter & " " &
"AND" & " " & strFirstNameFilter
If Len(Trim(strCIDFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strCIDFilter
If Len(Trim(strDateFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strDateFilter


' now I have to get rid of the "AND" if it is the first three characters of
the filter

If Left(strFilter , 3) = "AND" Then
strFilter = Right(strFilter , Len(strFilter ) - 3)
End If

We have our filter with just what we want.

 

[Steve Schapel]

Sept,

Thanks. I really don't understand why you have not followed my
recommendation. Did you try it, and it didn't work as you needed? My
recommendation is standard procedure for this type of situation. So, in
the Criteria of the EMPNO field in your query, instead of this...
[Forms]![EMPHRS QUERY]![EMPNO]
replace it with this...
[Forms]![EMPHRS QUERY]![EMPNO] Or [Forms]![EMPHRS QUERY]![EMPNO] Is Null

In the Criteria of the DATE field in your query, instead of this...
Between [Forms]![EMPHRS QUERY]![START DATE] And [Forms]![EMPHRS
QUERY]![END DATE]
replace it with this...
Between [Forms]![EMPHRS QUERY]![START DATE] And [Forms]![EMPHRS
QUERY]![END DATE] Or [Forms]![EMPHRS QUERY]![START DATE] Is Null Or
[Forms]![EMPHRS QUERY]![END DATE] Is Null

In the Criteria of the JOBNO field in your query, instead of this...
[Forms]![EMPHRS QUERY]![JOBNO]
replace it with this...
[Forms]![EMPHRS QUERY]![JOBNO] Or [Forms]![EMPHRS QUERY]![JOBNO] Is Null

Etc....

After you save this query, if you open it again in design view, you will
probably see that Access has re-arranged the criteria you have entered,
to satisfy its own requirements - don't worry about this, it will work fine.

 

[Steve Schapel]

CurtainMary,

I would recommend against this approach.

You can do something of similar lines, but less kludgy (as it does not
involve "dummy" records in tables, which will bite you one day), by
putting like this in your query criteria...
Like Nz([Forms]![frmSpecialMakeResults]![Combo81],"*")
But I would prefer not to use the Like keyword unless I mean Like.
Therefore, I would myself usually build the SQL in code, but otherwise I
always use the type of approach such as I have suggested elsethread to Sept.

 

[Steve Schapel]

Powder,

This is good. For the record, her's how I would do the same thing, but
really the same concept, just a slightly different syntax, and possibly
a little simpler...

Dim strFilter As String
strFilter = "TRUE"
If Not IsNull(Me.LName) Then
strFilter = strFilter & " And [LNAME] = '" & Me.LName & "'"
End If
If Not IsNull(Me.FName) Then
strFilter = strFilter & " And [FNAME] = '" & Me.FName & "'"
End If
If Not IsNull(Me.CID) Then
strFilter = strFilter & " And [CID] = " & Me.CID
End If
If Not IsNull(Me.StartDate + Me.EndDate) Then
strFilter = strFilter & " And [IDate] Between " &
CLng(Me.StartDate) & " And " & CLng(Me.EndDate)
End If

We have our filter with just what we want.

 

[CurtainMary]

Thank you Steve for taking the time.
What I am doing is to use a form with drop-down lists to choose the criteria
to be passed on to the query. Occasionally I want to retrieve all records in
a particular field depending on what i choose in another drop-down list. Your
solution appears to me to retrieve all records including blanks, all the
time, but i must say that i have not used the Nz function.
CurtainMary

CurtainMary,

I would recommend against this approach.

You can do something of similar lines, but less kludgy (as it does not
involve "dummy" records in tables, which will bite you one day), by
putting like this in your query criteria...
Like Nz([Forms]![frmSpecialMakeResults]![Combo81],"*")
But I would prefer not to use the Like keyword unless I mean Like.
Therefore, I would myself usually build the SQL in code, but otherwise I
always use the type of approach such as I have suggested elsethread to Sept.

--
Steve Schapel, Microsoft Access MVP

The way I solved this is by using a dummy record in the table * which is the
wild card in searches. By using Like
[Forms]![frmSpecialMakeResults]![Combo81] and choosing the * I get all the
records applicable to the other criteria parameters.
CurtainMary

 

[Steve Schapel]

CurtainMary,

It will apply the Criteria according to what is entered in the combobox
on the form, *if* there is something entered in the combobox. If there
is nothing entered in the combobox, then the query will return all
records, i.e. act as though there is no criteria at all applied. As I
understand it, this is exactly your requirement?