Formating negative numbers

[Steve67]

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

[Scott Price]

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

[Scott Price]

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...

Dim PromiseDate as Date
Dim CompleteDate as Date
Dim Differ as Integer
Dim lngRed as Long
 
lngRed = RGB(255, 0, 0)
PromiseDate = Me.txtPromiseDate
CompleteDate = Me.txtCompleteDate
Differ = DateDiff("d", PromiseDate, CompleteDate)
 
If PromiseDate > CompleteDate Then
    Me.txtDiffer.ForeColor = lngRed
    Me.txtDiffer = "(" & Differ & ")"
    MsgBox "You blew it! Project overdue!!", , "Overdue!"
Else
    Me.txtDiffer = Differ
    MsgBox "Good job! You finished on time and below budget!  Bonuses all around!"       
End If

Regards,
Scott

[FishVal]

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

SELECT tbl.*, DateDiff("d",tbl.dteCompletionDate,tbl.dtePromissDate) AS lngDateDiff, Switch(lngDateDiff<0,"(" & lngDateDiff & ")",lngDateDiff>=0,lngDateDiff) AS txtOutput
FROM tbl;
 

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.

[Scott Price]

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

[Steve67]

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

[Scott Price]

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