Multiply by Dlookup()

  • Thread starter Thread starter Ripper
  • Start date Start date
R

Ripper

I have a rate that changes from time to time and am trying to multiply a
value [AllStudents]*dlookup("[Rate]","[tblExtraInfo]")

I get an error message. Am I using the wrong syntax or is this impossible?
 
Try assigning the dlookup to a numeric variable first, then use that to
multiply with the [AllStudents] field and assign that to another numeric
var. Also, you don't have any criteria specified for which record to be
returned.

What is the error message?
 
The problem can be in the neighborhood of that expression but that
expression itself is fine. Note that it is usual, though, to have a
criteria, a third argument, for DLookup.

Do you have the whole SQL statement (as it appears in the SQL view) ?


Vanderghast, Access MVP
 
How would I use a third arguemnt? I have always used this syntax.

Here is a simplified version of the SQL expression:

SELECT qryMathTotal.Campus, qryMathTotal.Grade,
Round(([Students]*Dlookup(“[Rate]â€,â€[tblExtraInfo]â€))+0.4) AS StudentRate
FROM qryMathTotal;

Error Message: Syntax Error (Missing Operator) in query expression
'Round(([Students]*Dlookup(“[Rate]â€,â€[tblExtraInfo]â€))+0.4)'
--
Thanks As Always
Rip


Michel Walsh said:
The problem can be in the neighborhood of that expression but that
expression itself is fine. Note that it is usual, though, to have a
criteria, a third argument, for DLookup.

Do you have the whole SQL statement (as it appears in the SQL view) ?


Vanderghast, Access MVP


Ripper said:
I have a rate that changes from time to time and am trying to multiply a
value [AllStudents]*dlookup("[Rate]","[tblExtraInfo]")

I get an error message. Am I using the wrong syntax or is this
impossible?
Click to expand...
Click to expand...
 
How would I use a third arguemnt? I have always used this syntax.
Click to expand...

That syntax will just return the first record Access finds in tblExtraInfo (an
arbitrary record, basicall). If tblExtraInfo contains only one record you
should be OK.
 
Are you sure field Students is numerical (not alpha) as data type?

As John mentioned, if your table tblExtraInfo has one and only one record,
that is ok, but if you have more than only one record, how are you sure that
the first one is the one you need? Generally, we use something like (in
Northwind):

? DLookup( "City", "Customers", "CustomerID='DUMON'")
Nantes


To be sure you get the right record, try:

SELECT Campus,
Grade,
Dlookup("[Rate]","[tblExtraInfo]"),
Students,
Round(([Students]*Dlookup("[Rate]","[tblExtraInfo]"))+0.4) AS
StudentRate
FROM qryMathTotal;





Vanderghast, Access MVP


Ripper said:
How would I use a third arguemnt? I have always used this syntax.

Here is a simplified version of the SQL expression:

SELECT qryMathTotal.Campus, qryMathTotal.Grade,
Round(([Students]*Dlookup("[Rate]","[tblExtraInfo]"))+0.4) AS StudentRate
FROM qryMathTotal;

Error Message: Syntax Error (Missing Operator) in query expression
'Round(([Students]*Dlookup("[Rate]","[tblExtraInfo]"))+0.4)'
--
Thanks As Always
Rip


Michel Walsh said:
The problem can be in the neighborhood of that expression but that
expression itself is fine. Note that it is usual, though, to have a
criteria, a third argument, for DLookup.

Do you have the whole SQL statement (as it appears in the SQL view) ?


Vanderghast, Access MVP


Ripper said:
I have a rate that changes from time to time and am trying to multiply a
value [AllStudents]*dlookup("[Rate]","[tblExtraInfo]")

I get an error message. Am I using the wrong syntax or is this
impossible?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top