better way

[MarkS]

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]

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" <Ma...@discussions.microsoft.com> wrote in message
news:867135C7-89BD-467E...@microsoft.com...

[Dirk Goldgar]

In news:867135C7-89BD-467E...@microsoft.com,

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)

[MarkS]

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:867135C7-89BD-467E...@microsoft.com,
> MarkS <Ma...@discussions.microsoft.com> 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