Custom Search return all values...

[Brook]

I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?

is that possible?

Thanks,

Brook

 

[Marshall Barton]

I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?

Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Thanks for the post,

Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.

Thanks,

Brook

I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?

Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.
--
Marsh
MVP [MS Access]

Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.

Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Good Day Marshal,

I am slowly getting there. Within your code, what of these properties do I
need to change?

Also, where do I add a function to accept the date to search for? Can I do
this by lookup of what is already in the table? Same with the table field
Payee so that I won't have to know exactly what to enter in the search box?

txtDate
datefield
txtText
textfield
txtNumber
datefield

You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.
--
Marsh
MVP [MS Access]

Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.

Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?

Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Marshall,

I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)

Any suggestions?

Brook

You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.
--
Marsh
MVP [MS Access]

Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.

Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?

Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.
--
Marsh
MVP [MS Access]

I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)

You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.

Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

marshall,

I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?

Brook

I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.
--
Marsh
MVP [MS Access]

I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)

You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.
--
Marsh
MVP [MS Access]

I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?

I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.

I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Marshall,

Thanks for all your help, and sorry that i'm not getting it!

I'm going to look at what you have posted, and post a response later.

Brook

Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.
--
Marsh
MVP [MS Access]

I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?

I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Marshall,

I'm sorry i'm so confused about this...

So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

Again, I'm sorry...

What exactly is this process called? so that I can try to search for more
information?

Brook

Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.
--
Marsh
MVP [MS Access]

I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?

I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]

So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?

Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.

I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

ok... maybe you might have answered part of my trouble shooting... I want the
user to be able to use a drop down box of some sort to choose a date from a
date listed within my tblchecking so that if the user chooses : distinct date
: and the date is 1/1/05 (which is actually a date from my tbl, then all the
records from that date appear, or if the user selects payee "Chuck-e-Cheese"
from the drop down that is associated with my table it would populated my
"unbound" box with all the records associated with "chuck-e-cheese"...

am I trying something that is impossible? or should I start out small and
build up?

thanks for your patience!

Brook

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]

So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?

Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.


Brook wrote:
I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

I got it to work by looking up a date in my tblchecking acct.... but now I
would like to be able to select the date, or payee or description by a drop
down....

i'm so excited...

brook

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]

So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?

Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.


Brook wrote:
I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Good Day Marshall,

I got the Date search to work and the Amt Search to work, but I am
having trouble with the Payee Search to work.. here is the code that I
inserted into my commandbuttons onclick event:

Private Sub Command73_Click()
Dim strWhere As String
If Not IsNull(cbopayee) Then
strWhere = strWhere & " AND Payee]=" _
& Format(cbopayee)
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub


Any suggestions?

Brook

ok... maybe you might have answered part of my trouble shooting... I want the
user to be able to use a drop down box of some sort to choose a date from a
date listed within my tblchecking so that if the user chooses : distinct date
: and the date is 1/1/05 (which is actually a date from my tbl, then all the
records from that date appear, or if the user selects payee "Chuck-e-Cheese"
from the drop down that is associated with my table it would populated my
"unbound" box with all the records associated with "chuck-e-cheese"...

am I trying something that is impossible? or should I start out small and
build up?

thanks for your patience!

Brook

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]

So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?


:
Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.


Brook wrote:
I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

Choosing the search date value from a combo box should be as
easy as changing the txtDate text box to a combo box. I
think you're saying that the dates in the list would be
coming from table tblchecking so the combo box's Row Source
qould be:
SELECT [Date] FROM tblchecking ORDER BY [Date]
but you have to make sure the names I assumed are the actual
names you have.

For sanity's sake you should name the combo box cboDate and
change the procedure's code to use that name instead of the
textbox name, txtDate.
--
Marsh
MVP [MS Access]

I got it to work by looking up a date in my tblchecking acct.... but now I
would like to be able to select the date, or payee or description by a drop
down....

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]

So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?


:
Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.


Brook wrote:
I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Brook]

Marshall,

I have gotten all my search features to work properly except for one:

I set it up the same as the rest of them...

here is the info and the error:

SizeFeet --- example of type of value 4'9" x 5'10"

I have the following code on a command button to search based on a combobox

Private Sub Command31_Click()
Dim strWhere As String
If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & cboSizeFeet & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The problem is, is that i'm getting an error when I try to run the search:

Run-Time Error '2448'

You can't assign a value to this object

thanks for all your help! I have really learned a lot from you..

Brook

Choosing the search date value from a combo box should be as
easy as changing the txtDate text box to a combo box. I
think you're saying that the dates in the list would be
coming from table tblchecking so the combo box's Row Source
qould be:
SELECT [Date] FROM tblchecking ORDER BY [Date]
but you have to make sure the names I assumed are the actual
names you have.

