Ken Snell (MVP) said:How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:I run a query that returns as one of its results a parameter value ([Start
Date]). When I export the resulting query to Excel, the date value does
not
transfer. The cells in Excel display blanks. How can I get the date value
to
display?
Ray S. said:Oh, I'm just clicking on Tools, Office Links, Analyze It With MS Excel. Is
that the mistake?
Ken Snell (MVP) said:How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ken Snell (MVP) said:That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:Oh, I'm just clicking on Tools, Office Links, Analyze It With MS Excel. Is
that the mistake?
Ken Snell (MVP) said:How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ray S. said:I don't know if this will help much. The relevant part is this:
SELECT FINALWeekly.Expr1
FROM FINALWeekly;
The Expr1 is [Start Date] which is a parameter. That is, when I run the
query, a dialog pops up asking me to type in the Start Date. I do, and it
shows up on the query result exactly as I typed it in. But, when I do the
export to Excel, the result is blanks.
Ken Snell (MVP) said:That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy
the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:Oh, I'm just clicking on Tools, Office Links, Analyze It With MS Excel.
Is
that the mistake?
:
How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value
does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ken Snell (MVP) said:That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:Oh, I'm just clicking on Tools, Office Links, Analyze It With MS Excel. Is
that the mistake?
Ken Snell (MVP) said:How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ken Snell (MVP) said:Post the SQL statement of the FINALWeekly query. Let's see the whole
picture.
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:I don't know if this will help much. The relevant part is this:
SELECT FINALWeekly.Expr1
FROM FINALWeekly;
The Expr1 is [Start Date] which is a parameter. That is, when I run the
query, a dialog pops up asking me to type in the Start Date. I do, and it
shows up on the query result exactly as I typed it in. But, when I do the
export to Excel, the result is blanks.
Ken Snell (MVP) said:That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy
the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Oh, I'm just clicking on Tools, Office Links, Analyze It With MS
Excel. Is
that the mistake?
:
How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value
does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Pieter Wijnen said:Basically you need to use Forms Controls to be able to export Parameter
Queries
ie
Parameters Forms!MyForm!mydate Date;
SELECT * FROM MyTable WHERE MyDateField = Forms!MyForm!mydate;
HtH
Pieter
Ken Snell (MVP) said:Post the SQL statement of the FINALWeekly query. Let's see the whole
picture.
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:I don't know if this will help much. The relevant part is this:
SELECT FINALWeekly.Expr1
FROM FINALWeekly;
The Expr1 is [Start Date] which is a parameter. That is, when I run the
query, a dialog pops up asking me to type in the Start Date. I do, and it
shows up on the query result exactly as I typed it in. But, when I do the
export to Excel, the result is blanks.
:
That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy
the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Oh, I'm just clicking on Tools, Office Links, Analyze It With MS
Excel. Is
that the mistake?
:
How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value
does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ray S. said:OK, this is about to get a lot more complicated, so here it goes:
The underlying tables are:
MX_UNITS
MX_RESOURCES
MX_RESOURCE_CLASSES
MX_TIMESHEETS
It may be more helpful if I show this from the foundation up, so:
First, the
Resources Hours View query:
SELECT MX_RESOURCES.ID, MX_RESOURCES.LAST_NAME, MX_RESOURCES.FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, Sum(MX_TIME_SHEETS.NUM_HRS) AS SumOfNUM_HRS,
MX_UNITS.MANAGER_RES_ID
FROM MX_UNITS INNER JOIN (MX_RESOURCE_CLASSES INNER JOIN (MX_RESOURCES
INNER
JOIN MX_TIME_SHEETS ON MX_RESOURCES.ID = MX_TIME_SHEETS.RESOURCE_ID) ON
MX_RESOURCE_CLASSES.CODE = MX_RESOURCES.CLASS_CODE) ON MX_UNITS.CODE =
MX_RESOURCES.UNIT_CODE
GROUP BY MX_RESOURCES.ID, MX_RESOURCES.LAST_NAME, MX_RESOURCES.FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, MX_UNITS.MANAGER_RES_ID,
MX_RESOURCES.ADMIN_AREA_CODE, MX_RESOURCE_CLASSES.SHORT_DESC,
DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]), MX_TIME_SHEETS.TIME_DATE,
MX_RESOURCES.ACTIVE_FLAG_YN
HAVING (((MX_RESOURCES.ADMIN_AREA_CODE)="1") AND
((MX_RESOURCE_CLASSES.SHORT_DESC)="INTERNAL") AND
((DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]))=2007) AND
((MX_TIME_SHEETS.TIME_DATE)>=[Start Date] And
(MX_TIME_SHEETS.TIME_DATE)<=[End Date]) AND
((MX_RESOURCES.ACTIVE_FLAG_YN)="Y"))
ORDER BY MX_RESOURCES.LAST_NAME,
DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]);
Next, the
REQUIREDWeekly query:
SELECT [Resource Hours View].LAST_NAME, [Resource Hours View].FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, Sum([Resource Hours View].SumOfNUM_HRS) AS
SumOfSumOfNUM_HRS, MX_RESOURCES.REQUIRED_HRS, MX_RESOURCES_1.LAST_NAME,
MX_RESOURCES_1.FIRST_NAME, MX_RESOURCES_1.EMAIL_ADDRESS
FROM ([Resource Hours View] INNER JOIN MX_RESOURCES ON [Resource Hours
View].ID = MX_RESOURCES.ID) INNER JOIN MX_RESOURCES AS MX_RESOURCES_1 ON
[Resource Hours View].MANAGER_RES_ID = MX_RESOURCES_1.ID
GROUP BY [Resource Hours View].LAST_NAME, [Resource Hours
View].FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, MX_RESOURCES.REQUIRED_HRS,
MX_RESOURCES_1.LAST_NAME, MX_RESOURCES_1.FIRST_NAME,
MX_RESOURCES_1.EMAIL_ADDRESS
ORDER BY [Resource Hours View].LAST_NAME;
And Last, the
FINALWeekly query:
SELECT REQUIREDWeekly.[Resource Hours View].LAST_NAME,
REQUIREDWeekly.[Resource Hours View].FIRST_NAME,
REQUIREDWeekly.MX_RESOURCES.EMAIL_ADDRESS, REQUIREDWeekly.REQUIRED_HRS,
REQUIREDWeekly.SumOfSumOfNUM_HRS, [REQUIRED_HRS]-[SumOfSumOfNUM_HRS] AS
Missing, REQUIREDWeekly.MX_RESOURCES_1.LAST_NAME,
REQUIREDWeekly.MX_RESOURCES_1.FIRST_NAME,
REQUIREDWeekly.MX_RESOURCES_1.EMAIL_ADDRESS, [Start Date] AS Expr1
FROM REQUIREDWeekly
WHERE (((REQUIREDWeekly.REQUIRED_HRS)>[SumOfSumOfNUM_HRS]));
I hope this hasn't made my question hopelessly confused.
Ken Snell (MVP) said:That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy
the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:Oh, I'm just clicking on Tools, Office Links, Analyze It With MS Excel.
Is
that the mistake?
:
How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value
does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ken Snell (MVP) said:OK - this assists us in providing a more complete answer about how parameter
queries are used and handled.
The use of parameters in a query, when the user must enter the parameter
manually (such as in your situation) or when the query reads the value of
the parameter from a control on a form (e.g., the parameter is
[Forms]![MyFormName]![ControlName]) can create some issues for you when you
want to export the query's results, or when you want to run the query from
VBA code -- the reason is that some methods will not actually evaluate the
parameter for you and thus the parameter either comes out "empty" (your
situation) or the programming fails because you get an error message about a
missing parameter.
If you know that you want to use a parameter value in a query, it's usually
best to try to read the value from a control on an open form (what Pieter is
suggesting). However, for exporting and some programmatic methods, even that
method will not work in every situation. In these other situations, it's
best to programmatically build the actual query's SQL statement, including
the concatenation of the actual real value for the parameter into the
string, assign that SQL string to a stored query, and then export or run
that stored query. The programming to do this is not overly difficult, but
may appear to be a bit challenging to a novice user initially.
I know that you stated that your question was prompted by your observation
of trying to export the one query as a test. If you actually want to do that
on an ongoing basis, we can provide you with some alternative ways of doing
that successfully.
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:OK, this is about to get a lot more complicated, so here it goes:
The underlying tables are:
MX_UNITS
MX_RESOURCES
MX_RESOURCE_CLASSES
MX_TIMESHEETS
It may be more helpful if I show this from the foundation up, so:
First, the
Resources Hours View query:
SELECT MX_RESOURCES.ID, MX_RESOURCES.LAST_NAME, MX_RESOURCES.FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, Sum(MX_TIME_SHEETS.NUM_HRS) AS SumOfNUM_HRS,
MX_UNITS.MANAGER_RES_ID
FROM MX_UNITS INNER JOIN (MX_RESOURCE_CLASSES INNER JOIN (MX_RESOURCES
INNER
JOIN MX_TIME_SHEETS ON MX_RESOURCES.ID = MX_TIME_SHEETS.RESOURCE_ID) ON
MX_RESOURCE_CLASSES.CODE = MX_RESOURCES.CLASS_CODE) ON MX_UNITS.CODE =
MX_RESOURCES.UNIT_CODE
GROUP BY MX_RESOURCES.ID, MX_RESOURCES.LAST_NAME, MX_RESOURCES.FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, MX_UNITS.MANAGER_RES_ID,
MX_RESOURCES.ADMIN_AREA_CODE, MX_RESOURCE_CLASSES.SHORT_DESC,
DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]), MX_TIME_SHEETS.TIME_DATE,
MX_RESOURCES.ACTIVE_FLAG_YN
HAVING (((MX_RESOURCES.ADMIN_AREA_CODE)="1") AND
((MX_RESOURCE_CLASSES.SHORT_DESC)="INTERNAL") AND
((DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]))=2007) AND
((MX_TIME_SHEETS.TIME_DATE)>=[Start Date] And
(MX_TIME_SHEETS.TIME_DATE)<=[End Date]) AND
((MX_RESOURCES.ACTIVE_FLAG_YN)="Y"))
ORDER BY MX_RESOURCES.LAST_NAME,
DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]);
Next, the
REQUIREDWeekly query:
SELECT [Resource Hours View].LAST_NAME, [Resource Hours View].FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, Sum([Resource Hours View].SumOfNUM_HRS) AS
SumOfSumOfNUM_HRS, MX_RESOURCES.REQUIRED_HRS, MX_RESOURCES_1.LAST_NAME,
MX_RESOURCES_1.FIRST_NAME, MX_RESOURCES_1.EMAIL_ADDRESS
FROM ([Resource Hours View] INNER JOIN MX_RESOURCES ON [Resource Hours
View].ID = MX_RESOURCES.ID) INNER JOIN MX_RESOURCES AS MX_RESOURCES_1 ON
[Resource Hours View].MANAGER_RES_ID = MX_RESOURCES_1.ID
GROUP BY [Resource Hours View].LAST_NAME, [Resource Hours
View].FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, MX_RESOURCES.REQUIRED_HRS,
MX_RESOURCES_1.LAST_NAME, MX_RESOURCES_1.FIRST_NAME,
MX_RESOURCES_1.EMAIL_ADDRESS
ORDER BY [Resource Hours View].LAST_NAME;
And Last, the
FINALWeekly query:
SELECT REQUIREDWeekly.[Resource Hours View].LAST_NAME,
REQUIREDWeekly.[Resource Hours View].FIRST_NAME,
REQUIREDWeekly.MX_RESOURCES.EMAIL_ADDRESS, REQUIREDWeekly.REQUIRED_HRS,
REQUIREDWeekly.SumOfSumOfNUM_HRS, [REQUIRED_HRS]-[SumOfSumOfNUM_HRS] AS
Missing, REQUIREDWeekly.MX_RESOURCES_1.LAST_NAME,
REQUIREDWeekly.MX_RESOURCES_1.FIRST_NAME,
REQUIREDWeekly.MX_RESOURCES_1.EMAIL_ADDRESS, [Start Date] AS Expr1
FROM REQUIREDWeekly
WHERE (((REQUIREDWeekly.REQUIRED_HRS)>[SumOfSumOfNUM_HRS]));
I hope this hasn't made my question hopelessly confused.
Ken Snell (MVP) said:That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query in
design view, click on Query View on toolbar, select SQL View, and copy
the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Oh, I'm just clicking on Tools, Office Links, Analyze It With MS Excel.
Is
that the mistake?
:
How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date value
does
not
transfer. The cells in Excel display blanks. How can I get the date
value
to
display?
Ray S. said:Great...well, we have decided that it would be very useful to get the
results
of the query exported to Excel. While we could type and copy in the
parameter
date, it would be nice to handle this programmatically.
Ken Snell (MVP) said:OK - this assists us in providing a more complete answer about how
parameter
queries are used and handled.
The use of parameters in a query, when the user must enter the parameter
manually (such as in your situation) or when the query reads the value of
the parameter from a control on a form (e.g., the parameter is
[Forms]![MyFormName]![ControlName]) can create some issues for you when
you
want to export the query's results, or when you want to run the query
from
VBA code -- the reason is that some methods will not actually evaluate
the
parameter for you and thus the parameter either comes out "empty" (your
situation) or the programming fails because you get an error message
about a
missing parameter.
If you know that you want to use a parameter value in a query, it's
usually
best to try to read the value from a control on an open form (what Pieter
is
suggesting). However, for exporting and some programmatic methods, even
that
method will not work in every situation. In these other situations, it's
best to programmatically build the actual query's SQL statement,
including
the concatenation of the actual real value for the parameter into the
string, assign that SQL string to a stored query, and then export or run
that stored query. The programming to do this is not overly difficult,
but
may appear to be a bit challenging to a novice user initially.
I know that you stated that your question was prompted by your
observation
of trying to export the one query as a test. If you actually want to do
that
on an ongoing basis, we can provide you with some alternative ways of
doing
that successfully.
--
Ken Snell
<MS ACCESS MVP>
Ray S. said:OK, this is about to get a lot more complicated, so here it goes:
The underlying tables are:
MX_UNITS
MX_RESOURCES
MX_RESOURCE_CLASSES
MX_TIMESHEETS
It may be more helpful if I show this from the foundation up, so:
First, the
Resources Hours View query:
SELECT MX_RESOURCES.ID, MX_RESOURCES.LAST_NAME,
MX_RESOURCES.FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, Sum(MX_TIME_SHEETS.NUM_HRS) AS
SumOfNUM_HRS,
MX_UNITS.MANAGER_RES_ID
FROM MX_UNITS INNER JOIN (MX_RESOURCE_CLASSES INNER JOIN (MX_RESOURCES
INNER
JOIN MX_TIME_SHEETS ON MX_RESOURCES.ID = MX_TIME_SHEETS.RESOURCE_ID) ON
MX_RESOURCE_CLASSES.CODE = MX_RESOURCES.CLASS_CODE) ON MX_UNITS.CODE =
MX_RESOURCES.UNIT_CODE
GROUP BY MX_RESOURCES.ID, MX_RESOURCES.LAST_NAME,
MX_RESOURCES.FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, MX_UNITS.MANAGER_RES_ID,
MX_RESOURCES.ADMIN_AREA_CODE, MX_RESOURCE_CLASSES.SHORT_DESC,
DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]),
MX_TIME_SHEETS.TIME_DATE,
MX_RESOURCES.ACTIVE_FLAG_YN
HAVING (((MX_RESOURCES.ADMIN_AREA_CODE)="1") AND
((MX_RESOURCE_CLASSES.SHORT_DESC)="INTERNAL") AND
((DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]))=2007) AND
((MX_TIME_SHEETS.TIME_DATE)>=[Start Date] And
(MX_TIME_SHEETS.TIME_DATE)<=[End Date]) AND
((MX_RESOURCES.ACTIVE_FLAG_YN)="Y"))
ORDER BY MX_RESOURCES.LAST_NAME,
DatePart('yyyy',[MX_TIME_SHEETS]![TIME_DATE]);
Next, the
REQUIREDWeekly query:
SELECT [Resource Hours View].LAST_NAME, [Resource Hours
View].FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, Sum([Resource Hours View].SumOfNUM_HRS) AS
SumOfSumOfNUM_HRS, MX_RESOURCES.REQUIRED_HRS, MX_RESOURCES_1.LAST_NAME,
MX_RESOURCES_1.FIRST_NAME, MX_RESOURCES_1.EMAIL_ADDRESS
FROM ([Resource Hours View] INNER JOIN MX_RESOURCES ON [Resource Hours
View].ID = MX_RESOURCES.ID) INNER JOIN MX_RESOURCES AS MX_RESOURCES_1
ON
[Resource Hours View].MANAGER_RES_ID = MX_RESOURCES_1.ID
GROUP BY [Resource Hours View].LAST_NAME, [Resource Hours
View].FIRST_NAME,
MX_RESOURCES.EMAIL_ADDRESS, MX_RESOURCES.REQUIRED_HRS,
MX_RESOURCES_1.LAST_NAME, MX_RESOURCES_1.FIRST_NAME,
MX_RESOURCES_1.EMAIL_ADDRESS
ORDER BY [Resource Hours View].LAST_NAME;
And Last, the
FINALWeekly query:
SELECT REQUIREDWeekly.[Resource Hours View].LAST_NAME,
REQUIREDWeekly.[Resource Hours View].FIRST_NAME,
REQUIREDWeekly.MX_RESOURCES.EMAIL_ADDRESS, REQUIREDWeekly.REQUIRED_HRS,
REQUIREDWeekly.SumOfSumOfNUM_HRS, [REQUIRED_HRS]-[SumOfSumOfNUM_HRS] AS
Missing, REQUIREDWeekly.MX_RESOURCES_1.LAST_NAME,
REQUIREDWeekly.MX_RESOURCES_1.FIRST_NAME,
REQUIREDWeekly.MX_RESOURCES_1.EMAIL_ADDRESS, [Start Date] AS Expr1
FROM REQUIREDWeekly
WHERE (((REQUIREDWeekly.REQUIRED_HRS)>[SumOfSumOfNUM_HRS]));
I hope this hasn't made my question hopelessly confused.
:
That should give you the desired results.
Post the SQL statement of the query that you're exporting (open query
in
design view, click on Query View on toolbar, select SQL View, and copy
the
text that you see in the next window, and paste the text in your reply
here).
--
Ken Snell
<MS ACCESS MVP>
Oh, I'm just clicking on Tools, Office Links, Analyze It With MS
Excel.
Is
that the mistake?
:
How are you doing the export -- TransferSpreadsheet? other means?
--
Ken Snell
<MS ACCESS MVP>
I run a query that returns as one of its results a parameter value
([Start
Date]). When I export the resulting query to Excel, the date
value
does
not
transfer. The cells in Excel display blanks. How can I get the
date
value
to
display?