< date - not working

[nuttychick]

Hi, I have some VB on a form and part of its job is to display a message if certain criteria is met, the code itself runs but doesn't appear to be calculating correctly....I have highlighted in red the section that is being missed, because the app is calculating the result as false

If Forms![Study_Main]![study_type_id] = 26 Then 'And txtCurrentUserRole = "Proposal Manager" Then
    If Not IsNull(Forms![Study_Main]![ITSuppPropDate].ItemData(0)) Then
    
        [COLOR="Red"]If Forms![Study_Main]![ITSuppPropDate].ItemData(0) <= Date[/COLOR] Then
            If Forms![Study_Main]![study_status_hist_subform]![status_type] <> "Closed" Then
                If Forms![Study_Main]![study_status_hist_subform]![status_type] <> "Commercial Cover Received" Then
                    MsgBox "The IT Supplier Proposal due date on this study has been reached. If you are the Bid Manager, please can you ensure the status is updated"
                Else
                End If
            End If
        End If
    End If
End If

I have used the debug window to confirm what the value is of each object.
My knowledge of dates tell me that 08/05/06 is indeed less than or equal to 10/05/06, so why does it come back as false?

?date
10/05/06 

?Forms![Study_Main]![ITSuppPropDate].ItemData(0)
08/05/06

?Forms![Study_Main]![ITSuppPropDate].ItemData(0) <= Date
False

The object Forms![Study_Main]![ITSuppPropDate] is a list box showing dates, using itemdata to return the first entry in the list box, the object is formatted as shortdate...

Where am I going wrong..??
Oh and if I make Forms![Study_Main]![ITSuppPropDate].ItemData(0) >= Date it will run the code!? but it doesn't seem to be being consistant..

HELP!! driving me barmy

 

[namliam]

I am gambling a little but...

Try doing format(Forms![Study_Main]![ITSuppPropDate].ItemData(0),"dd-mmm-yyyy")
I think you will find it is not 08-May-2006 but rather 05-Aug-2006...

This is caused by the ITSuppPropDate beeing treated like a string and then implicitly converted into a MM/DD/YYYY format date...

Solution: Use dateserial or something alike to expliticitly convert the string the proper way.

Regards & GL

 

[nuttychick]

oh woe is me

How I wish that had said August!
debug confirms it thinks it is the 08th may
I have changed the date on system to make sure that is picking it up correctly and it is.


?format(Forms![Study_Main]![ITSuppPropDate].ItemData(0), "dd-mmm-yyyy")
08-May-2006

Any ideas?

 

[namliam]

your statement here

but it doesn't seem to be being consistant..

does seem to point to the date conversion problem... ie. any dates on or before the 12th day are reversed (ie bad) any dates over the 12th day are OK...

Try storing the value from the form in a variable first and try to format that variable...
Yes I am grasping at straws...

 

[nuttychick]

date format

Think I may have 'fixed' it or at least got round it.

If format(Forms![Study_Main]![ITSuppPropDate].ItemData(0), "dd-mmm-yyyy") <= format(Date, "dd-mmm-yyyy")

need to test it, but in the debug window it works..so I will give it a go.

 

[namliam]

no no No NO NO NO NO !!!!

01-Aug-2006 < 01-Jan-2006

That is not going to work, if you feel forcing the format will work try using the ISO Format of YYYYMMDD....

This however does prove to me that the issue is indeed with the date formatting... Probably the listbox is beeing converted to text and back to date somehow...

But do change the format... or make it a dateserial thing or something. In any case dont use DD-MMM-YYYY

 

[dheich4321]

try wrapping the variables with #

If Forms![Study_Main]![ITSuppPropDate].ItemData(0) <= #Date#

 

[nuttychick]

I agree!

Ok...thought until I went cross eyed and then did the following.
Declared a variable

Dim ITsuppDate As Date

After requering, assigned value

ITSuppPropDate.Requery
'asign the prop date to date variable
If Not IsNull(ITSuppPropDate.ItemData(0)) Then
ITsuppDate = ITSuppPropDate.ItemData(0)
End If

Then amended my code to use this variable

If Not IsNull(ITsuppDate) Then

If ITsuppDate < Date Then

Ok - it appears to be working now... but I will test it completely tomorrow...one problem down, only another million or so to go!

Thank you for your help! I will post back how it goes!

 

[EMP]

To ensure the correct data type is used in the comparison, you can use:

If CDate(Forms![Study_Main]![ITSuppPropDate].ItemData(0)) <= Date Then

^