Using an Iif statement in a Select query, I am allocating unit costs -
dollars and cents to two decimal places - to Year fields - 2001, 2002,
etc. When the query results are ultimately Appended to a table, the
cost is rounded to whole dollars, e.g. 5.75 ends up as 6.00. The
table's data type for this allocated unit cost is Field Size - Long
Interger, Format - Standard, Decimal Places - 4. What am I doing
incorrectly? How can I preserve the decimal formatting in the Append
query? Thank you for any advice.

Change the field type to Currency. A long integer by definition does not
include a fractional part. BTW: if you have fields named 2001, 2002, etc.,
you have a serious problem with your database design.

--
Brendan Reynolds
Access MVP

"Jon R" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Using an Iif statement in a Select query, I am allocating unit costs -
> dollars and cents to two decimal places - to Year fields - 2001, 2002,
> etc. When the query results are ultimately Appended to a table, the
> cost is rounded to whole dollars, e.g. 5.75 ends up as 6.00. The
> table's data type for this allocated unit cost is Field Size - Long
> Interger, Format - Standard, Decimal Places - 4. What am I doing
> incorrectly? How can I preserve the decimal formatting in the Append
> query? Thank you for any advice.
>

"Jon R" <(E-Mail Removed)> wrote

> Using an Iif statement in a Select query, I am
> allocating unit costs - dollars and cents to two
> decimal places - to Year fields - 2001, 2002,
> etc. When the query results are ultimately
> Appended to a table, the cost is rounded to
> whole dollars, e.g. 5.75 ends up as 6.00. The
> table's data type for this allocated unit cost is
> Field Size - Long Interger, Format - Standard,
> Decimal Places - 4. What am I doing incorrectly?
> How can I preserve the decimal formatting in
> the Append query? Thank you for any advice.

An Integer or Long Integer, by definition, has no decimal places.

Format only controls the display, not the definition or storage of the
value. You can display an Integer in a Format that shows decimal places, but
as you've found out, those will be zero.

If you want the decimal fractions to be preserved, you'll have to use a
numeric format that preserves them: Single, Double (both floating point),
Decimal, or Currency.

Larry Linson
Microsoft Access MVP

On Mar 27, 12:18 am, "Larry Linson" <boun...@localhost.not> wrote:
> "Jon R" <jwrasmus...@earthlink.net> wrote
>
> > Using an Iif statement in a Select query, I am
> > allocating unit costs - dollars and cents to two
> > decimal places - to Year fields - 2001, 2002,
> > etc. When the query results are ultimately
> > Appended to a table, the cost is rounded to
> > whole dollars, e.g. 5.75 ends up as 6.00. The
> > table's data type for this allocated unit cost is
> > Field Size - Long Interger, Format - Standard,
> > Decimal Places - 4. What am I doing incorrectly?
> > How can I preserve the decimal formatting in
> > the Append query? Thank you for any advice.
>
> An Integer or Long Integer, by definition, has no decimal places.
>
> Format only controls the display, not the definition or storage of the
> value. You can display an Integer in a Format that shows decimal places, but
> as you've found out, those will be zero.
>
> If you want the decimal fractions to be preserved, you'll have to use a
> numeric format that preserves them: Single, Double (both floating point),
> Decimal, or Currency.
>
> Larry Linson
> Microsoft Access MVP

Mr. Reynolds
Will you please elaborate on your comment "serious problem with your
database design?" Can you suggest a "best" way to segregate data in
time periods - including being able to calculate totals for the data
in the time periods. Thank you in advance for the help.

For database design, the article at the following URL is a good starting
point, and has links to some further resources ...

http://office.microsoft.com/en-gb/ac...242471033.aspx

Segregation of data into time periods or other categories is an issue of
presentation rather than of storage, and better handled using queries and/or
report group sections.

Oh .. and my name is Brendan ... 'Mr Reynolds' is my Dad! :-)

--
Brendan Reynolds
Access MVP