For sanity's sake you should name the combo box cboDate and
change the procedure's code to use that name instead of the
textbox name, txtDate.
--
Marsh
MVP [MS Access]

I got it to work by looking up a date in my tblchecking acct.... but now I
would like to be able to select the date, or payee or description by a drop
down....

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]


Brook wrote:
So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?


:
Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.


Brook wrote:
I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

Starting to get fancy already? ;-)

Make sure you understand how your combo box is set up
relative to the table you are searching. This is very
important and without this information I can only guess what
you are doing and how to do what you want. I really need to
know what the combo box's Row Source query is (Copy/Paste
the query's SQL view) along with the fields in each related
table.

I can't be sure, but I suspect that the payee field
(payeeID?) in your table is really a foreign key in to the
payees table. If so post back with more details so I can
figure out how to set up the combo box.

On the other hand, maybe you set up the combo box to just
select the names from the same table you are searching (what
is the Row Source query?). If this is what you are doing,
you chose the wrong If block to use. When the payee field
is a Text field, you need to use the template If block for
text:

Private Sub Command73_Click()
Dim strWhere As String
If Not IsNull(cbopayee) Then
strWhere = strWhere & " AND Payee]=""" _
& cbopayee & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

I see where you are creating a separate command button for
each search field. This is fine if you really want the
users to make such a distinction, but the original example
code I posted only used one button that used multiple If
blocks to check all the search text/combo boxes ion one
shot.
--
Marsh
MVP [MS Access]

I got the Date search to work and the Amt Search to work, but I am
having trouble with the Payee Search to work.. here is the code that I
inserted into my commandbuttons onclick event:

Private Sub Command73_Click()
Dim strWhere As String
If Not IsNull(cbopayee) Then
strWhere = strWhere & " AND Payee]=" _
& Format(cbopayee)
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

ok... maybe you might have answered part of my trouble shooting... I want the
user to be able to use a drop down box of some sort to choose a date from a
date listed within my tblchecking so that if the user chooses : distinct date
: and the date is 1/1/05 (which is actually a date from my tbl, then all the
records from that date appear, or if the user selects payee "Chuck-e-Cheese"
from the drop down that is associated with my table it would populated my
"unbound" box with all the records associated with "chuck-e-cheese"...

am I trying something that is impossible? or should I start out small and
build up?

 

[Brook]

Marshall,

did you see my last question regard the field size?

Also you mentioned having all the searches from one command button. I tried
it, but it isn't working. Here is the code:

Private Sub Command46_Click()
Dim strWhere As String

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFt]=" _
& """" & cboSizeFeet & """"
End If
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

Thanks,

Brook

Choosing the search date value from a combo box should be as
easy as changing the txtDate text box to a combo box. I
think you're saying that the dates in the list would be
coming from table tblchecking so the combo box's Row Source
qould be:
SELECT [Date] FROM tblchecking ORDER BY [Date]
but you have to make sure the names I assumed are the actual
names you have.

For sanity's sake you should name the combo box cboDate and
change the procedure's code to use that name instead of the
textbox name, txtDate.
--
Marsh
MVP [MS Access]

I got it to work by looking up a date in my tblchecking acct.... but now I
would like to be able to select the date, or payee or description by a drop
down....

Don't worry about "not getting it" right away. You are kind
of jumping in at the deep end here, but the concepts are
fairly simple, once you catch on ;-)

To search on a single date field named [Date] for a date
value entered in the unbound text box named txtDate:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

The use of the field name ([Date] in this case) to the left
of the = sign tells the filter which field to search and it
will search for the value entered in the text box named
txtDate. Note that if you do not enter a date in the
txtDate text box before clicking the button, the If
statement will skip the next statement so strWhere will only
contain a zero length string (i.e. "") so the form's filter
will not have anything in it and all records will be
"found".

Since we are not mentioning the name of another field (such
as payee), it won't search for it.

I don't know if this is called anything specific, it's just
a dynamic form filter.
--
Marsh
MVP [MS Access]


Brook wrote:
So, what I need in my header:

Command Button with your code

unbound text box called txtdate

What else do I need? How and what tells the command what to search for,
either date? or description or Payee?

What exactly is this process called? so that I can try to search for more
information?


:
Let's take the If block for a numeric field as a working
example (and correcting my copy paste mistake, which
explains your extra change that I said was wrong):

If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [numberfield]=" & txtNumber
End If

txtNumber - is a place holder for the name of an unbound
text box in the form's header section. You should use
whatever name you have assigned to the particular text box
associated with the If block.

numberfield - is a place holder for the name of a field in
the form's record source table/query. This field is the one
that the text box, txtNumber, will search.


