Hi,
there are several places in this scenario where something can go wrong.
1. when you use a make table query, you have very little control over how
the data types are set up.
I was assuming (silly thing) that you would export the query I gave you.
Instead you used it as the basis for a make table query.
2. The calculated field with CStr([TheField]) needs to be in the final query
that is exported.
3. Your last post showed the numbers as integers, so I assumed (again silly
thing) that you were dealing with long integers. This post shows that you
are dealing with singles. Is this correct? Is prearn.ytdh a single?
4.It would be easier to skip the make table query altogether.
Use a table that you have built specially for this purpose. Set up the data
types as you want them.
Create an export query based on the export table.
Each time you want add data to the export table, you can delete all the
current data before you add the new data.
Here's how:
I will call the table tblExport for this discussion.
tblExport has 3 fields, the first two are text fields for comp and empno,
the 3rd field YTDH is a number field with field size set to single.
5.Create a query based on tblExport
In this query use a calculated field called FinalYTDHC: Str((CInt([YTDH]))
6. Export the query
Note: the order of the steps is important.
The last thing before export is to convert the number to an integer and then
a string - no more changes to YTDH before you export it.
Jeanette Cunningham
DumbCluck said:
Hey Jeanette,
the CStr formula brought the values into the table (500.00, 580.98, etc)
so I changed the formula to CStr(Round(prearn.YTDH)). It converts the
values to
a text string in the table. (no decimals)
Then when I run the query, there are no decimals visible when I visually
check out
the output.
When I export it to a text file, the %$#@*&% decimal is back. Demons I
swear!
Jeanette Cunningham said:
Using your extra info, here is the problem.
The field YTDH would be better done slightly differently.
My suggestion is to scrap the CInt and the +0.5
Replace with YTDH:CStr([prearn].[ytdhours])
Create a new export spec using a query with Comp, EmpNo, YTDH ( the new
YTDH)
This will export all 3 fields as text.
Exporting YTDH as text, will stop the export process from adding decimal
points.
Jeanette Cunningham
DumbCluck said:
Thanks for taking the time Jeanette,
step 1: ODBC to table and run a Make-Table query.
heres a sample of what the data looks like (w/field format):
Comp EmpNo YTDH
TSE (t) 0003 (t) 568 (n) I do not modify the format
TSE 0012 640
TSE 1287 47
The YTDH is the offending field. Here is the formula to get that data:
YTDH:Cint(prearn.ytdhours + .05)
(I tried Round, but thought this would eliminate the decimal problem)
When I look at the data in table view, there is no decimals.
When I run the query to check the data before the text export, there is
no
decimal.
My export specs have the YTDH field as a 4 byte field.
Here is what the text export looks like:
TSE 0003 568.
TSE 0012 640.
TSE 1287 47.
Thanks again for taking the time to look at this:
regards,
:
Please describe how you are doing the export.
When I do the export using the query I described in my previous
answer, I
get the number without any decimal points in the text file.
So it is possible to do what you want, we just need to look more
closely
at
each step you are using.
Would you post a couple of sample lines showing the query results
before
export.
You don't need to include all columns, 2 columns with the problem
field
would be sufficient.
Would you list the steps you use when you use from the export wizard.
Jeanette Cunningham
The phunny thing is that the decimal point is not there when I look
at
the
result of the query. But when I export it to a text file, the
decimal
point
suddenly appears. I have tried a bunch of things to eliminate it
but
have
not resorted to manually deleting it. (time consuming).
:
Hi,
here is a way to do what you want.
Here is my test method.
Create a new table called tblSnowii
3 fields
TheDate - date/time
TheNumber - double
Field3 - long integer
put in this data
TheDate TheNumber Field3
5/4/2008 193.25 456789
3/2/2008 80.49 301245
Create a query on tblSnowii
Drag all 3 fields on to the query grid
In a new column of the query in the Field row type the following
Expr1: CInt([TheNumber])
Save the query and switch to datasheet view.
You will see that Expr1 contains the numbers 193 and 80 without any
decimal
point.
You can delete the column for TheNumber from the query.
You can replace Expr1 with the name you want for the export.
Jeanette Cunningham
Hey Jeanette,
I am exporting from a query. But I have the Make-table query
(table)
linked
to the final export. The difference with the 193. & 80.0 column
of
data
is
that I have to use the round function to strip off the decimal
info.
When
I
export the data from the make table query, the decimal does not
show.
When I
join that table to the final export query, the decimal is there.
It
seems
that the round function is causing problems for me because the
fields
to
the
right are numeric field that have a formula applied to them and
they
do
not
carry a decimal.
12/30/189911311 193. 143588 143588
12/30/189911311 80. 143588 143588
I changed the regional setting to carry 0 values after the
decimal
in
hopes
that this would fix the problem, but you can see all it did was
eliminate
the
zero after the 80.
I am going to try to use a formula rather than the round function
and
see
if
that will help.
regards,
:
It is usually better to export a query instead of a table,
because
the
query
gives you more contorl over how you can format each column.
Looking at the middle column 193. and 80.0 - are these the
records
where
you
don't want the decimal point?
Can you put all the fields that you want to export into a query
and
post
the
sample of the results of the query here.
(i.e. don't use a make table query at all - put all the export
data
into
a
query.)
We should be able to suggest how to format that field in the
query
to
remove the decimal point, as long as you post a sample of how
you
want
it
to
look after it goes into the text file.
Jeanette Cunningham
message
I created a Make-Table query and formated the field to text (I
have
to
modify this everytime I run the query because it changes it
back
to
a
decimal field.
I linked my new table to my query and I am still getting the
$#%^&*'ing
decimal
point (sample below)
12/30/189911314 193. 1234567890
12/23/199211313 80.0 1234567890
This is a fixed format record that must be exactly as
specified
(transaction
deck).
I am ready to smash something! PLEASE HELP!
:
I am doing it manually. the export query joined to a link
table
and
I
am
using export specifications. The funny thing is that when I
export
the
query, the decimal point does not show up in the resulting
text
file.
But
when I add the query to the final query, it is there. If I
try
to
perform a
formula on the final query, I am unable to export (greyed
out).
Crazy!
I think I am going to create the make-table query where I am
have
control
over the data format, and then use that table in the final
query.
This
is a
688 byte record and I have a bunch of time already invested
in
this.
Now
I
have to begin anew.
thanks for your help
