format text in code

[javablood]

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:


SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

TIA

 

[pietlinden]

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table.  Here is the main part of thecode:

    SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
    "INTO [Monthly costs tracking] FROM [All Invoices]" & _
    "GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.  
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line.  How do I format this in the code to make sure I
can get the zero to appear in the make table?  Not an expert in programming
so please bear with me.

TIA

why you would create tables of summary query results is questionable,
but if you use Format$() you can coerce the numbers to be treated as
text.

 

[javablood]

I am not sure I follow you. The resulting table is exported to Excel for
other purposes. Anyway, the numbers are already formatted as text in the
main table but the zero does not come out in the make table, e.g., 21.0
becomes 21. I tried using user-defined string format but it appeared that I
was using it incorrectly.
--
javablood

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

TIA

why you would create tables of summary query results is questionable,
but if you use Format$() you can coerce the numbers to be treated as
text.

 

[pietlinden]

I am not sure I follow you.  The resulting table is exported to Excel for
other purposes.  Anyway, the numbers are already formatted as text in the
main table but the zero does not come out in the make table, e.g., 21.0
becomes 21.  I tried using user-defined string format but it appeared that I
was using it incorrectly.
--
javablood

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table.  Here is the main part ofthe code:
    SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
    "INTO [Monthly costs tracking] FROM [All Invoices]" & _
    "GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.
The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.  
The problem is that the zeros are lost so that 21.0 becomes 21 and itmisses
up programs down the line.  How do I format this in the code to make sure I
can get the zero to appear in the make table?  Not an expert in programming
so please bear with me.
TIA

why you would create tables of summary query results is questionable,
but if you use Format$() you can coerce the numbers to be treated as
text.

did you check the formatting of the columns in Excel?

 

[javablood]

yes, it is General but even if formatted as text it will still only be 21
because that is what came from Access unless it is re-typed as 21.0.
--
javablood

I am not sure I follow you. The resulting table is exported to Excel for
other purposes. Anyway, the numbers are already formatted as text in the
main table but the zero does not come out in the make table, e.g., 21.0
becomes 21. I tried using user-defined string format but it appeared that I
was using it incorrectly.
--
javablood

On Jun 25, 11:01 am, javablood <[email protected]>
wrote:
I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:

SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

why you would create tables of summary query results is questionable,
but if you use Format$() you can coerce the numbers to be treated as
text.

did you check the formatting of the columns in Excel?

 

[Klatuu]

By formatting, he means you need to do something like this:

SQL = "SELECT Format([All Invoices].Code, "0.00") As CodeFixed

You can any name you want where I have CodeFixed, but be aware it will cause
the field name in the resulting table to be whatever you use there. You will
not be able to use the name as Code.

Is there a reason for this intermediate step of a maketable query? Why not
just use a select query and export it directly to Excel? Creating and
Deleting make tables does contribute to file size bloat.

 

[javablood]

I tried using the user-defined string formats, e.g., & as a placeholder to
give me either the character (in this case a zero [0]) or nothing but I get a
compile error - 'expected expression' the same if I try using the "0.00".
What gives???

I am not well versed and did not know I could export directly to Excel from
my query. I will look at that. Thanks.
--
javablood

By formatting, he means you need to do something like this:

SQL = "SELECT Format([All Invoices].Code, "0.00") As CodeFixed

You can any name you want where I have CodeFixed, but be aware it will cause
the field name in the resulting table to be whatever you use there. You will
not be able to use the name as Code.

Is there a reason for this intermediate step of a maketable query? Why not
just use a select query and export it directly to Excel? Creating and
Deleting make tables does contribute to file size bloat.
--
Dave Hargis, Microsoft Access MVP

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:


SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

TIA

 

[Klatuu]

Jet SQL understands the Format function, but does not understand named formats.
--
Dave Hargis, Microsoft Access MVP

I tried using the user-defined string formats, e.g., & as a placeholder to
give me either the character (in this case a zero [0]) or nothing but I get a
compile error - 'expected expression' the same if I try using the "0.00".
What gives???

I am not well versed and did not know I could export directly to Excel from
my query. I will look at that. Thanks.
--
javablood

By formatting, he means you need to do something like this:

SQL = "SELECT Format([All Invoices].Code, "0.00") As CodeFixed

You can any name you want where I have CodeFixed, but be aware it will cause
the field name in the resulting table to be whatever you use there. You will
not be able to use the name as Code.

Is there a reason for this intermediate step of a maketable query? Why not
just use a select query and export it directly to Excel? Creating and
Deleting make tables does contribute to file size bloat.
--
Dave Hargis, Microsoft Access MVP

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:


SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

TIA

 

[javablood]

I just changed how I read that field in Excel (21*) so it will see 21 or
21.0. I figure my energy is better focused on using TransferSpreadsheet to
export directly to Excel and reduce file size. Also read - I do not know
Jet SQL from Jet ski and had to figure another way thanks for you help -
yet again
--
javablood

Jet SQL understands the Format function, but does not understand named formats.
--
Dave Hargis, Microsoft Access MVP

I tried using the user-defined string formats, e.g., & as a placeholder to
give me either the character (in this case a zero [0]) or nothing but I get a
compile error - 'expected expression' the same if I try using the "0.00".
What gives???

I am not well versed and did not know I could export directly to Excel from
my query. I will look at that. Thanks.
--
javablood

By formatting, he means you need to do something like this:

SQL = "SELECT Format([All Invoices].Code, "0.00") As CodeFixed

You can any name you want where I have CodeFixed, but be aware it will cause
the field name in the resulting table to be whatever you use there. You will
not be able to use the name as Code.

Is there a reason for this intermediate step of a maketable query? Why not
just use a select query and export it directly to Excel? Creating and
Deleting make tables does contribute to file size bloat.
--
Dave Hargis, Microsoft Access MVP


:

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:


SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

TIA

 

[Klatuu]

Jetski is the Polish version of Jet <g>
--
Dave Hargis, Microsoft Access MVP

I just changed how I read that field in Excel (21*) so it will see 21 or
21.0. I figure my energy is better focused on using TransferSpreadsheet to
export directly to Excel and reduce file size. Also read - I do not know
Jet SQL from Jet ski and had to figure another way thanks for you help -
yet again
--
javablood

Jet SQL understands the Format function, but does not understand named formats.
--
Dave Hargis, Microsoft Access MVP

I tried using the user-defined string formats, e.g., & as a placeholder to
give me either the character (in this case a zero [0]) or nothing but I get a
compile error - 'expected expression' the same if I try using the "0.00".
What gives???

I am not well versed and did not know I could export directly to Excel from
my query. I will look at that. Thanks.
--
javablood


:

By formatting, he means you need to do something like this:

SQL = "SELECT Format([All Invoices].Code, "0.00") As CodeFixed

You can any name you want where I have CodeFixed, but be aware it will cause
the field name in the resulting table to be whatever you use there. You will
not be able to use the name as Code.

Is there a reason for this intermediate step of a maketable query? Why not
just use a select query and export it directly to Excel? Creating and
Deleting make tables does contribute to file size bloat.
--
Dave Hargis, Microsoft Access MVP


:

I am running a make table SQL in VBA and want to be able to format a field to
come out as it appears in the main table. Here is the main part of the code:


SQL = "SELECT [All Invoices].Code, Sum([All Invoices].Amount) AS
SumOfAMOUNT " & _
"INTO [Monthly costs tracking] FROM [All Invoices]" & _
"GROUP BY [All Invoices].Code, [All Invoices].[Month invoice paid], " &
_ . . .etc.

The field "Code" is text but has numbers, e.g., 24.1, 14.2.7, 21.0, etc.
The problem is that the zeros are lost so that 21.0 becomes 21 and it misses
up programs down the line. How do I format this in the code to make sure I
can get the zero to appear in the make table? Not an expert in programming
so please bear with me.

TIA

 

[pietlinden]

check out the code at Access Web. You can open your query in ADO and
use CopyFromRecordset to transfer the data to an Excel template. Dev
has examples of it on his site.

www.mvps.org/access

They're all in the modules section - just look up "Excel" and you'll
find them all. Then you can format everything in your workbook
template and put in any macros etc. (he even shows you how to run them
from Access, I think!)

Pieter

 

[Klatuu]

Dev's code does not address using CopyfromRecordset. It does demonstrate the
basics of using Automation and is a good starting point.

I don't recommend using ADO. Here is an example from one of my apps on how
it is done. This uses good ol'e DAO:

Dim rstItms As Recordset 'Recordset to load ITM Name in Header
Dim qdf As QueryDef 'Query def to load data

Set qdf = CurrentDb.QueryDefs("qselSCCBhdr")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstItms = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Be sure there are records to process
rstItms.MoveLast
rstItms.MoveFirst
lngItmCount = rstItms.RecordCount
If lngItmCount = 0 Then
MsgBox "No Data Found For This Report", vbInformation + vbOKOnly,
"Data Error"
GoTo Build_XL_Report_Exit
End If

'Load Header Data
xlSheet.Cells(2, 1).CopyFromRecordset rstItms
rstItms.Close
Set rstItms = Nothing
Set qdf = Nothing