Variable = QueryField

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is the following possible (where it will correctly execute)? If so, how?

DoCmd.OpenQuery "qryPAYMENTTOTALS"
PaymentTotal = query!qryPAYMENTTOTALS!PaymentAmount

THanks,

Ray.
 
Ray said:
Is the following possible (where it will correctly execute)? If so,
how?

DoCmd.OpenQuery "qryPAYMENTTOTALS"
PaymentTotal = query!qryPAYMENTTOTALS!PaymentAmount

THanks,

Ray.
Click to expand...

No. You need either a RecordSet or the Domain function Dlookup() to
retrieve the value from the query. In both of those cases you would not
need the OpenQuery line at all. Dlookup would be the simplest.

PaymentTotal = DLookup("PaymentAmount", "qryPAYMENTTOTALS")
 
Ray-
You are trying to put a value from your query into a variable, PaymentTotal,
no? From what you've written, I can't tell how you are specifying a
particular record. That is, couldn't there be many PaymentAmount's returned
by your query? Or is the query written in such a way that it will always
only return one record?

You can do the following to lookup a specific field from a specified record
in your query and assign that value to a variable. (It doesn't work if there
are any parameters in your query, however)

PaymentTotal = dlookup("[PaymentAmount]","qryPAYMENTTOTALS","[PaymentYear] =
2005")

The last part is how to tell it to find a certain record. I made up an
example that you have things grouped by year and want the total for 2005.
hope this helps
-John
 
Rick and John

One last (hopefully) question on this subject:

This is what I have in the code:

Dim PaymentTotal as Currency

PaymentTotal = DLookup("PaymentAmount", "qryPAYMENTTOTALS", "[PartyNumber]
=" & Forms!frmPAYMENTentry!PartyNumber)


When ran, I get a type mismatch error. The PaymentAmount field in the table
is set to currency. Please tell me what I am doing incorrectly.

Thanks,

Ray.

John Welch said:
Ray-
You are trying to put a value from your query into a variable, PaymentTotal,
no? From what you've written, I can't tell how you are specifying a
particular record. That is, couldn't there be many PaymentAmount's returned
by your query? Or is the query written in such a way that it will always
only return one record?

You can do the following to lookup a specific field from a specified record
in your query and assign that value to a variable. (It doesn't work if there
are any parameters in your query, however)

PaymentTotal = dlookup("[PaymentAmount]","qryPAYMENTTOTALS","[PaymentYear] =
2005")

The last part is how to tell it to find a certain record. I made up an
example that you have things grouped by year and want the total for 2005.
hope this helps
-John

Ray Todd said:
Is the following possible (where it will correctly execute)? If so, how?

DoCmd.OpenQuery "qryPAYMENTTOTALS"
PaymentTotal = query!qryPAYMENTTOTALS!PaymentAmount

THanks,

Ray.
Click to expand...
Click to expand...
 
This is resolved. When the query is run, it creates a field called
SumofPaymentAmount. I need to reference this field instead of PaymentAmount.

Once I changed this reference, the code runs correctly.

Thanks,

Ray.
 
Back
Top