Re: Parameter default value

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've created some hybrid ADO/DAO query in Access.  After you create
your Test.mdb open it & look at the query you've created.  It looks like
an Access DAO QueryDef, but acts like a Stored Procedure - the default
gets set, which I can't find any documentation on why it does that.

The following is the result of your CREATE PROCEDURE statement - a
typical Access query.

PARAMETERS arg_data_col Text ( 255 );
SELECT Test.key_col, Test.data_col
FROM Test
WHERE (((Test.data_col)=IIf([arg_data_col] Is
Null,[data_col],[arg_data_col])));

One that I had posted in one of my earlier posts.

The WHERE clause is equivalent to this:

WHERE data_col = Nz(arg_data_col, data_col)

Which means if the parameter is NULL, then compare the data_col to the
data_col (a True evaluation), which will return all rows.

BUT, your WHERE clause doesn't work like the Nz() function WHERE clause.
 It appears to fill the parameter w/ the default value "N/A"; therefore,
something "underneath" is going on that I don't understand.  Apparently,
by using ADO, you've created some hidden Default value for the parameter
-- in Access 95 format, no less!  Good grief....

Back to your original Q:  How to read the default value?  Since it
doesn't show in the SQL view AND it appears to be undocumented, I
haven't the slightest....
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ0Hf4IechKqOuFEgEQIIdQCdEf+KQfn5Ewnkc6n9085Eq59l6+EAoKEl
GW+BQqsyQEZpwZIB6tw5Ns/q
=MEmI
-----END PGP SIGNATURE-----
peregenem@xxxxxxxxxxxx wrote:

MGFoster wrote:

Note that in Jet a default can't be assigned to the parameters in the
parameters declaration line.



Where does it say this in the help file or documentation? If my link is
wrong (and it seem to be correct to me) then can you please supply the
correct link or a direct quote from the documentation that I can google
for it (I only have Access95 installed at the moment, which I need, and
if I install Access2003 it will automatically uninstall Access95, so
I'm relying on online resources at the moment).

The truth is, in Jet a default *can* be assigned in the parameters
declaration. My example code demonstrates this. Please try it. I'll
post it again here, slightly modified:

Sub Test_Access_Jet3()
On Error Resume Next
Kill "C:\Test.mdb"
On Error GoTo 0

Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
  .Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Jet OLEDB:Engine Type=4;" & _
      "Data Source=C:\Test.mdb"
  With .ActiveConnection
    .Execute _
        "CREATE TABLE Test (" & _
        " key_col INTEGER NOT NULL," & _
        " data_col VARCHAR(10));"
    .Execute _
        "INSERT INTO Test VALUES" & _
        " (1, 'Plenty');"
    .Execute _
        "INSERT INTO Test VALUES" & _
        " (2, NULL);"
    .Execute _
        "INSERT INTO Test VALUES" & _
        " (3, 'N/A');"
    .Execute _
        "CREATE PROCEDURE TestProc " & _
        "(arg_data_col VARCHAR(10) = 'N/A') AS " & _
        " SELECT Key_col, data_col" & _
        " FROM Test" & _
        " WHERE data_col = " & _
        " IIF(arg_data_col IS NULL," & _
        " data_col, arg_data_col);"

    Dim rs As Object
    Set rs = .Execute("EXECUTE TestProc")
    MsgBox rs.Source & vbCr & vbCr & rs.GetString
  End With

End With
End Sub

Note that in the parameters declaration I have assigned the default as
'N/A' i.e.

arg_data_col VARCHAR(10) = 'N/A'

I've then executed the proc and omitted an explicit parameter value
from the call:

EXECUTE TestProc

One row is returned, being the row where data_col = 'N/A'.

My conclusion is that, in absence of an explicit value, Jet has
recognized, honored and applied the default value. How else would you
explain the fact that just one row is returned and it is the row where
data_col = 'N/A'?


As I've been saying in previous posts you are mixing the 2
together.  Learn the proper syntax for each version.



And as I keep replying, this has nothing to do with SQL Server and I am
not mixing my syntax. If you could test my code and post back your
conclusions I would be extremely grateful. Many thanks for your help.

.

Relevant Pages

  • Re: Parsing memo field into texy fields
    ... in query definition;s SELECT list. ... Set cat = CreateObject ... " 'Event Time') " ... Set .ActiveConnection = Nothing ...
    (microsoft.public.access.queries)
  • Re: Boolean Query Algorithm
    ... my question involves a query were you would like to retrieve the all ... consecutive for example "Bat Cat" ... then in the query stage to hash both words (hash ...
    (comp.databases.theory)
  • best way to store the results of a query?
    ... I have recently migrated from Perl, ... easily handle the results of the query. ... I have tried HashMap, but cant ... cat manc Gus ...
    (comp.lang.java.databases)
  • Re: Parameter for query
    ... >> Sorry but the Title did say Parameter for Query. ... > syntax (PARAMETERS Declaration), it just a different way of initially ... To modify the design of some objects in the database, ... database to Front-End and Back-End. ...
    (microsoft.public.access.queries)
  • Re: Query using date parameters
    ... see response in your other thread ... You can Not use Parameters declaration in any custom query ... |> but now receive this error message: ...
    (microsoft.public.frontpage.programming)