"Jon R" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 27, 12:18 am, "Larry Linson" <boun...@localhost.not> wrote:
>> "Jon R" <jwrasmus...@earthlink.net> wrote
>>
>> > Using an Iif statement in a Select query, I am
>> > allocating unit costs - dollars and cents to two
>> > decimal places - to Year fields - 2001, 2002,
>> > etc. When the query results are ultimately
>> > Appended to a table, the cost is rounded to
>> > whole dollars, e.g. 5.75 ends up as 6.00. The
>> > table's data type for this allocated unit cost is
>> > Field Size - Long Interger, Format - Standard,
>> > Decimal Places - 4. What am I doing incorrectly?
>> > How can I preserve the decimal formatting in
>> > the Append query? Thank you for any advice.
>>
>> An Integer or Long Integer, by definition, has no decimal places.
>>
>> Format only controls the display, not the definition or storage of the
>> value. You can display an Integer in a Format that shows decimal places,
>> but
>> as you've found out, those will be zero.
>>
>> If you want the decimal fractions to be preserved, you'll have to use a
>> numeric format that preserves them: Single, Double (both floating point),
>> Decimal, or Currency.
>>
>> Larry Linson
>> Microsoft Access MVP
>
> Mr. Reynolds
> Will you please elaborate on your comment "serious problem with your
> database design?" Can you suggest a "best" way to segregate data in
> time periods - including being able to calculate totals for the data
> in the time periods. Thank you in advance for the help.
>

On Mar 27, 12:18 am, "Larry Linson" <boun...@localhost.not> wrote:
> "Jon R" <jwrasmus...@earthlink.net> wrote
>
> > Using an Iif statement in a Select query, I am
> > allocating unit costs - dollars and cents to two
> > decimal places - to Year fields - 2001, 2002,
> > etc. When the query results are ultimately
> > Appended to a table, the cost is rounded to
> > whole dollars, e.g. 5.75 ends up as 6.00. The
> > table's data type for this allocated unit cost is
> > Field Size - Long Interger, Format - Standard,
> > Decimal Places - 4. What am I doing incorrectly?
> > How can I preserve the decimal formatting in
> > the Append query? Thank you for any advice.
>
> An Integer or Long Integer, by definition, has no decimal places.
>
> Format only controls the display, not the definition or storage of the
> value. You can display an Integer in a Format that shows decimal places, but
> as you've found out, those will be zero.
>
> If you want the decimal fractions to be preserved, you'll have to use a
> numeric format that preserves them: Single, Double (both floating point),
> Decimal, or Currency.
>
> Larry Linson
> Microsoft Access MVP

Mr Linson
Thank you for your help. You solved my formatting issue.
Jon Rasmussen

On Mar 27, 10:16 am, "Brendan Reynolds"
<brenr...@discussions.microsoft.com> wrote:
> For database design, the article at the following URL is a good starting
> point, and has links to some further resources ...
>
> http://office.microsoft.com/en-gb/ac...242471033.aspx
>
> Segregation of data into time periods or other categories is an issue of
> presentation rather than of storage, and better handled using queries and/or
> report group sections.
>
> Oh .. and my name is Brendan ... 'Mr Reynolds' is my Dad! :-)
>
> --
> Brendan Reynolds
> Access MVP
>
> "Jon R" <jwrasmus...@earthlink.net> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Mar 27, 12:18 am, "Larry Linson" <boun...@localhost.not> wrote:
> >> "Jon R" <jwrasmus...@earthlink.net> wrote
>
> >> > Using an Iif statement in a Select query, I am
> >> > allocating unit costs - dollars and cents to two
> >> > decimal places - to Year fields - 2001, 2002,
> >> > etc. When the query results are ultimately
> >> > Appended to a table, the cost is rounded to
> >> > whole dollars, e.g. 5.75 ends up as 6.00. The
> >> > table's data type for this allocated unit cost is
> >> > Field Size - Long Interger, Format - Standard,
> >> > Decimal Places - 4. What am I doing incorrectly?
> >> > How can I preserve the decimal formatting in
> >> > the Append query? Thank you for any advice.
>
> >> An Integer or Long Integer, by definition, has no decimal places.
>
> >> Format only controls the display, not the definition or storage of the
> >> value. You can display an Integer in a Format that shows decimal places,
> >> but
> >> as you've found out, those will be zero.
>
> >> If you want the decimal fractions to be preserved, you'll have to use a
> >> numeric format that preserves them: Single, Double (both floating point),
> >> Decimal, or Currency.
>
> >> Larry Linson
> >> Microsoft Access MVP
>
> > Mr. Reynolds
> > Will you please elaborate on your comment "serious problem with your
> > database design?" Can you suggest a "best" way to segregate data in
> > time periods - including being able to calculate totals for the data
> > in the time periods. Thank you in advance for the help.

