Re: Export specification Data Types

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Apparantly there was some confusion going on between the redesigned export
specs and the query formatting. I've got it straightened out now and the
output looks just fine.

Thanks again,

Bill R

Ken Snell (MVP) wrote:
First, I went back and looked at some old posts of mine, and I did find one
from 2002 that said you could set data type in an export specification. That
post was from my "unlearned" days... sorry for the misleading info.

Second, when ACCESS exports numeric data, it will default to using "0.00" as
the export format, regardless of how you've "cast" the number by a CLng or
other function. You must explicitly format an integer value to be an
integer:

SELECT [Type], ReceiveIndexID, PayIndexID,
Format([Tenor], "0") AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable;

Third, I recommend that you not use Type as the name of a field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

If you must use it as a field name, then always surround it with [ ]
characters so that Jet and ACCESS will know it's not the reserved property
that you mean.

Ken,

[quoted text clipped - 51 lines]

Bill R

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via http://www.accessmonster.com
.

Relevant Pages

  • Re: Querying Access Date fields
    ... > when your application passes a string containing a SQL statement to the Jet ... > was built on some other system there's no way to know that unless the format ... > will be no confusion. ...
    (microsoft.public.vb.database)
  • Re: Excel to Access
    ... >It is Jet, not MS Access, that determines the Excel data ... >Jet code and go directly to the Excel data? ... >> ACCESS ignores what the format of the EXCEL column ... >Change the cell format to dd mmm yyyy. ...
    (microsoft.public.access.externaldata)
  • Re: A2007 ADPs
    ... older format .MDB will never change again. ... ACCDB is a new version of Jet. ... could have been called Jet 5 and used the MDB format, ... ADP, according to reports from those who were trying desperately to ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Query between 2 dates
    ... When you run this as a query, JET calls the Expression Service which figures out the values and substitutes them, so the query runs. ... When you include literal date values in a string, they must be delimited with #, and they should be formatted in the native JET format The Formatfunction call performs this operation. ... Dim rs AS DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: A2007 ADPs
    ... I should have added that while the format for the storing of forms/modules ... and macros has changed in the previous versions of JET, ... changements might likely occur in the future for ACCDB). ... open the forms and modules of a more recent version of MDB, ...
    (microsoft.public.access.adp.sqlserver)