Getting a percentage

[MarkNie]

Hi All

I am stuck and any help will be great.

I have an update query which works fine when I want to work out the percentage without changing the format of the field that it updates to percentage. But when I change the field in my table to format percentage it messes everything up.

I then thought it will use the same logic as Excel would. So I tried to divide the 1 number from the other and that would give me the percentage in the percentage column in Excel.

This is the sql I am using but for some reason I just get 0.00% all the time.

SQL:
UPDATE ytblRunDeadlineSummary SET ytblRunDeadlineSummary.[Run Deadlines Met Percentage] = [ytblRunDeadlineSummary]![Run Cumulative Target]/[ytblRunDeadlineSummary]![Run Deadlines missed];


Any help would be great ;p

Thanks
Mark

 

[dhookom]

What is the data type of [Run Deadlines Met Percentage]? If it is Numeric Long Integer or Integer, you won't be able to store decimal values. You would need to change the field type to Single, Double, Decimal, or Currency.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MarkNie]

Hi

The data type = Number
Field size = Decimal
Format = Percentage

Hope this helps

 

[dhookom]

Decimal data has Precision and Scale properties. Is your Scale property >0?

What do you see with this SQL:

SELECT [Run Cumulative Target]/[Run Deadlines missed] as ThePct
FROM ytblRunDeadlineSummary;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MarkNie]

Hi

Precesion = 18
Scale = 0

I get the following when I run the code you provided:

2.8936

Should be 65%

Cumulative is 310
Missed is 107

Thanks

 

[PHV]

Should be 65%
So, you want this ?
([Run Cumulative Target]-[Run Deadlines missed])/[Run Deadlines missed]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Nope that isn't working either.

I basically have to get this figure for it to work with the percentage format:

0.65

but for some readon even if I try to insert that I get 0.00%

 

[dhookom]

My question "Is your Scale property >0?" was a hint. Try set the value to 4 and see what happens.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Sorry for the typo:
([Run Cumulative Target]-[Run Deadlines missed])/[Run Cumulative Target]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886