Formatting Date Cell with No Values
 

Im using the TransferSpreadsheet Action in my access macro db to
automatically transfer data to an Excel spreadsheet. The data gets
transferred to the worksheet €œqryRequirement _Status_NotComp_b€� (Sheet 1).
In my adjacent worksheet €œNot Completed Requirement€� (Sheet 2), have
formulated cells to copy data from Sheet 1. One column heading is €œDate
Completed€�. The following formula is in the date cell of Sheet 2:
=+qryRequirement_Status_NotComp_b!F2; formatted as date fields. Excel 2003

In Sheet 1, not all the cells under the DateCompleted contain values. When
the data gets copied to Sheet 2, those date values that are empty in Sheet 1
result in a 0 (zero) in Sheet 2; resulting in 1/0/00. I want those cells
with no values to remain empty in Sheet 2. I havent used any coding in
Excel, and was wondering if there is a way to get this accomplished. Any
suggestions would be helpful.

Formatting Date Cell with No Values
 

One way would be to change your reference formula to include an IF statement
to trap for the blank cells on the first sheet. For instance:

=IF(qryRequirement_Status_NotComp_b!F2="","",qryRe quirement_Status_NotComp_b!F2)

(I'm not sure why you had a "+" sin in front of your reference, assumed it
was an error).

Hope that helps.

"Curtis" wrote:

Im using the TransferSpreadsheet Action in my access macro db to
automatically transfer data to an Excel spreadsheet. The data gets
transferred to the worksheet €œqryRequirement _Status_NotComp_b€� (Sheet 1).
In my adjacent worksheet €œNot Completed Requirement€� (Sheet 2), have
formulated cells to copy data from Sheet 1. One column heading is €œDate
Completed€�. The following formula is in the date cell of Sheet 2:
=+qryRequirement_Status_NotComp_b!F2; formatted as date fields. Excel 2003

In Sheet 1, not all the cells under the DateCompleted contain values. When
the data gets copied to Sheet 2, those date values that are empty in Sheet 1
result in a 0 (zero) in Sheet 2; resulting in 1/0/00. I want those cells
with no values to remain empty in Sheet 2. I havent used any coding in
Excel, and was wondering if there is a way to get this accomplished. Any
suggestions would be helpful.

Formatting Date Cell with No Values
 

Yes, your post did exactly what I wanted. I copied this formula into the
Date Completed cell of Sheet 2 and only blank cells remained for those blank
cells in Sheet 1. Thanks for your professional reponse! (the + symbol was
there to "copy" the contents of the same cell in Sheet 2)....

"andy62" wrote:

One way would be to change your reference formula to include an IF statement
to trap for the blank cells on the first sheet. For instance:

=IF(qryRequirement_Status_NotComp_b!F2="","",qryRe quirement_Status_NotComp_b!F2)

(I'm not sure why you had a "+" sin in front of your reference, assumed it
was an error).

Hope that helps.

"Curtis" wrote:

Im using the TransferSpreadsheet Action in my access macro db to
automatically transfer data to an Excel spreadsheet. The data gets
transferred to the worksheet €œqryRequirement _Status_NotComp_b€� (Sheet 1).
In my adjacent worksheet €œNot Completed Requirement€� (Sheet 2), have
formulated cells to copy data from Sheet 1. One column heading is €œDate
Completed€�. The following formula is in the date cell of Sheet 2:
=+qryRequirement_Status_NotComp_b!F2; formatted as date fields. Excel 2003

In Sheet 1, not all the cells under the DateCompleted contain values. When
the data gets copied to Sheet 2, those date values that are empty in Sheet 1
result in a 0 (zero) in Sheet 2; resulting in 1/0/00. I want those cells
with no values to remain empty in Sheet 2. I havent used any coding in
Excel, and was wondering if there is a way to get this accomplished. Any
suggestions would be helpful.