dLookUp error? (report->detail->on print event)

[pauljkeenan]

Hi everyone,

Ive just used dLookUp() for the first time today, and thought I had got the hang of it but evidently there is something wrong. Here's whats happening, if you can help at all please let me know

I have a report, which will display record information including name, fee and currency. As well as this, I want the report to display an exchange rate(depending on month of application date) and exchanged fee for each record also.

I have a table, tblRates, which has the following fields
Mon(i.e.month) Rat(rate) Dat(date)
aug 1.76 1/8/06

and also a table for records, which among other info has name, application date, fee, currency, etc

In the report, on print event, I want to compute the following.
for each record, If the currency is YTL, multiply the fee by the rate for that applicable month (from tblRates). So if the apply date was in august, the exchange rate will be for august. These will then be displayed in the report

Here is my code so far

Dim gbl_euro As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim ans As Integer
Dim theRate As Integer
If (Me.Curr = "YTL") Then

theRate = DLookup("[Rat]", "[tblRates]", Month([Dat]) = Month(Me.App_Date))
gbl_euro = (Me.Fee / theRate)
ans = Round(gbl_euro, 0)
Me.txtEuro = ans
Me.txtRate = theRate
End If

#pseduocode
If the currency is ytl
theRate = the rate where month of rate is same as month of application
gbl_euro is fee / rate
round ans to 0 decimal points
put ans in field
put rate in field

But I get an error for dLookUp line, saying
RunTime error 2465
MS cant find the field "|" refered to in your expression

anybody have any ideas about it?
Help is much needed and appreciated.

all the best lads, kolay gelsin

 

[pauljkeenan]

problem solved, as always a syntax error that somehow I missed from the examples

theRate = DLookup("[Rat]", "tblRates", "Month([Dat]) = " & Month(Me.App_Date))

take it easy

paul