469,607 Members | 2,048 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,607 developers. It's quick & easy.

Formating negative numbers

20
Need someones help for what I believe is a simple process, but something I am doing is wrong. I am working in Access 2003 program in a simple Jet query. What I am trying to do is subtract two dates and have the negative number have parenthese around it as well as turn the color red. According to what I have tried to figure out on my own the sting should go something like this; Format([PromissDate]-[CompletionDate],(0) [Red]). But it doesn't seem to work. What am I missing here?

Thanks for your help
Aug 15 '07 #1
6 5287
Scott Price
1,384 Expert 1GB
Need someones help for what I believe is a simple process, but something I am doing is wrong. I am working in Access 2003 program in a simple Jet query. What I am trying to do is subtract two dates and have the negative number have parenthese around it as well as turn the color red. According to what I have tried to figure out on my own the sting should go something like this; Format([PromissDate]-[CompletionDate],(0) [Red]). But it doesn't seem to work. What am I missing here?

Thanks for your help
Not sure about doing this in a query, but in vba it would be fairly simple.

An overview:
You would use the DateDiff() function to calculate the difference between the two dates, then place an If... Then structure to determine if the promise date is > (greater than) the completion date. Then change the color to red using the .forecolor property of the control, and concatenate the parentheses around the resulting value from the DateDiff() function.

Please ask if you have any questions about this process!

Regards, and welcome to the Scripts!
Scott
Aug 15 '07 #2
Scott Price
1,384 Expert 1GB
Here's a quick and dirty solution I just cooked up :-) Place it in the AfterUpdate event of your txtbox that accepts the Completion date...
Expand|Select|Wrap|Line Numbers
  1. Dim PromiseDate as Date
  2. Dim CompleteDate as Date
  3. Dim Differ as Integer
  4. Dim lngRed as Long
  5.  
  6. lngRed = RGB(255, 0, 0)
  7. PromiseDate = Me.txtPromiseDate
  8. CompleteDate = Me.txtCompleteDate
  9. Differ = DateDiff("d", PromiseDate, CompleteDate)
  10.  
  11. If PromiseDate > CompleteDate Then
  12.     Me.txtDiffer.ForeColor = lngRed
  13.     Me.txtDiffer = "(" & Differ & ")"
  14.     MsgBox "You blew it! Project overdue!!", , "Overdue!"
  15. Else
  16.     Me.txtDiffer = Differ
  17.     MsgBox "Good job! You finished on time and below budget!  Bonuses all around!"       
  18. End If
Regards,
Scott
Aug 15 '07 #3
FishVal
2,653 Expert 2GB
Need someones help for what I believe is a simple process, but something I am doing is wrong. I am working in Access 2003 program in a simple Jet query. What I am trying to do is subtract two dates and have the negative number have parenthese around it as well as turn the color red. According to what I have tried to figure out on my own the sting should go something like this; Format([PromissDate]-[CompletionDate],(0) [Red]). But it doesn't seem to work. What am I missing here?

Thanks for your help
Hi, Steve.

I completely agree with Scott in all points but one - concerning programmatic change of TextBox.Forecolor. This will work on single form view only. In datasheet and continious form view Scott's code will change the field color in all rows/forms. So conditional formatting will be a better solution here.

I suggest the following:

Build a query
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl.*, DateDiff("d",tbl.dteCompletionDate,tbl.dtePromissDate) AS lngDateDiff, Switch(lngDateDiff<0,"(" & lngDateDiff & ")",lngDateDiff>=0,lngDateDiff) AS txtOutput
  2. FROM tbl;
  3.  
Create a form based on the query (BTW the simplest way to do it is to use [AutoForm] button).

Apply conditional formatting on the control corresponding to txtOutput field of the query.
Set Condition1 to
[Field value is] - [less than] - ["0"]
Don't miss double quotes as ControlSource is text type.

Good luck.
Aug 15 '07 #4
Scott Price
1,384 Expert 1GB
FishVal is right on my code changing the color for all records in continuous form view!

I had forgotten that in the situation I pulled this code from, that in my subform continuous form view I used conditional formatting, but in my single form - detail view (accessed via a Detail command button on my subform) I used a very similar version of the code I posted above.

Anyway, go with what works for your situation! And post back here if you have any questions.

Regards,
Scott
Aug 15 '07 #5
Steve67
20
Thanks guys for all your help. The information was very useful. I ended up going a different route and changed the script to an If statement, then changed the conditional formate in the Report section and everything came out to what I needed to accomplish. Once again, thanks for your input.

Steve
Aug 15 '07 #6
Scott Price
1,384 Expert 1GB
Thanks guys for all your help. The information was very useful. I ended up going a different route and changed the script to an If statement, then changed the conditional formate in the Report section and everything came out to what I needed to accomplish. Once again, thanks for your input.

Steve
Glad we could help!

Regards,
Scott
Aug 15 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Subrahmanyam Arya | last post: by
15 posts views Thread by jaks.maths | last post: by
11 posts views Thread by drtimhill | last post: by
39 posts views Thread by Frederick Gotham | last post: by
3 posts views Thread by Steven D'Aprano | last post: by
4 posts views Thread by eriwik | last post: by
20 posts views Thread by Casey | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.