Solved

Why is my recordset not finding values on particular dates?

Posted on 2014-11-01
19
171 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
  • 9
  • 8
  • 2
19 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
Comment Utility
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
Comment Utility
First one worked perfectly.
I didn't think to force formatting on the SQL table field as well as the value.

Thanks!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Gustav,

Thank you for the education. I appreciate it.

Regards,

Mike
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now