Add <ALL> to combo box

R

Ron Weaver

I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
 
D

Douglas J. Steele

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT Null, "All", 0, Null, Null
FROM [Customer Query]
ORDER BY [Customer Query].StartDate;

This assumes that the 2nd column is what appears in the combo box. If it's
the first column (and CustomerID is a text field), put the "All" instead of
the first Null.

Of course, you'll need to change how you use the combo box so that you'll
know to do something different when they select the All row rather than a
specific customer's row.
 
K

Ken Sheridan

Ron:

You don't say which column is the combo box's BoundColumn, but it looks like
it might be the StartDate column, so on that basis:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID, 1 AS SortColumn
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT NULL, NULL, "<ALL>", NULL, NULL, 0
FROM [Customer Query]
ORDER BY SortColumn, [Customer Query].StartDate;

The SortColumn column contains the constants 0 and 1 in each half of the
UNION operation; this causes the second half to sort first. The second half
of the UNION operation would actually return as many identical rows as are
returned by the [Customer Query] but as a UNION operation suppresses
duplicate rows only one is returned in the result set. You can in fact use
anything in the FROM clause of the second half of the UNION operation as it
only returns constants and Nulls, so any table could be used there rather
than the [Customer Query].

Ken Sheridan
Stafford, England
 
R

Ron Weaver

Thanks Doug
That works just fine. What needs to be done to get the "<ALL>" to function
in the combo? Would the code be added to the current combo After Update
event? I'm still not very good at writing code. I am including my current
combo After Update code. I would really appreciate some help with this.
Private Sub cboCustomerDates_AfterUpdate()
Me.RecordsetClone.FindFirst "[CustomerID] = '" & Me![cboCustomerDates] &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.txtCustomerID = Me.cboCustomerDates.Column(0)
Me.txtPhone = Me.cboCustomerDates.Column(3)
Me.CustDates.Requery
End Sub
Thanks
Ron

Douglas J. Steele said:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT Null, "All", 0, Null, Null
FROM [Customer Query]
ORDER BY [Customer Query].StartDate;

This assumes that the 2nd column is what appears in the combo box. If it's
the first column (and CustomerID is a text field), put the "All" instead of
the first Null.

Of course, you'll need to change how you use the combo box so that you'll
know to do something different when they select the All row rather than a
specific customer's row.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ron Weaver said:
I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
 
R

Ron Weaver

Hi Ken
Thanks for the reply.
I used Doug's code and now have the <ALL> in the combo.
Now all I have to do is figure out how to get it to function.
Thanks again!
Ron
Ken Sheridan said:
Ron:

You don't say which column is the combo box's BoundColumn, but it looks like
it might be the StartDate column, so on that basis:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID, 1 AS SortColumn
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT NULL, NULL, "<ALL>", NULL, NULL, 0
FROM [Customer Query]
ORDER BY SortColumn, [Customer Query].StartDate;

The SortColumn column contains the constants 0 and 1 in each half of the
UNION operation; this causes the second half to sort first. The second half
of the UNION operation would actually return as many identical rows as are
returned by the [Customer Query] but as a UNION operation suppresses
duplicate rows only one is returned in the result set. You can in fact use
anything in the FROM clause of the second half of the UNION operation as it
only returns constants and Nulls, so any table could be used there rather
than the [Customer Query].

Ken Sheridan
Stafford, England

Ron Weaver said:
I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
 
K

Ken Sheridan

Ron:

How you get it to do what's required depends on the combo box's BoundColumn
property. This determines what the value of the control is when you make a
selection. The default is 1 so the value would be the first column,
CustomerID If it is 1, or anything apart from 2 for that matter, you'd
examine the value of the property for Null. Often the control's AfterUpdate
event procedure is used so that the relevant action is undertaken once an
item is selected. I assume in your case you want to restrict the records
returned on the basis of the selection, but return all records if <ALL> is
selected, so you might do something like this:

If IsNull(Me.YourComboBox) Then
' show all the records
Else
' show a restricted set of records
End If

Just what code you'd use to show all or a restricted set of records depends
on how you are showing the records. If you are filtering the current form
for instance then you'd set its Filter property; if you are opening another
form or a report then you'd use the WhereCondition argument of the OpenForm
or OpenReport method.