Brook wrote:
I guess i'm a little or maybe a lot confused...

Per your code, what names do I need to change in order to correspond with
my data?


:
I think you have some of the names mixed up. At least you
still have some of the example names that I used. You did
change one field name to [Date], but you did it in two
places. There is clearly a miscommunication somewhere in
here, because using a date field in my example of how to
write the where criteria for a numeric type field will
definitely not work.

Note that you should NOT use reserved names for anything you
create, i.e. using Date as the name of a field creates all
kinds of confusion. Change that field in the table to a
more descriptive name like InvoiceDate or whatever relates
to the data in your table.

The code I posted had three example If - End If blocks that
are templates of how to deal with three common types of
fields in a table. You do not have to have all three or you
might need multiple blocks of the same type. You do have to
have a separate block for each unbound control that you have
provided for users to enter search criteria in the form
header. It's up to you to make those design decisions and
copy and modify the appropriate template block needed for
each unbound control (and change the template's example
control name (txt...) that I used to the unbound control
name that you are using).

As for the error, without knowing what error message you
got, I can't tell what it's complaining about. Actually I
don't see how that line can have an error unless the
strWhere string is an illegal filter, which wouldn't
surprise me at this point. Did you hover the mouse over the
strWhere variable when the line was highlighted so you could
see the string and determine what the code actually did? If
so what did it look like? If not, try it, you might get a
clue as to what is wrong.


Brook wrote:
I added the unbound control box and the commmand button with this code:

Private Sub Command74_Click()
Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [Date]=" _
& Format(txtDate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [Date]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True
End Sub

but when I click on the command button it errors and goes to the following
line for debugging:
Me.Filter = Mid(strWhere, 6)


:
You know, that's a good question. I guess I don't, at least
I can't think of one at the moment. When all else fails,
see if you can find some thing in the Northwind sample
database that comes with Access.

Actually, what I posted is pretty much what I would expect
in a sample,but without the actual form. And, that should
be fairly easy to create. Just add the text boxes to a new
form, name them appropriately and change the names in my
posted code (my names use "txt" as a prefix for the unbound
text boxes and the names that have "field" in them are
columns in the form's record source table.

If you hit a specific roadblock, come on back with the
details and we'll go from there.


Brook wrote:
Do you know of any samples that I could look at the help me with this? I'm
going to get started with what you posted.


Brook wrote:
I have frmcheckingacct that I would like to add a custom search feature so
that for example if I wanted to search by date, it would in a new pop up box
and or unbound text box would show all the records for that date,

or if I wanted to search by payee, it would pull all the records for that
payee?


:
Not only possible, but common to many applications ;-)

The usual arrangement is to make a Continuous form bound to
your table with the important data fields bound to controls
in the forms detail section.

To provide the "search" facility, plaxe an unbound text box
for each of the seach items (along with a button to run the
search) into the form's header section.

The code in the button's Click event procedure would be
along the lines of this air code:

Dim strWhere As String
If Not IsNull(txtDate) Then
strWhere = strWhere & " AND [datefield]=" _
& Format(txtDate,"\#m\/d\/yyyy\#")
End If
If Not IsNull(txtText) Then
strWhere = strWhere & " AND [textfield]=" _
& """" & txtText & """"
End If
If Not IsNull(txtNumber) Then
strWhere = strWhere & " AND [datefield]=" & txtNumber
End If
Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

 

[Marshall Barton]

did you see my last question regard the field size?

Also you mentioned having all the searches from one command button. I tried
it, but it isn't working. Here is the code:

Private Sub Command46_Click()
Dim strWhere As String

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFt]=" _
& """" & cboSizeFeet & """"
End If
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

Yes, I now see the size question. This is the old quoted
quotes issue. Any time the value of a string contains quote
marks and it is going to be included inside quote marks, the
imbedded quote must be doubled up. Sounds like double speak
to me too ;-)

For example:
str = "He said, "Hello""
has ambiguous syntax that can not be parsed. The way to
make it follow the above rule is to use:

str = "He said, ""Hello"""

You can achieve that effect within the combo box's value is
to make the statement even more general:

strWhere = strWhere & " AND [SizeFt]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
which I agree is kind of confusing all by itself. An
Alternative way to do the same thing is:

strWhere = strWhere & " AND [SizeFt]=" & Chr(34) _
& Replace(cboSizeFeet, Chr(34), Chr(34) & Chr(34)) & Chr(34)

Well, that may not really clarify things all that much, but
if you think about it, in spite of the headache, you can
gain a fair insight into the issue.

********
Another problem in your code is that you miscounted the
number of quotes in the DesignName block of code.

********
Are you sure the DesignNumber field in the table is a Text
field?