Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm actually quite surprised that <code>WHERE Format([TimeStamp]...</code> works in an ADO query against the Access Database Engine (ACE), but apparently it does.</p> <p>I certainly agree that specific details on using some Microsoft features can be difficult to find in Microsoft's documentation. I guess that helps keep sites like Stack Overflow in business. ;)</p> <p>As for your <code>.Filter</code> question, using <code>Format()</code> in this context does fail, presumably because <code>Format()</code> is a VBA function and is not (always) available to an expression outside of the Access application itself. However, the following test shows that...</p> <pre><code>rst.Filter = "[TimeStamp] &gt;= #2013/04/11# AND [TimeStamp]&lt;#2013/04/12#" </code></pre> <p>...does work. (When no time is specified for a DateTime value then midnight - <code>00:00:00</code> - is assumed.)</p> <p>Test data:</p> <pre><code>ID TimeStamp 1 2013-04-10 21:22:00 2 2013-04-11 02:34:56 3 2013-04-11 04:45:15 </code></pre> <p>Test code:</p> <pre><code>Sub foo() Dim con As ADODB.Connection, rst As ADODB.Recordset Set con = New ADODB.Connection con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=C:\Users\Gord\Desktop\Database1.accdb;" Set rst = New ADODB.Recordset Debug.Print "Test 1: WHERE Format([TimeStamp]..." rst.Open _ "SELECT * FROM [TimeStampData] " &amp; _ "WHERE Format([TimeStamp], ""yyyy/mm/dd"") = Format(#2013/04/11#, ""yyyy/mm/dd"")", _ con, adOpenKeyset, adLockOptimistic Debug.Print "Records returned: " &amp; rst.RecordCount rst.Close Debug.Print Debug.Print "Test 2: Filter" rst.Open "SELECT * FROM [TimeStampData]", con, adOpenKeyset, adLockOptimistic Debug.Print "Total records: " &amp; rst.RecordCount rst.Filter = "[TimeStamp] &gt;= #2013/04/11# AND [TimeStamp]&lt;#2013/04/12#" Debug.Print "Filtered records: " &amp; rst.RecordCount rst.Close Set rst = Nothing con.Close Set con = Nothing End Sub </code></pre> <p>Test results:</p> <pre><code>Test 1: WHERE Format([TimeStamp]... Records returned: 2 Test 2: Filter Total records: 3 Filtered records: 2 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POInformation about syntax of ADO recordset filter queries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USGord Thompson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POInformation about syntax of ADO recordset filter queries
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COseems like your filter does work, although I saw in the docs (http://technet.microsoft.com/en-us/library/cc966377.aspx) that Jet uses only MM/DD/YYYY format, but yours YYYY/MM/DD is different, but working.
      singulars
      1. PO
      1. USMaksee
    2. CO@Maksee In its earlier days the Jet database engine was quite insistent on dealing with date literals in m/d/y format *only*. Somewhere along the way (possibly with the release of Jet_3 or Jet_4) it started accepting yyyy/mm/dd date literals, but much of the documentation still only refers to the m/d/y format.
      singulars
      1. PO
      1. USGord Thompson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload