Solved

Why is my recordset not finding values on particular dates?

Posted on 2014-11-01
19
187 Views
Last Modified: 2014-11-03
I'm having some strange behaviour occurring when searching if a record exists in a recordset based on 2 matching criteria: an ID (lngID) number and a Date/Time (dateLastSample).

It works most of the time. But fails to find records that I know are there (and consequently ends up duplicating them) for specific dates: e.g 11-Sep-14, 11-Oct-14, 1-Nov-14. The rs.recordcount = 0 for these dates. Surrounding dates seem to be fine, and I haven't gone back further than 11-Sep

I'm forcing the dates into the required SQL mm/dd/yy format.... is there some other peculiarity with dates that I'm missing?

lngID = 39
dateLastSample = #10/15/2014 01:00#

Dim rs as dao.recordset
Set rs = db.OpenRecordset("SELECT * FROM tblOPLOGS_Data WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & Format(CDate(dateLastSample), "mm/dd/yy hh:mm") & "#")

 'Add Record if required
                    If rs.EOF Then    'If no record then
                        .AddNew
                        !SampleDateTime = datelastsample
                        !TGOLevel2 = lngID
                        .Update
                    Else    'If record exists do nothing
                   end if
rs.close
set rs = nothing
0
Comment
Question by:JDido
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
19 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40417318
Try this:

... WHERE TGOLevel2=" & lngID & " AND Format(SampleDateTime, 'mm/dd/yy hh:mm')='" & Format(CDate(dateLastSample), "mm/dd/yy hh:mm") & "'")

I have enclosed the following with ' because Format() changes it to string:

'" & Format(CDate(dateLastSample), "mm/dd/yy hh:mm") & "'

And also to make sure your [dateLastSample] has matching format and type, Forma() again used for that.

Alternatively, you should be also able to use:

... WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & CDate(dateLastSample) & "#")
0
 

Author Closing Comment

by:JDido
ID: 40417376
First one worked perfectly.
I didn't think to force formatting on the SQL table field as well as the value.

Thanks!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40417943
This makes no sense:

"WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & Format(CDate(dateLastSample), "mm/dd/yy hh:mm") & "#"

if dateLastSample is of data type Date, as CDate serves no purpose.
Also, the format mm/dd/yy will fail in a non-US environment.

Further, the error is more likely that minute is not "m" but "n". Thus:

"WHERE TGOLevel2=" & lngID & " AND SampleDateTime = #" & Format(dateLastSample, "yyyy\/mm\/dd hh\:nn") & "#"

If this doesn't return the expected result, it may be because SampleDateTime contains seconds.
If so, you can either round SampleDateTime to the minute (which will exclude a use of the index on SampleDateTime) or use an interval:

"WHERE TGOLevel2=" & lngID & " AND SampleDateTime >= #" & Format(dateLastSample, "yyyy\/mm\/dd hh\:nn") & "# And SampleDateTime < #" & Format(DateAdd("n", 1, dateLastSample), "yyyy\/mm\/dd hh\:nn") & "#"

/gustav
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418027
Hi Gustav,

Because Formt() is another calculation for each record, I think the best dolution will be:

... WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & CDate(dateLastSample) & "#")

As included in my original post. With this, we don't have to be concern about seconds either.

Mike
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40418047
Using CDate on a date value doesn't change much as CDate just will convert the numeric value of the date value - which is a Double - to a date value identical to the original date value.
Thus you could just as well use:

... WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & dateLastSample & "#")

but that will fail in a non-US environment as dateLastSample  will be casted to a string using the local settings.
This is why Format using an unambiguous format must be used when concatenating string expressions of date/time values into SQL strings.

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418095
Aren't non-US environment and US dates dates the same behind the scene? I think they are with only difference is the way they are presented. So, use of format() is required when we are dealing with non-US environment vs US.

I left CDate() in my response thinking the asker is converting something like '12/12/2013' to date. If that date is date type, I agree we can remove to use just:

... WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & dateLastSample & "#")

I am sure you would want to explain why non-US environment would fail with the above solution?

Regards,

Mike
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40418159
We don't know, but:

      .AddNew
      !SampleDateTime = datelastsample
      !TGOLevel2 = lngID
      .Update

indicates that datelastsample is of data type Date.
If Text, you are right that CDate should be used to convert to Date.

    ...WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & dateLastSample & "#")

would fail for example here where the default local format is dd-mm-yyyy. Thus today's date/time, 2014-11-02 15:45, would be converterted to "02-11-2014 15.45". VBA can cope with the dash and the dot as the date and time separators, but Access SQL can't:

    "WHERE TGOLevel2 = 1000 AND SampleDateTime = #02-11-2014 15.45#"

would filter on 2014-02-11.
However, using format as shown, the expression would result in:

    "WHERE TGOLevel2 = 1000 AND SampleDateTime = #2014/11/02 15:45#"