Brendan
Thank you for the reference. I should have stated more clearly that I
am using a query of a table to allocate daily transactions by INV_DATE
to annual periods. Is this approach in keeping with your "using
queries" suggestion?
Jon

"Jon R" <(E-Mail Removed)> wrote

> Thank you for your help. You solved my
> formatting issue.

You are welcome. I'm glad I could assist you in getting past that stumbling
block.

But, like Brendan, when you mention "Mr. Linson", I look around to see if my
late father has been temporarily resurrected to vote in some election or
other. <SMILE Whether you believe in miracles or not, that does happen
sometimes.>

Larry Linson
Microsoft Access MVP

I don't know enough about your specific scenario to advise you, Jon.
Repeating columns in a query certainly sounds less alarming than repeating
columns in a table, but then you mentioned that this is an append query, so
perhaps those repeating columns do end up in a table. But then again, it
sounds as though this may be a data-warehousing type of operation, and
de-normalized tables are sometimes used in that type of scenario for
performance reasons.

--
Brendan Reynolds
Access MVP

"Jon R" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 27, 10:16 am, "Brendan Reynolds"
> <brenr...@discussions.microsoft.com> wrote:
>> For database design, the article at the following URL is a good starting
>> point, and has links to some further resources ...
>>
>> http://office.microsoft.com/en-gb/ac...242471033.aspx
>>
>> Segregation of data into time periods or other categories is an issue of
>> presentation rather than of storage, and better handled using queries
>> and/or
>> report group sections.
>>
>> Oh .. and my name is Brendan ... 'Mr Reynolds' is my Dad! :-)
>>
>> --
>> Brendan Reynolds
>> Access MVP
>>
>> "Jon R" <jwrasmus...@earthlink.net> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > On Mar 27, 12:18 am, "Larry Linson" <boun...@localhost.not> wrote:
>> >> "Jon R" <jwrasmus...@earthlink.net> wrote
>>
>> >> > Using an Iif statement in a Select query, I am
>> >> > allocating unit costs - dollars and cents to two
>> >> > decimal places - to Year fields - 2001, 2002,
>> >> > etc. When the query results are ultimately
>> >> > Appended to a table, the cost is rounded to
>> >> > whole dollars, e.g. 5.75 ends up as 6.00. The
>> >> > table's data type for this allocated unit cost is
>> >> > Field Size - Long Interger, Format - Standard,
>> >> > Decimal Places - 4. What am I doing incorrectly?
>> >> > How can I preserve the decimal formatting in
>> >> > the Append query? Thank you for any advice.
>>
>> >> An Integer or Long Integer, by definition, has no decimal places.
>>
>> >> Format only controls the display, not the definition or storage of the
>> >> value. You can display an Integer in a Format that shows decimal
>> >> places,
>> >> but
>> >> as you've found out, those will be zero.
>>
>> >> If you want the decimal fractions to be preserved, you'll have to use
>> >> a
>> >> numeric format that preserves them: Single, Double (both floating
>> >> point),
>> >> Decimal, or Currency.
>>
>> >> Larry Linson
>> >> Microsoft Access MVP
>>
>> > Mr. Reynolds
>> > Will you please elaborate on your comment "serious problem with your
>> > database design?" Can you suggest a "best" way to segregate data in
>> > time periods - including being able to calculate totals for the data
>> > in the time periods. Thank you in advance for the help.
>
> Brendan
> Thank you for the reference. I should have stated more clearly that I
> am using a query of a table to allocate daily transactions by INV_DATE
> to annual periods. Is this approach in keeping with your "using
> queries" suggestion?
> Jon
>

