Getting data from query in VBA

[bmac]

I am a beginner-intermediate trying to understand how to extract data from a simple query. The query fields are vdate, provider, sumamt. Using VBA during a click event I want the code to get sumamt where vdatedate=form![medical].visitdate and provider=form![medical].provider.

SumAmt would be stuffed in a variable for use later.

Any help would be appreciated.

Bill

 

[ajetrumpet]

I am a beginner-intermediate trying to understand how to extract data from a simple query. The query fields are vdate, provider, sumamt. Using VBA during a click event I want the code to get sumamt where vdatedate=form![medical].visitdate and provider=form![medical].provider.

SumAmt would be stuffed in a variable for use later.

Any help would be appreciated.

Bill

dim a variable, use a dlookup(), and that's it:

dim myvar as variant

  myvar = dlookup("sumamt", "queryName", _
    "[vdate] = " & forms![medical]!visitdate & " AND " & _
      "[provider] = '" & forms![medical]!provider & "'"

 

[bmac]

Thanks for the fast reply! The two criteria - the AND statement - was what was giving me trouble. Thanks again.

Bill

 

[bmac]

Adam,

For some reason I get a Type Mismatch error (13). Why would this be.

The VBA code, slightly modified is:

Dim amtcost As Variant
mydate = Forms![Medical]!VisitDate
xprovider = Forms![Medical]!Provider

amtcost = DLookup("sumamt", "qry_transSumByDate", "[tdate] =" & mydate And "[provider] =" & xprovider)

tdate is a date (obviously) and provider is a lookup table number.

Thanks again for any assistance.

Bill