Cant get value from query

  • Thread starter Thread starter Rowland
  • Start date Start date
R

Rowland

Okay, complex, NOT!

I have a query that I run to get the sum of the amount
paid.

I need to take this sum ammount and use it in a form. I
tried executing the query string ring on the form in VBA,
but as I searched the internet on how to do this it
involved ADO and I am not using ADO (that I know of), it
is strickly access.

So I would like to know the syntax for extracting a value
from a query. Something like this

DoCmd.OpenQuery "qry_smAmtPd", acViewNormal, acReadOnly
payments = qry_smAmtPd.SummedValue.Value
DoCmd.Close acquery, "qry_smAmtPd"

I am looking for something that will do that, but the
above doesn't work.

thanks,
Rowland
 
Unless it's the bound source for the form, Access doesn't want to let you pull a value from a query - even if it's only a 1 record, 1 field query - to put into a field. You need to use a DLookUp or DSum function (check the Help files for details). You CAN use a query as a datasource for either of these and, since it's done in the query, you shouldn't need to worry about Criteria in the D-function. So, assuming you want it displayed in a TextBox (txtSummedValue), try this:

Dim curSumdVal As Currency
curSumdVal = DLookUp("[SummedValue]", "qry_smAmtPaid")
[txtSummedValue] = curSumdVal

If your query doesn't do the summing for you, use the DSum function.

Hope this helps!

Howard Brody



----- Rowland wrote: -----

Okay, complex, NOT!

I have a query that I run to get the sum of the amount
paid.

I need to take this sum ammount and use it in a form. I
tried executing the query string ring on the form in VBA,
but as I searched the internet on how to do this it
involved ADO and I am not using ADO (that I know of), it
is strickly access.

So I would like to know the syntax for extracting a value
from a query. Something like this

DoCmd.OpenQuery "qry_smAmtPd", acViewNormal, acReadOnly
payments = qry_smAmtPd.SummedValue.Value
DoCmd.Close acquery, "qry_smAmtPd"

I am looking for something that will do that, but the
above doesn't work.

thanks,
Rowland
 
yes yes, thats it, I don't know why I didn't think of
Dlookup, that command has been my best friend for this
database..lol
I never heard of Dsum though, that one I will have to
keep in mind..

Thanks a million..

Rowland
-----Original Message-----
Unless it's the bound source for the form, Access
Click to expand...
doesn't want to let you pull a value from a query - even
if it's only a 1 record, 1 field query - to put into a
field. You need to use a DLookUp or DSum function (check
the Help files for details). You CAN use a query as a
datasource for either of these and, since it's done in
the query, you shouldn't need to worry about Criteria in
the D-function. So, assuming you want it displayed in a
TextBox (txtSummedValue), try this:
Dim curSumdVal As Currency
curSumdVal = DLookUp("[SummedValue]", "qry_smAmtPaid")
[txtSummedValue] = curSumdVal

If your query doesn't do the summing for you, use the DSum function.

Hope this helps!

Howard Brody



----- Rowland wrote: -----

Okay, complex, NOT!

I have a query that I run to get the sum of the amount
paid.

I need to take this sum ammount and use it in a form. I
tried executing the query string ring on the form in VBA,
but as I searched the internet on how to do this it
involved ADO and I am not using ADO (that I know of), it
is strickly access.

So I would like to know the syntax for extracting a value
from a query. Something like this

DoCmd.OpenQuery "qry_smAmtPd", acViewNormal, acReadOnly
payments = qry_smAmtPd.SummedValue.Value
DoCmd.Close acquery, "qry_smAmtPd"

I am looking for something that will do that, but the
above doesn't work.

thanks,
Rowland

.
Click to expand...
 
Okay, complex, NOT!

I have a query that I run to get the sum of the amount
paid.

I need to take this sum ammount and use it in a form. I
tried executing the query string ring on the form in VBA,
but as I searched the internet on how to do this it
involved ADO and I am not using ADO (that I know of), it
is strickly access.

So I would like to know the syntax for extracting a value
from a query. Something like this

DoCmd.OpenQuery "qry_smAmtPd", acViewNormal, acReadOnly
payments = qry_smAmtPd.SummedValue.Value
DoCmd.Close acquery, "qry_smAmtPd"

I am looking for something that will do that, but the
above doesn't work.

thanks,
Rowland
Click to expand...

How about...

Dim db As Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qry_smAmtPd")
With rst
payment = .Fields!SummedValue
.Close
End With
Set rst = Nothing
Set db = Nothing

Or...

Can you simply use the DSum function? If so then you could put it in
the Control Source of the textbox on the form.

- Jim
 
Back
Top