Alternatively if you are using a reference to the combo box as a parameter
in a query to restrict the rows returned then you'd extend the criterion in
the query with a Boolean OR operation so it tests for the control being Null,
e.g.

WHERE (CustomerID = Forms!YourForm!YourComboBox OR
Forms!YourForm!YourComboBox IS NULL)

If the combo box's BoundColumn property is 2 the its value would be <ALL>
if you select that item so in that case instead of testing for it being Null
you'd test for:

YourComboBox = "<ALL>".

Ken Sheridan
Stafford, England

Ron Weaver said:
Hi Ken
Thanks for the reply.
I used Doug's code and now have the <ALL> in the combo.
Now all I have to do is figure out how to get it to function.
Thanks again!
Ron
Ken Sheridan said:
Ron:

You don't say which column is the combo box's BoundColumn, but it looks like
it might be the StartDate column, so on that basis:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID, 1 AS SortColumn
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT NULL, NULL, "<ALL>", NULL, NULL, 0
FROM [Customer Query]
ORDER BY SortColumn, [Customer Query].StartDate;

The SortColumn column contains the constants 0 and 1 in each half of the
UNION operation; this causes the second half to sort first. The second half
of the UNION operation would actually return as many identical rows as are
returned by the [Customer Query] but as a UNION operation suppresses
duplicate rows only one is returned in the result set. You can in fact use
anything in the FROM clause of the second half of the UNION operation as it
only returns constants and Nulls, so any table could be used there rather
than the [Customer Query].

Ken Sheridan
Stafford, England

Ron Weaver said:
I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I'm not sure I understand how you'd use FindFirst in conjunction with All!

Doubtlessly you're going to need some code to determine whether All was
selected, and do something else:

Private Sub cboCustomerDates_AfterUpdate()
If Nz(Me![cboCustomerDates], "") <> "All" Then
Me.RecordsetClone.FindFirst "[CustomerID] = '" & _
Me![cboCustomerDates] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.txtCustomerID = Me.cboCustomerDates.Column(0)
Me.txtPhone = Me.cboCustomerDates.Column(3)
Me.CustDates.Requery
Else
' All was selected: what do you want to do?
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ron Weaver said:
Thanks Doug
That works just fine. What needs to be done to get the "<ALL>" to function
in the combo? Would the code be added to the current combo After Update
event? I'm still not very good at writing code. I am including my current
combo After Update code. I would really appreciate some help with this.
Private Sub cboCustomerDates_AfterUpdate()
Me.RecordsetClone.FindFirst "[CustomerID] = '" & Me![cboCustomerDates]
&
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.txtCustomerID = Me.cboCustomerDates.Column(0)
Me.txtPhone = Me.cboCustomerDates.Column(3)
Me.CustDates.Requery
End Sub
Thanks
Ron

Douglas J. Steele said:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT Null, "All", 0, Null, Null
FROM [Customer Query]
ORDER BY [Customer Query].StartDate;

This assumes that the 2nd column is what appears in the combo box. If
it's
the first column (and CustomerID is a text field), put the "All" instead
of
the first Null.

Of course, you'll need to change how you use the combo box so that you'll
know to do something different when they select the All row rather than a
specific customer's row.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ron Weaver said:
I have a combo box used to view specific customer records. I would like
to
add <ALL> to my Search By Date combo box as the default value. Below is
my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Weaver

Thanks Ken,
I received your response. I will be working on this later. You have given me
plenty to work with. I will let everyone know how it works out.
Ron

Ken Sheridan said:
Ron:

How you get it to do what's required depends on the combo box's BoundColumn
property. This determines what the value of the control is when you make a
selection. The default is 1 so the value would be the first column,
CustomerID If it is 1, or anything apart from 2 for that matter, you'd
examine the value of the property for Null. Often the control's AfterUpdate
event procedure is used so that the relevant action is undertaken once an
item is selected. I assume in your case you want to restrict the records
returned on the basis of the selection, but return all records if <ALL> is
selected, so you might do something like this:

If IsNull(Me.YourComboBox) Then
' show all the records
Else
' show a restricted set of records
End If

