Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Since you changed your questions, I'll add another answer.</p> <p><strong>1) It is querying - if the value is NULL, how may I tell it to print out "DATA NOT AVAILABLE"</strong></p> <p>Which value? I suspect that you mean when the query returns no records. To check this, test for rsObject.RecordCount = 0:</p> <pre><code>Dim ws As Worksheet Set ws = Worksheets("ZaiNet Data") ws.UsedRange.Clear '' remove results of previous query if any If rsObject.RecordCount = 0 Then ws.Cells(1, 1) = "DATA NOT AVAILABLE" Else ws.Cells(1, 1).CopyFromRecordset rsObject End If </code></pre> <p>You can also test for one or both of rsObject.BOF or rsObject.EOF being true ("Beginning Of File" or "End Of File" respectively).</p> <p>When developing things in VBA, especially when using new features that I'm unfamiliar with, I do lots of tests that output things to the Immediate Window. To help with this, I use the following little routine:</p> <pre><code>Sub Say(s as String) Debug.Print s End Sub </code></pre> <p>It makes it a little easier to generate testing output that typing "Debug.Print" all the time (even slightly easier than typing "Debug.P" + Enter using Intellisense).</p> <p>So when you open your recordset, show the record count after it:</p> <pre><code>rsObject.Open strQry, cnnObject, adOpenStatic Say rsObject.RecordCount &amp; " records" </code></pre> <p>Do something like this any time you want to verify a value.</p> <p>Later on, if you want to capture your debugging statements in a text file, you just need to change the operation of the Say() routine.</p> <p><strong>2) How can I clear up the old output from pipeline A, when I am querying pipeline B?</strong></p> <p>As shown in context above:</p> <pre><code>ws.UsedRange.Clear '' remove results of previous query if any </code></pre> <p><strong>3) My dates are saved as strings in Oracle - how can I convert that to date?</strong></p> <p>You don't technically need to convert the resulting date strings to date values, you may find that just by putting them in a cell, Excel will treat them as date values.</p> <p>You just need to make sure that the user's dates get converted to the format that the database is expecting. </p> <p>Your query string as it stands above still shows two lines incorporating the user's dates. The one that uses Format() to convert them to "dd-MMM-yyyy" format is the one you want to keep. Delete the other line, making sure your string concatenating syntax is still correct.</p> <p>To actually convert the date string to a date value though, you would use the CDate() function:</p> <pre><code>Sub DateTest() Dim sDate As String Dim dDate As Date sDate = "09-Jul-2009" dDate = CDate(sDate) Say "sDate = " &amp; sDate Say "dDate = " &amp; dDate dDate = dDate + 1 Say "dDate = " &amp; dDate End Sub Immediate Window output: sDate = 09-Jul-2009 dDate = 7/9/2009 dDate = 7/10/2009 </code></pre> <p>We can verify that it converted the string to a date value because it shows up in the default date format for my machine, and responds to date math (adding 1 day).</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to run query, automate using VBA Macro and Excel, make "loading" feature while reconciling?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTodd
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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