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

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" <(E-Mail Removed)> wrote in message
news:867135C7-89BD-467E-99AA-(E-Mail Removed)...
> 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

In news:867135C7-89BD-467E-99AA-(E-Mail Removed),
MarkS <(E-Mail Removed)> 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)

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-99AA-(E-Mail Removed),
> MarkS <(E-Mail Removed)> 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)
>
>
>