Quantcast

Query Report Question

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Query Report Question

Boice, Grant
Morning Group,

My question is concerning about query reports.   I have a query report that is
being outputted to Excel.   My dilemma is this...   I have a field, serial
number, that is 6 alpha-numeric characters long.   There are cases, where our
serial number has leading zeroes.  When I run the query and the data is being
outputted, Excel treats this field as a numeric, not as a text field.
Currently, I am using a derived variable surrounding quotes around the field
to be fed into Excel.  When the data is being displayed in Excel, the quotes
are visible.  (This is acceptable to our user community, but I would like
eliminate the quotes if possible.)

Is there a way that I can force the query to convince Excel that the leading
zeroes are necessary and treat the field as text?

Just asking...

Thanks in advance!

Grant W. Boice, Jr.
Programmer/Analyst
Matheson-Trigas, Inc.
150 Allen Road, Ste. 302
Basking Ridge, NJ  07920
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

{Blocked Content} RE: Query Report Question

Simon Graham
Warning: This message has had one or more attachments removed
Warning: (not named).
Warning: Please read the "AngelicHost-Attachment-Warning.txt" attachment(s)
for more information.

From memory if you use ="012345" it should keep the leading zero.



Simon

________________________________

From: [hidden email] on behalf of Boice, Grant
Sent: Mon 08/10/2007 15:45
To: Sbsolutions (E-mail)
Subject: [sbs] Query Report Question



Morning Group,

My question is concerning about query reports.   I have a query report that is
being outputted to Excel.   My dilemma is this...   I have a field, serial
number, that is 6 alpha-numeric characters long.   There are cases, where our
serial number has leading zeroes.  When I run the query and the data is being
outputted, Excel treats this field as a numeric, not as a text field.
Currently, I am using a derived variable surrounding quotes around the field
to be fed into Excel.  When the data is being displayed in Excel, the quotes
are visible.  (This is acceptable to our user community, but I would like
eliminate the quotes if possible.)

Is there a way that I can force the query to convince Excel that the leading
zeroes are necessary and treat the field as text?

Just asking...

Thanks in advance!

Grant W. Boice, Jr.
Programmer/Analyst
Matheson-Trigas, Inc.
150 Allen Road, Ste. 302
Basking Ridge, NJ  07920
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
This is a message from the MailScanner E-Mail Virus Protection Service
----------------------------------------------------------------------
The original e-mail attachment "winmail.dat"
was believed to be infected by a virus and has been replaced by this warning
message.

If you wish to receive a copy of the *infected* attachment, please
e-mail helpdesk and include the whole of this message
in your request. Alternatively, you can call them, with
the contents of this message to hand when you call.

At Mon Oct  8 07:30:42 2007 the virus scanner said:
   Could not parse Outlook Rich Text attachment

Note to Help Desk: Look on the AngelicHost MailScanner in
/home/virtual/site2/fst/var/spool/mail.quarantine/20071008 (message
l98EUd2G028949).
--
Postmaster
MailScanner thanks transtec Computers for their support
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query Report Question

Kevin King-2
In reply to this post by Boice, Grant
I haven't found a way to make this happen with the DDE transfer to Excel
that happens with the /QRD tool.  Sorry man.  If you build the item and then
use TU.DOWNLOAD to transfer it and TU.LAUNCH.APP to launch Excel, then the
="xxx" formulae are hidden.  But that's a lot more difficult than the Excel
option.

-K
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query Report Question

TPellitieri
In reply to this post by Boice, Grant
Grant Boice wrote on 10/08/2007 10:45:28 AM:

> I have a query report that is being outputted to Excel. ...
> Is there a way that I can force the query to convince Excel
> that the leading zeroes are necessary and treat the field
> as text?

This is one of the problems with exporting to Excel.  I have similar
problems with part numbers in the numeric format XX-XXXX, where the first
pair is between 01 and 12.  Excel treats these as dates, and displays them
as MMM-YY (!)

You could try one of two alternatives:

1) Prefix the field with a single quote (e.g., '012345).  If you type that
in Excel, it treats the field as Text.

2) Use ="012345" - This will evaluate as a string instead of a number.

Hope one of these works.

--Tom Pellitieri
  Toledo, Ohio
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Query Report Question

Boice, Grant
In reply to this post by Boice, Grant
That's what I thought... Like I stated in my posting, just asking...

Thanks, Kevin!

-- Grant

P.S.  B. Hollar says "Hello!"



-----Original Message-----
From: [hidden email]
[mailto:[hidden email]]On Behalf Of Kevin King
Sent: Monday, October 08, 2007 11:01 AM
To: [hidden email]
Subject: Re: [sbs] Query Report Question


I haven't found a way to make this happen with the DDE transfer to Excel
that happens with the /QRD tool.  Sorry man.  If you build the item and then
use TU.DOWNLOAD to transfer it and TU.LAUNCH.APP to launch Excel, then the
="xxx" formulae are hidden.  But that's a lot more difficult than the Excel
option.

-K
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Query Report Question

Boice, Grant
In reply to this post by Boice, Grant
Tom,

I am currently using option #2.  Just thought I ask the group if there is a more eloquent way.

Thanks Tom!

Grant

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]]On Behalf Of
[hidden email]
Sent: Monday, October 08, 2007 11:02 AM
To: [hidden email]
Subject: Re: [sbs] Query Report Question


Grant Boice wrote on 10/08/2007 10:45:28 AM:

> I have a query report that is being outputted to Excel. ...
> Is there a way that I can force the query to convince Excel
> that the leading zeroes are necessary and treat the field
> as text?

This is one of the problems with exporting to Excel.  I have similar
problems with part numbers in the numeric format XX-XXXX, where the first
pair is between 01 and 12.  Excel treats these as dates, and displays them
as MMM-YY (!)

You could try one of two alternatives:

1) Prefix the field with a single quote (e.g., '012345).  If you type that
in Excel, it treats the field as Text.

2) Use ="012345" - This will evaluate as a string instead of a number.

Hope one of these works.

--Tom Pellitieri
  Toledo, Ohio
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Query Report Question

Dave Davis-3
This is a cumbersome solution, but:

1. Start out with a spreadsheet with the column types defined as Text
instead of General.
2. Export to GUI instead of EXCEL
3. Copy and paste to spreadsheet from the GUI window.

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Boice, Grant
Sent: Monday, October 08, 2007 11:15 AM
To: [hidden email]
Subject: RE: [sbs] Query Report Question

Tom,

I am currently using option #2.  Just thought I ask the group if there
is a more eloquent way.

Thanks Tom!

Grant

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]]On Behalf Of
[hidden email]
Sent: Monday, October 08, 2007 11:02 AM
To: [hidden email]
Subject: Re: [sbs] Query Report Question


Grant Boice wrote on 10/08/2007 10:45:28 AM:

> I have a query report that is being outputted to Excel. ...
> Is there a way that I can force the query to convince Excel that the
> leading zeroes are necessary and treat the field as text?

This is one of the problems with exporting to Excel.  I have similar
problems with part numbers in the numeric format XX-XXXX, where the
first pair is between 01 and 12.  Excel treats these as dates, and
displays them as MMM-YY (!)

You could try one of two alternatives:

1) Prefix the field with a single quote (e.g., '012345).  If you type
that in Excel, it treats the field as Text.

2) Use ="012345" - This will evaluate as a string instead of a number.

Hope one of these works.

--Tom Pellitieri
  Toledo, Ohio
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
-------
SBSolutions mailing list
[hidden email]
To unsubscribe please visit http://listserver.u2ug.org/
Loading...