which will filter correctly.

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418210
Hi Gustav,

Access stores Date/Time fields as floating point numbers, counts of
days and fractions of a day (times) since midnight, December 30, 1899.
The format merely controls how the value is displayed, not what's stored."

and

"Because in Access a Date/Time value is not a string. It's stored
internally as a double floating point count of days from a start
point; it can be formatted in many ways but underneath it's still just
a number.
"

So, when we are saying:  ... WHERE TGOLevel2 = 1000 AND SampleDateTime =" &  #"dateLastSample & "#") we should not be concerned with whether either side of = sign it is presented like 2014-02-11 or like 2014/11/02 15:45.

Mike
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40418231
No one has mentioned display of the date/time value. This is about building a string for SQL which don't know about the ins and outs of date/time numeric values.

> we should not be concerned with whether either side of = sign it is
> presented like 2014-02-11 or like 2014/11/02 15:45.

That's right. As I wrote, Access VBA can handle that.
But it can't handle this as a date of November:

    "WHERE TGOLevel2 = 1000 AND SampleDateTime = #02-11-2014 15.45#"

which is the resulting string here and most other places outside the US from this expression:

    "WHERE TGOLevel2=" & lngID & " AND SampleDateTime=#" & dateLastSample & "#"

It will be read as February 11th.
That's why you have to use Format to force an unambiguous string expression of the date value.
As this will not fail neither in the US nor anywhere else, it's the universal and safe method.

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418251
This is what my first solution does:

... WHERE TGOLevel2=" & lngID & " AND Format(SampleDateTime, 'mm/dd/yy hh:nn')='" & Format(CDate(dateLastSample), "mm/dd/yy hh:nn") & "'")

other than nn change.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40418265
Exactly. VBA cannot concatenate a string and a value of another data type without first casting that other data value to a string. Doing so, VBA uses the local settings which outside the US will result in malformatted strings. Using Format brings you in control.

Have in mind, this is all about string handling. The data values will not come into play before we actually run the SQL string.

Another example is decimals. Here we use comma as the decimal separator. Thus, say:

   "WHERE FieldOfDouble = " & dblValue & ""

will result in:

   "WHERE FieldOfDouble = 2,345"

which will raise a syntax error because of the comma.
You will have to use Str to apply the correct format:

   "WHERE FieldOfDouble =" & Str(dblValue) & ""

which will result in:

   "WHERE FieldOfDouble = 2.345"

because Str always return a string where dot is the decimal separator.

In a pure US environment you can make many shortcuts, but if your application must survive in foreigh areas, you learn the hard way to live with it.

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418307
Gustav,

Thank you for the education. I appreciate it.

Regards,

Mike
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40418316
You are welcome!

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418454
Hi JDido,

I hope you will check out this comment. From what I learned from Gustav, please use:

... WHERE TGOLevel2=" & lngID & " AND Format(SampleDateTime, 'mm/dd/yy')='" & Format(CDate(dateLastSample), "mm/dd/yy") & "'")

And if you already know that SampleDateTime is date type, the use:

... WHERE TGOLevel2=" & lngID & " AND Format(SampleDateTime, 'mm/dd/yy')='" & Format(dateLastSample, "mm/dd/yy") & "'")
0
 

Author Comment

by:JDido
ID: 40418485
Hi Mike and Gustav,

Thanks for the ongoing comments.

I am in Australia so non-US dates are a definite. I may have introduced some confusion with my original code having cdates in there.... I had tried for quite a while to get this working before posting here and was just throwing everything at it in a 'trial and error' fashion, so even though yes it is a date format I was trying to remove any doubt ie get it working then cut parts out to see where the problem starts.

Glad you got me to a better solution.

Cheers,

James
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418712
my personal preference on formt is:

SampDate:Format(SampleDateTime, 'yyyymmdd')

because it is easy to sort. Somehow, I think it even functions faster in Where clause. I have not tested it.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40418876
As proposed in my initial post, this will be fastest:

"WHERE TGOLevel2=" & lngID & " AND SampleDateTime = #" & Format(dateLastSample, "yyyy\/mm\/dd hh\:nn") & "#"

because if Format (or anything else) will be applied on SampleDateTime, the function will be called for each record. Further, if you have an index on the field, it cannot be used.
Of course, if you only have a few records, say a thousand or so, you'll hardly notice.

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40419505
but here the left side of = sign is date but right side is string. this will not work.

Additionally, when we filter, we are not concern with minutes or seconds.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40419540
Well, in the SQL string it is a string but - wrapped in hashtags - Access understands that this expresses a date value:

#2014/11/03 15:44#

This also works in VBA, for example:

Dim datDate As Date
datDate = #14:27#

So it will work.
As for the filtering on hours and minutes, that you can include as you need. The original question filtered on these.

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question