On Mar 29, 2:15 am, "Brendan Reynolds"
<brenr...@discussions.microsoft.com> wrote:
> I don't know enough about your specific scenario to advise you, Jon.
> Repeating columns in a query certainly sounds less alarming than repeating
> columns in a table, but then you mentioned that this is an append query, so
> perhaps those repeating columns do end up in a table. But then again, it
> sounds as though this may be a data-warehousing type of operation, and
> de-normalized tables are sometimes used in that type of scenario for
> performance reasons.
>
> --
> Brendan Reynolds
> Access MVP
>
> "Jon R" <jwrasmus...@earthlink.net> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Mar 27, 10:16 am, "Brendan Reynolds"
> > <brenr...@discussions.microsoft.com> wrote:
> >> For database design, the article at the following URL is a good starting
> >> point, and has links to some further resources ...
>
> >>http://office.microsoft.com/en-gb/ac...242471033.aspx
>
> >> Segregation of data into time periods or other categories is an issue of
> >> presentation rather than of storage, and better handled using queries
> >> and/or
> >> report group sections.
>
> >> Oh .. and my name is Brendan ... 'Mr Reynolds' is my Dad! :-)
>
> >> --
> >> Brendan Reynolds
> >> Access MVP
>
> >> "Jon R" <jwrasmus...@earthlink.net> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > On Mar 27, 12:18 am, "Larry Linson" <boun...@localhost.not> wrote:
> >> >> "Jon R" <jwrasmus...@earthlink.net> wrote
>
> >> >> > Using an Iif statement in a Select query, I am
> >> >> > allocating unit costs - dollars and cents to two
> >> >> > decimal places - to Year fields - 2001, 2002,
> >> >> > etc. When the query results are ultimately
> >> >> > Appended to a table, the cost is rounded to
> >> >> > whole dollars, e.g. 5.75 ends up as 6.00. The
> >> >> > table's data type for this allocated unit cost is
> >> >> > Field Size - Long Interger, Format - Standard,
> >> >> > Decimal Places - 4. What am I doing incorrectly?
> >> >> > How can I preserve the decimal formatting in
> >> >> > the Append query? Thank you for any advice.
>
> >> >> An Integer or Long Integer, by definition, has no decimal places.
>
> >> >> Format only controls the display, not the definition or storage of the
> >> >> value. You can display an Integer in a Format that shows decimal
> >> >> places,
> >> >> but
> >> >> as you've found out, those will be zero.
>
> >> >> If you want the decimal fractions to be preserved, you'll have to use
> >> >> a
> >> >> numeric format that preserves them: Single, Double (both floating
> >> >> point),
> >> >> Decimal, or Currency.
>
> >> >> Larry Linson
> >> >> Microsoft Access MVP
>
> >> > Mr. Reynolds
> >> > Will you please elaborate on your comment "serious problem with your
> >> > database design?" Can you suggest a "best" way to segregate data in
> >> > time periods - including being able to calculate totals for the data
> >> > in the time periods. Thank you in advance for the help.
>
> > Brendan
> > Thank you for the reference. I should have stated more clearly that I
> > am using a query of a table to allocate daily transactions by INV_DATE
> > to annual periods. Is this approach in keeping with your "using
> > queries" suggestion?
> > Jon

Brendan
Thanks for the response. Your response has prompted me to rethink my
approach to the organization of data I have been asked to analyze. I
only have access to the electronic data. To clean up the data, make
part numbers consistent in sales, inventory and purchasing, correct
the formatting, etc. I have used the "Replace" and "Iif" functions
with Delete, Make Table, and Append queries. Group members like you,
Larry Linson and others have helped me with the use of the "Replace"
function, formatting, etc. My level of Access skill is such that it
has gotten too complicated and cumbersome for me given the time
constraints. While Access can probably do it, I am now using Excel to
get the data cleaned up, formatted, etc before I use Access to group,
analyze, etc. I will use this experience to identify areas where I
need to gain more Access knowledge such as data organization and
manipulation and macros to handle repetitive tasks.
Jon