Export Spreadsheet from Switchboard

[Guest]

In the switchboard there is an option to export the data as an excell
spreadsheet.Is there a way to place a date paramater so the entire database
is not exported? Since this is new to me, would it be better to create a
query with a date parameter that could some how be exported as an excell
file..if it is possable? Looking for easiest methods to export data since DB
will be in another cntry.

 

[Guest]

I find that the best way to do this is to create a form that will contain
date field(s). In your query, refer to these data fields in your criteria to
limit the number of records. You can refer to them as
Forms!FormName!ControlName.value. Then Make sure that the person opens this
form and enters a date (you can even enter a default date). Then when the
query is exported it will automatically pull the date from this form. As you
have probably already discovered, you cannot have pop-up parameters in the
query because it will fail on export, but the Form parameters will work fine.

Please let me know if I can provide more assistance.

 

[Guest]

To add a little to hmadyson's reply, I have modified an existing sample to
include Export to Excel capability. This uses a dialog form, which you could
open from your switchboard. You are welcome to download a copy here:

http://home.comcast.net/~tutorme2/samples/customdialogbox.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Tom, This download is pretty cool. I am not great in VB but i will try to
modify the forms you have in this download to my application.If you dont
mind, i may need your help in the event i cant through it but thank you for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Guest]

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Hello Tom,

I imported my tables and applied one query called querytester1. When i use
the form fdlgAskfor Datesand City2, my query will run but display all records
since i inserted my query in this part of the code. How do get the form to
use querytester1 and have the dates parameter apply to it?

 

[Ken Snell \(MVP\)]

Tom's taking well-deserved vacation for a few days, so let me see if I can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate". Did you
change this to the query that you want to use -- which I assume is
"querytester1"?

 

[Guest]

Thanks Ken,
I have worked on it and date parameters are now are fine. There is a
function to export to excell, do you know if on exporting to excell, is there
away to set up the source query so there will be a total on one column?

 

[Ken Snell \(MVP\)]

You want to show the total of a column at the bottom of the column? If yes,
you'd need to create a union query and export that query (following shows
how you'd do it if you wanted to sum the third column -- it uses a sorting
field to ensure that the total is the last thing exported; if you don't want
that extra column, you'd need to identify some way to add information to the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

 

[Guest]

Hello Ken,

In order to avoid several emails, i thought it best to show the SQL view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt, VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field so would
i apply your answer to the eight fields before it?

 

[Ken Snell \(MVP\)]

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

In order to avoid several emails, i thought it best to show the SQL view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field so
would
i apply your answer to the eight fields before it?

You want to show the total of a column at the bottom of the column? If
yes,
you'd need to create a union query and export that query (following shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you don't
want
that extra column, you'd need to identify some way to add information to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

 

[Guest]

Thanks Ken,

For some reason, i run the query and receive TT.VendorInv.InvAmt prompt. I
guess this is something that I need to work through and i want to thank you
for the excellent support.

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

In order to avoid several emails, i thought it best to show the SQL view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field so
would
i apply your answer to the eight fields before it?

You want to show the total of a column at the bottom of the column? If
yes,
you'd need to create a union query and export that query (following shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you don't
want
that extra column, you'd need to identify some way to add information to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken,
I have worked on it and date parameters are now are fine. There is a
function to export to excell, do you know if on exporting to excell, is
there
away to set up the source query so there will be a total on one column?

:

Tom's taking well-deserved vacation for a few days, so let me see if I
can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate". Did you
change this to the query that you want to use -- which I assume is
"querytester1"?

--

Ken Snell
<MS ACCESS MVP>



Hello Tom,

I imported my tables and applied one query called querytester1. When
i
use
the form fdlgAskfor Datesand City2, my query will run but display
all
records
since i inserted my query in this part of the code. How do get the
form
to
use querytester1 and have the dates parameter apply to it?

:

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, This download is pretty cool. I am not great in VB but i
will
try
to
modify the forms you have in this download to my application. If
you
dont
mind, i may need your help in the event i cant through it but
thank
you
for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Ken Snell \(MVP\)]

Sorry -- found some typos:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

For some reason, i run the query and receive TT.VendorInv.InvAmt prompt. I
guess this is something that I need to work through and i want to thank
you
for the excellent support.

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

In order to avoid several emails, i thought it best to show the SQL
view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field so
would
i apply your answer to the eight fields before it?


:

You want to show the total of a column at the bottom of the column? If
yes,
you'd need to create a union query and export that query (following
shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you
don't
want
that extra column, you'd need to identify some way to add information
to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken,
I have worked on it and date parameters are now are fine. There is a
function to export to excell, do you know if on exporting to excell,
is
there
away to set up the source query so there will be a total on one
column?

:

Tom's taking well-deserved vacation for a few days, so let me see
if I
can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate". Did
you
change this to the query that you want to use -- which I assume is
"querytester1"?

--

Ken Snell
<MS ACCESS MVP>



Hello Tom,

I imported my tables and applied one query called querytester1.
When
i
use
the form fdlgAskfor Datesand City2, my query will run but display
all
records
since i inserted my query in this part of the code. How do get
the
form
to
use querytester1 and have the dates parameter apply to it?

:

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, This download is pretty cool. I am not great in VB but i
will
try
to
modify the forms you have in this download to my application.
If
you
dont
mind, i may need your help in the event i cant through it but
thank
you
for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Guest]

Hello Ken,
It absoutely runs great and i have been practicing to learn more on my own.
I have tried to enter the parameter AND ((VendorInv.InvDesc) Like "AUDIT" &
"*")) within the sql to limit the output.Can this be done or would this be
stretching it abit?

Sorry -- found some typos:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

For some reason, i run the query and receive TT.VendorInv.InvAmt prompt. I
guess this is something that I need to work through and i want to thank
you
for the excellent support.

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>




Hello Ken,

In order to avoid several emails, i thought it best to show the SQL
view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field so
would
i apply your answer to the eight fields before it?


:

You want to show the total of a column at the bottom of the column? If
yes,
you'd need to create a union query and export that query (following
shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you
don't
want
that extra column, you'd need to identify some way to add information
to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken,
I have worked on it and date parameters are now are fine. There is a
function to export to excell, do you know if on exporting to excell,
is
there
away to set up the source query so there will be a total on one
column?

:

Tom's taking well-deserved vacation for a few days, so let me see
if I
can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate". Did
you
change this to the query that you want to use -- which I assume is
"querytester1"?

--

Ken Snell
<MS ACCESS MVP>



Hello Tom,

I imported my tables and applied one query called querytester1.
When
i
use
the form fdlgAskfor Datesand City2, my query will run but display
all
records
since i inserted my query in this part of the code. How do get
the
form
to
use querytester1 and have the dates parameter apply to it?

:

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, This download is pretty cool. I am not great in VB but i
will
try
to
modify the forms you have in this download to my application.
If
you
dont
mind, i may need your help in the event i cant through it but
thank
you
for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Ken Snell \(MVP\)]

You can add more criteria to the query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*") AND
((VendorInv.InvDesc) Like "AUDIT" & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*")) AND
((TT.InvDesc) Like "AUDIT" & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
It absoutely runs great and i have been practicing to learn more on my
own.
I have tried to enter the parameter AND ((VendorInv.InvDesc) Like "AUDIT"
&
"*")) within the sql to limit the output.Can this be done or would this be
stretching it abit?

Sorry -- found some typos:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

For some reason, i run the query and receive TT.VendorInv.InvAmt
prompt. I
guess this is something that I need to work through and i want to thank
you
for the excellent support.

:

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID =
TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>




Hello Ken,

In order to avoid several emails, i thought it best to show the SQL
view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field
so
would
i apply your answer to the eight fields before it?


:

You want to show the total of a column at the bottom of the column?
If
yes,
you'd need to create a union query and export that query (following
shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you
don't
want
that extra column, you'd need to identify some way to add
information
to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken,
I have worked on it and date parameters are now are fine. There
is a
function to export to excell, do you know if on exporting to
excell,
is
there
away to set up the source query so there will be a total on one
column?

:

Tom's taking well-deserved vacation for a few days, so let me
see
if I
can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate".
Did
you
change this to the query that you want to use -- which I assume
is
"querytester1"?

--

Ken Snell
<MS ACCESS MVP>



Hello Tom,

I imported my tables and applied one query called
querytester1.
When
i
use
the form fdlgAskfor Datesand City2, my query will run but
display
all
records
since i inserted my query in this part of the code. How do get
the
form
to
use querytester1 and have the dates parameter apply to it?

:

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, This download is pretty cool. I am not great in VB but
i
will
try
to
modify the forms you have in this download to my
application.
If
you
dont
mind, i may need your help in the event i cant through it
but
thank
you
for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Guest]

