Solved Form field conditional background color

[trax1337]

I have no idea why this is failing, I simply want to make the background red if the Date is before Today. The results I am getting are all over the place.

 

[MajP]

Is the field by chance a text field storing string dates?

 

[trax1337]

It is a text field, what should it be?
Format is set to Short Date.

 

[MajP]

A date or time should be saved in a date field.
If not try
[Value] <= format(date(),"dd/mm/yyyy")

 

[trax1337]

A date or time should be saved in a date field.
If not try
[Value] <= format(date(),"dd/mm/yyyy")

If it's not too much trouble, could you post a screenshot or some instructions on how to add a Date Field to a form? All I can find is "Text Box".

Also, even with the formula above I'm getting the same results.
Thanks.

 

[MajP]

Is the control bound to a table? If the control is bound the bound field should be a date field not a text field.

 

[Gasman]

USA format of dates?

 

[trax1337]

Is the control bound to a table? If the control is bound the bound field should be a date field not a text field.

Not directly, I'm using data from a Date Field inside the table but not the actual field. The value for the particular Date I'm using inside the form is set by vba code.

 

[MajP]

Can you show the code? Are you setting a string value or date value in code?
I.e
Txtbox = "10/9/2023"
Or
Txtbox = #9/10/2023#

 

[trax1337]

Can you show the code? Are you setting a string value or date value in code?
I.e
Txtbox = "10/9/2023"
Or
Txtbox = #9/10/2023#

Me.IforceDate_Text.Value = CalculateDeleteDate(eanValue)
Debug.Print "Function Result: " & CalculateDeleteDate(eanValue)

Debug Result below:
inside the function deletionDate: 03/11/2023
Function Result: 03/11/2023

Same Debug.Print results for:

Me.IforceDate_Text.Value = Format(CalculateDeleteDate(eanValue), "dd/mm/yyyy")
Debug.Print "Function Result: " & Format(CalculateDeleteDate(eanValue), "dd/mm/yyyy")

ClaulcateDeleteDate function code:

Function CalculateDeleteDate(eanValue As String) As Date
    Dim lastAdjustmentDate As Date
    Dim deleteAfterDays As Integer
    
    deleteAfterDays = DLookup("DeleteAfterDays", "tbl_settings", "ID = 1")
    lastAdjustmentDate = DLookup("DT", "tbl_crt_stock", "EAN = '" & eanValue & "'")
    
    ' Calculate the date when the stock will be deleted
    Dim deletionDate As Date
    deletionDate = DateAdd("d", deleteAfterDays, lastAdjustmentDate)
    
    Debug.Print "inside the function deletionDate: " & deletionDate
    
    CalculateDeleteDate = deletionDate
End Function

 

[MajP]

Not sure why this is happening since you appear to be returning an actual date with your function and not a string formatted as a date. But there it definitely looks like string comparison happening even though you are returning dates.

if today is
06/10/2023

the string
"06/10/2023" is less than the strings
"06/11/2023"
"07/9/2023"

and greater than the string
"03/09/2023"
which is what you show

 

[trax1337]

Not sure why this is happening since you appear to be returning an actual date with your function and not a string formatted as a date. But there it definitely looks like string comparison happening even though you are returning dates.

if today is
06/10/2023

the string
"06/10/2023" is less than the strings
"06/11/2023"
"07/9/2023"

and greater than the string
"03/09/2023"
which is what you show

Dates are a constant issue, for example, if I want to save a date in a table and I want it to have the format dd/mm/yyyy, I have to insert it as mm/dd/yyyy or it will be the other way around. No idea what is going on.

 

[Minty]

Dates are stored as decimal numbers in Access. What you see is that number formatted to look like a date, based on your regional settings.
The whole number represents the day, the decimal portion is the time. You can see this in the immeditate window:

Print now(), cdec(Now()) ,          Date(), cdec(Date())
05/10/2023 09:13:57  45204.3846875  05/10/2023   45204

So you could convert both to numbers for a true comparison? If only days with no time component is involved or is irrelevant you could try

CLng(Value) <= CLng(Date())

Just a thought?

 

[trax1337]

I cannot explain this but what seems to be working as intended is:

 

[MajP]

It appears that by using the "value" of the control in CF it casts that value to a string. I need to test if this happens all the time. The expression does not cast it for some reason. It should not cast anything in this case because iForceDate_Text was set as a real date.
VBA and access can cast values unexpectedly when there is different datatypes on each side of the expression.

 

[Minty]

@MajP Without testing it I suspect you assumption about CF casting the value may well be correct to avoid "Data Type Mismatch" errors, especially with nulls, or numbers comparing to text. It would sort of make sense.