Just what code you'd use to show all or a restricted set of records depends
on how you are showing the records. If you are filtering the current form
for instance then you'd set its Filter property; if you are opening another
form or a report then you'd use the WhereCondition argument of the OpenForm
or OpenReport method.

Alternatively if you are using a reference to the combo box as a parameter
in a query to restrict the rows returned then you'd extend the criterion in
the query with a Boolean OR operation so it tests for the control being Null,
e.g.

WHERE (CustomerID = Forms!YourForm!YourComboBox OR
Forms!YourForm!YourComboBox IS NULL)

If the combo box's BoundColumn property is 2 the its value would be <ALL>
if you select that item so in that case instead of testing for it being Null
you'd test for:

YourComboBox = "<ALL>".

Ken Sheridan
Stafford, England

Ron Weaver said:
Hi Ken
Thanks for the reply.
I used Doug's code and now have the <ALL> in the combo.
Now all I have to do is figure out how to get it to function.
Thanks again!
Ron
Ken Sheridan said:
Ron:

You don't say which column is the combo box's BoundColumn, but it looks like
it might be the StartDate column, so on that basis:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID, 1 AS SortColumn
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT NULL, NULL, "<ALL>", NULL, NULL, 0
FROM [Customer Query]
ORDER BY SortColumn, [Customer Query].StartDate;

The SortColumn column contains the constants 0 and 1 in each half of the
UNION operation; this causes the second half to sort first. The second half
of the UNION operation would actually return as many identical rows as are
returned by the [Customer Query] but as a UNION operation suppresses
duplicate rows only one is returned in the result set. You can in fact use
anything in the FROM clause of the second half of the UNION operation as it
only returns constants and Nulls, so any table could be used there rather
than the [Customer Query].

Ken Sheridan
Stafford, England

:

I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Weaver

Ken
Here's my new SQL and After Update code. The BoundColumn is 1 (CustomerID)
and is set to "0" Length. Would the code for showing All records be used with
the current After Update code? What would that be? Sorry, I'm still trying to
learn this.

SELECT Customer.CustomerID, [FirstName] & " " & [LastName] AS Expr1,
Customer.FirstName, Customer.LastName, Customer.Phone FROM Customer UNION
SELECT Null,"<ALL>", Null, Null, Null FROM Customer
ORDER BY [FirstName];

Private Sub cboCustomerName_AfterUpdate()
Me.RecordsetClone.FindFirst "[CustomerID] = '" & Me![cboCustomerName] &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.txtCustomerID2 = Me.cboCustomerName.Column(0)
Me.txtPhone2 = Me.cboCustomerName.Column(3)
Me.CustOrders.Requery
End Sub
Thanks for your help.
Ron


Ken Sheridan said:
Ron:

How you get it to do what's required depends on the combo box's BoundColumn
property. This determines what the value of the control is when you make a
selection. The default is 1 so the value would be the first column,
CustomerID If it is 1, or anything apart from 2 for that matter, you'd
examine the value of the property for Null. Often the control's AfterUpdate
event procedure is used so that the relevant action is undertaken once an
item is selected. I assume in your case you want to restrict the records
returned on the basis of the selection, but return all records if <ALL> is
selected, so you might do something like this:

If IsNull(Me.YourComboBox) Then
' show all the records
Else
' show a restricted set of records
End If

Just what code you'd use to show all or a restricted set of records depends
on how you are showing the records. If you are filtering the current form
for instance then you'd set its Filter property; if you are opening another
form or a report then you'd use the WhereCondition argument of the OpenForm
or OpenReport method.

Alternatively if you are using a reference to the combo box as a parameter
in a query to restrict the rows returned then you'd extend the criterion in
the query with a Boolean OR operation so it tests for the control being Null,
e.g.

WHERE (CustomerID = Forms!YourForm!YourComboBox OR
Forms!YourForm!YourComboBox IS NULL)

If the combo box's BoundColumn property is 2 the its value would be <ALL>
if you select that item so in that case instead of testing for it being Null
you'd test for:

YourComboBox = "<ALL>".

Ken Sheridan
Stafford, England

Ron Weaver said:
Hi Ken
Thanks for the reply.
I used Doug's code and now have the <ALL> in the combo.
Now all I have to do is figure out how to get it to function.
Thanks again!
Ron
Ken Sheridan said:
Ron:

You don't say which column is the combo box's BoundColumn, but it looks like
it might be the StartDate column, so on that basis:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID, 1 AS SortColumn
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT NULL, NULL, "<ALL>", NULL, NULL, 0
FROM [Customer Query]
ORDER BY SortColumn, [Customer Query].StartDate;

The SortColumn column contains the constants 0 and 1 in each half of the
UNION operation; this causes the second half to sort first. The second half
of the UNION operation would actually return as many identical rows as are
returned by the [Customer Query] but as a UNION operation suppresses
duplicate rows only one is returned in the result set. You can in fact use
anything in the FROM clause of the second half of the UNION operation as it
only returns constants and Nulls, so any table could be used there rather
than the [Customer Query].

Ken Sheridan
Stafford, England

:

I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Weaver

Ken,
Never mind, I worked it out.
Thanks very much for your help.
Ron

Ken Sheridan said:
Ron:

How you get it to do what's required depends on the combo box's BoundColumn
property. This determines what the value of the control is when you make a
selection. The default is 1 so the value would be the first column,
CustomerID If it is 1, or anything apart from 2 for that matter, you'd
examine the value of the property for Null. Often the control's AfterUpdate
event procedure is used so that the relevant action is undertaken once an
item is selected. I assume in your case you want to restrict the records
returned on the basis of the selection, but return all records if <ALL> is
selected, so you might do something like this:

If IsNull(Me.YourComboBox) Then
' show all the records
Else
' show a restricted set of records
End If

Just what code you'd use to show all or a restricted set of records depends
on how you are showing the records. If you are filtering the current form
for instance then you'd set its Filter property; if you are opening another
form or a report then you'd use the WhereCondition argument of the OpenForm
or OpenReport method.

Alternatively if you are using a reference to the combo box as a parameter
in a query to restrict the rows returned then you'd extend the criterion in
the query with a Boolean OR operation so it tests for the control being Null,
e.g.

WHERE (CustomerID = Forms!YourForm!YourComboBox OR
Forms!YourForm!YourComboBox IS NULL)

If the combo box's BoundColumn property is 2 the its value would be <ALL>
if you select that item so in that case instead of testing for it being Null
you'd test for:

YourComboBox = "<ALL>".

Ken Sheridan
Stafford, England

Ron Weaver said:
Hi Ken
Thanks for the reply.
I used Doug's code and now have the <ALL> in the combo.
Now all I have to do is figure out how to get it to function.
Thanks again!
Ron
Ken Sheridan said:
Ron:

You don't say which column is the combo box's BoundColumn, but it looks like
it might be the StartDate column, so on that basis:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID, 1 AS SortColumn
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
UNION
SELECT NULL, NULL, "<ALL>", NULL, NULL, 0
FROM [Customer Query]
ORDER BY SortColumn, [Customer Query].StartDate;

The SortColumn column contains the constants 0 and 1 in each half of the
UNION operation; this causes the second half to sort first. The second half
of the UNION operation would actually return as many identical rows as are
returned by the [Customer Query] but as a UNION operation suppresses
duplicate rows only one is returned in the result set. You can in fact use
anything in the FROM clause of the second half of the UNION operation as it
only returns constants and Nulls, so any table could be used there rather
than the [Customer Query].

Ken Sheridan
Stafford, England

:

I have a combo box used to view specific customer records. I would like to
add <ALL> to my Search By Date combo box as the default value. Below is my
current SQL:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].StartDate, [Customer Query].Phone, Orders.OrderID
FROM [Customer Query] INNER JOIN Orders ON [Customer Query].OrderID =
Orders.OrderID
WHERE ((([Customer Query].StartDate) Between
[Forms]![frmSearchCustomerOrders]![txtStartDate] And
[Forms]![frmSearchCustomerOrders]![txtEndDate]))
ORDER BY [Customer Query].StartDate;
Would someone please show me how to do this?
Thanks
Ron
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select name in one listbox and pull all matching names to second 21
Combo box synch question 1
Problem adding new table to form 2
If then code for empty combo box column 4
Cannot add a record 2
Running Totals with a difference 0
report help needed thanks 1
Form filter with multi-column combo box in report 0

Top