Hello Ken,

This works great and now i would like to limit my results with this sql
statement:
AND ((VendorInv.InvDesc) Like "AUDIT" & "*")) .I have tried to place it but
continue to get syntax errors. Can a union query contain this?

Sorry -- found some typos:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

For some reason, i run the query and receive TT.VendorInv.InvAmt prompt. I
guess this is something that I need to work through and i want to thank
you
for the excellent support.

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>




Hello Ken,

In order to avoid several emails, i thought it best to show the SQL
view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field so
would
i apply your answer to the eight fields before it?


:

You want to show the total of a column at the bottom of the column? If
yes,
you'd need to create a union query and export that query (following
shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you
don't
want
that extra column, you'd need to identify some way to add information
to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken,
I have worked on it and date parameters are now are fine. There is a
function to export to excell, do you know if on exporting to excell,
is
there
away to set up the source query so there will be a total on one
column?

:

Tom's taking well-deserved vacation for a few days, so let me see
if I
can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate". Did
you
change this to the query that you want to use -- which I assume is
"querytester1"?

--

Ken Snell
<MS ACCESS MVP>



Hello Tom,

I imported my tables and applied one query called querytester1.
When
i
use
the form fdlgAskfor Datesand City2, my query will run but display
all
records
since i inserted my query in this part of the code. How do get
the
form
to
use querytester1 and have the dates parameter apply to it?

:

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, This download is pretty cool. I am not great in VB but i
will
try
to
modify the forms you have in this download to my application.
If
you
dont
mind, i may need your help in the event i cant through it but
thank
you
for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Ken Snell \(MVP\)]

Hello Ken,

This works great and now i would like to limit my results with this sql
statement:
AND ((VendorInv.InvDesc) Like "AUDIT" & "*")) .I have tried to place it
but
continue to get syntax errors. Can a union query contain this?

Yes, see my post about 3 hours ago in this thread.

 

[Guest]

Hello Ken,
Where can i learn more on working and creating these type of query's"? The
export to excel works great and i would like to hide the column titled
SortOrder. Sorry for late response, discussion boards technical problems
occured.

You can add more criteria to the query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*") AND
((VendorInv.InvDesc) Like "AUDIT" & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*")) AND
((TT.InvDesc) Like "AUDIT" & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
It absoutely runs great and i have been practicing to learn more on my
own.
I have tried to enter the parameter AND ((VendorInv.InvDesc) Like "AUDIT"
&
"*")) within the sql to limit the output.Can this be done or would this be
stretching it abit?

Sorry -- found some typos:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>




Thanks Ken,

For some reason, i run the query and receive TT.VendorInv.InvAmt
prompt. I
guess this is something that I need to work through and i want to thank
you
for the excellent support.

:

This should do what you seek:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.VendorInv.InvAmt) AS SF3, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID =
TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

--

Ken Snell
<MS ACCESS MVP>




Hello Ken,

