Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: How can get data without using a recordset

    How can get data without using a recordset

    I am using Access 200. and I have the following:

    ' Now select and insert from QryCrossTabWorks and insert into
    ' new table.
    ' That table must have been emptied by now
    Set rst = New ADODB.Recordset
    SomeColumn = “[I am Clin5a]” '(programmatically got)
    SomeBillPeriod = “1/31/2002” ''(programmatically got)

    strCriteria = "SELECT “ & SomeColumn & " FROM QryCrossTabWorks where " & BillPeriod = “ & SomeBillPeriod

    ' select based on a criteria and not a table
    rst.Open strCriteria, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Is there a way I can get that value “55000” without using
    “rst![I am Clin5a]”
    In other words, I do not know “[I am Clin5a]” ahead of time and I do not know how to programmatically write:

    MyValue = rst![I am Clin5a]


    Quer/Table: QryCrossTabWorks

    BillPeriod I am clin5a I am clin5b
    1/31/2002 55000 40000
    2/28/2002 60000 10000
    3/31/2002 60000 20000
    4/30/2002 30000 30000
    5/31/2002 45000 51000
    6/30/2002 20000 45000
    7/28/2002 10000 50000

    And this table is a result of the following query:

    strSQL = "TRANSFORM Sum(ContractInvoices.InvoiceValue) AS SumOfValue " & _
    " SELECT ContractInvoices.Billperiod " & _
    "FROM ContractInvoices " & _
    "WHERE (((ContractInvoices.ContractNumber)='" & theContract & "'" & _
    "GROUP BY ContractInvoices.Billperiod " & _
    "PIVOT ContractInvoices.CLIN;"

    'DoCmd.RunSQL strSQL
    'DoCmd.SetWarnings True

    Set rst = New ADODB.Recordset
    ' select based on a criteria and not a table
    rst.Open QryCrossTabWorks, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    rst.Source = strSQL
    rst.Requery

    Set rst = Nothing

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Replace:
    MyValue = rst![I am Clin5a]

    with
    MyValue = rst(SomeColumn)

    First syntax is Access-specific and you can't pass a field name from a variable.
    Second syntax is general VB and looks like:

    rst("FieldName")

    If you get FieldName programatically through a variable (in your case SomeColumn), you can lose the quotes and pass the variable name instead of the exact field name...

    Good luck,

    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •