better way

Discussion in 'Microsoft Access VBA Modules' started by Guest, Nov 15, 2007.

  1. Guest

    Guest Guest

    Hi,
    I use this code to get the hours in a contracts

    Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours from
    IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
    rst1.MoveFirst
    lHours = rst1(0)

    there can only be one answer. Is there a way to load the hours into 'lHours'
    with out using a recordset

    Thanks
     
    Guest, Nov 15, 2007
    #1
    1. Advertisements

  2. Guest

    Allen Browne Guest

    Perhaps:

    Me.lHours = DCount("PeriodNo", "IERSPROD_TOFFERSLOT", _
    "OfferCD =""" & sOfferCD & """") / 2

    I doubt this will execute any more quickly than your recordset does.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "MarkS" <> wrote in message
    news:...
    > Hi,
    > I use this code to get the hours in a contracts
    >
    > Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours from
    > IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
    > rst1.MoveFirst
    > lHours = rst1(0)
    >
    > there can only be one answer. Is there a way to load the hours into
    > 'lHours'
    > with out using a recordset
    >
    > Thanks
     
    Allen Browne, Nov 15, 2007
    #2
    1. Advertisements

  3. Guest

    Dirk Goldgar Guest

    In news:,
    MarkS <> wrote:
    > Hi,
    > I use this code to get the hours in a contracts
    >
    > Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours
    > from IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
    > rst1.MoveFirst
    > lHours = rst1(0)
    >
    > there can only be one answer. Is there a way to load the hours into
    > 'lHours' with out using a recordset


    Even if you call DLookup or DCount, there'll still be a recordset at
    work; it'll just be inside the function instead of outside it. I think
    you'll do better with your own recordset.

    Note, though, that this line:

    > rst1.MoveFirst


    .... is unnecessary. When you first open a recordset, you're at the
    first record already, unless the recordset is empty.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Nov 15, 2007
    #3
  4. Guest

    Guest Guest

    Hi,
    This answers my question. I need to the same basic thing in several places
    and I want to reduce the code to a standed one line piece of code. This will
    work fine

    Thanks

    "Dirk Goldgar" wrote:

    > In news:,
    > MarkS <> wrote:
    > > Hi,
    > > I use this code to get the hours in a contracts
    > >
    > > Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours
    > > from IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
    > > rst1.MoveFirst
    > > lHours = rst1(0)
    > >
    > > there can only be one answer. Is there a way to load the hours into
    > > 'lHours' with out using a recordset

    >
    > Even if you call DLookup or DCount, there'll still be a recordset at
    > work; it'll just be inside the function instead of outside it. I think
    > you'll do better with your own recordset.
    >
    > Note, though, that this line:
    >
    > > rst1.MoveFirst

    >
    > .... is unnecessary. When you first open a recordset, you're at the
    > first record already, unless the recordset is empty.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
    Guest, Nov 15, 2007
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. t a

    better way to create job cost reports

    t a, Oct 29, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    202
  2. Guest

    Sql or a better way

    Guest, Jul 11, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    169
    David C. Holley
    Jul 12, 2005
  3. Guest

    Security-Is there a better way?

    Guest, Aug 16, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    12
    Views:
    208
    John Nurick
    Sep 2, 2006
  4. Mike

    A better way?

    Mike, Jun 10, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    152
  5. Jack

    Any better way to do the query

    Jack, Sep 17, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    203
    John W. Vinson
    Sep 18, 2009
Loading...

Share This Page