In order to avoid several emails, i thought it best to show the SQL
view:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND
((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY VendorInv.EnterDate;

The VendorInv.InvAmt is the field that i would like to have totaled
automatically in excell. There are 8 fields in front of InvAmt field
so
would
i apply your answer to the eight fields before it?


:

You want to show the total of a column at the bottom of the column?
If
yes,
you'd need to create a union query and export that query (following
shows
how you'd do it if you wanted to sum the third column -- it uses a
sorting
field to ensure that the total is the last thing exported; if you
don't
want
that extra column, you'd need to identify some way to add
information
to
the
second subquery so that it's recod would sort last in the export):

SELECT T.Field1, T.Field2, T.Field3, 1 AS SortOrder
FROM TableName AS T
UNION ALL
SELECT Null, Null, Sum(TT.Field3) AS SF3, 2 AS SortOrder
FROM TableName AS TT
ORDER BY SortOrder;

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken,
I have worked on it and date parameters are now are fine. There
is a
function to export to excell, do you know if on exporting to
excell,
is
there
away to set up the source query so there will be a total on one
column?

:

Tom's taking well-deserved vacation for a few days, so let me
see
if I
can
assist.

In Tom's sample database, the record source query for the
"fdlgAskForDatesAndCity2" form is "qryShowMinAndMaxOrderDate".
Did
you
change this to the query that you want to use -- which I assume
is
"querytester1"?

--

Ken Snell
<MS ACCESS MVP>



Hello Tom,

I imported my tables and applied one query called
querytester1.
When
i
use
the form fdlgAskfor Datesand City2, my query will run but
display
all
records
since i inserted my query in this part of the code. How do get
the
form
to
use querytester1 and have the dates parameter apply to it?

:

Hi JK,

You're welcome. If you get stuck, please post back.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, This download is pretty cool. I am not great in VB but
i
will
try
to
modify the forms you have in this download to my
application.
If
you
dont
mind, i may need your help in the event i cant through it
but
thank
you
for
opening my thoughts on this matter.

hymadyson, thanks for the reply as well!

 

[Ken Snell \(MVP\)]

Any book on SQL should be helpful in learning how to write queries. One good
book I've used often is "SQL Queries for Mere Mortals" by Michael Hernandez
and John Viescas. (A new Second Edition by John Viescas will be published
later this year.)

There is no way to hide the SortOrder column through the export process
itself. You'd need to use EXCEL Automation after the file is created to open
the file and delete that column from the EXCEL file's spreadsheet.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
Where can i learn more on working and creating these type of query's"? The
export to excel works great and i would like to hide the column titled
SortOrder. Sorry for late response, discussion boards technical problems
occured.

You can add more criteria to the query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*") AND
((VendorInv.InvDesc) Like "AUDIT" & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*")) AND
((TT.InvDesc) Like "AUDIT" & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

 

[Ken Snell \(MVP\)]

I must change my answer. There is a way for you to export the data without
the SortOrder field being exported as well.

Instead of exporting the UNION query directly, save it into your database
and give it a name (such as qryUnion). Now create a new query with this SQL
statement:

SELECT qryUnion.EnterDate, qryUnion.VendorID, qryUnion.VendorName,
qryUnion.InvoiceID, qryUnion.VendorCtry, qryUnion.VendorCostCenter,
qryUnion.PerDate, qryUnion.InvDesc, qryUnion.InvAmt,
qryUnion.InvoiceCode
FROM qryUnion
ORDER BY qryUnion.SortOrder;

Then export the above query.
--

Ken Snell
<MS ACCESS MVP>

Any book on SQL should be helpful in learning how to write queries. One
good book I've used often is "SQL Queries for Mere Mortals" by Michael
Hernandez and John Viescas. (A new Second Edition by John Viescas will be
published later this year.)

There is no way to hide the SortOrder column through the export process
itself. You'd need to use EXCEL Automation after the file is created to
open the file and delete that column from the EXCEL file's spreadsheet.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
Where can i learn more on working and creating these type of query's"?
The
export to excel works great and i would like to hide the column titled
SortOrder. Sorry for late response, discussion boards technical problems
occured.

You can add more criteria to the query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*") AND
((VendorInv.InvDesc) Like "AUDIT" & "*"))
UNION ALL
SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*")) AND
((TT.InvDesc) Like "AUDIT" & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;