Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: QueryDef bringing back different value than what's show in query

    I have a form with a subform on it. The subform is populated via querydef using the following SQL statement:

    strSql = "Select TrackingTbl.* From TrackingTbl WHERE (((TrackingTbl.CtNumber) = '" & strCtNumber & "') " & _
    "AND ((TrackingTbl.CtSegment)= '" & strCtSegment & "'));"

    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

    Thanks in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    rst was declared a variable as recordset in the beginning
    1. Which kind of RecordSet (i.e. from which library: DAO or ADODB)?

    2. What's 'currentDate' and how is it declared?

    3. You cannot compare date values using an expression such as:
    Code:
    If rst("Field1") < currentDate Then
    You must properly format the vate values (using '#' and the 'mm/dd/yyyy' format) and you should use the DateDiff() function.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •