Aside from the CtNumber and CtSegment, TrackingTbl has 10 other fields, all of which are date/time data types. I create the query using the follwoing:
Set qdf = dbs.CreateQueryDef("Temp", strSql)
I have then opened the query using
DoCmd.OpenQuery "Temp" just to confirm that it did pull out the right record, and that all my fields that are date/time have dates in them. They do and everything looks fine in the query.
I then try to pull back the value from a specific field in Tracking tbl by creating a recordset:
set rst = qdf.Openrecordset(dbOpenSnapshot, dbReadOnly)
(rst was declared a variable as recordset in the beginning)
If rst.RecordCount = 1 Then
If rst("Field1") < currentDate Then
and then I have it change the background color of the control on the subform that is correspondant to that field. I do these if statements for all of the Fields in the table that have a date value.
However, for some reason the value of rst("Field1"), as well as all the other fields, is coming back as 12:00:00, so basically it's reading the value as 0. When I added a watch point and looked into it, it is showing a boolean data type. When I look in the query, I can see that the value show in that field is 12/10/11. Why is this then pulling back a value of 0 if it appears fine in the query itself?
I have spent days trying to figure this out or trying to find a similar problem on google, but have not had any success. Any help would be greatly appreciated as I don't know what else to try