Help with DLookup (Syntax Error)

[cydud3]

I currently have the following code in my form:

Private Sub CurrentFXRate_GotFocus()
    Dim dblMonth
    Dim dblYear
    dblMonth = Month([Date])
    dblYear = Year([Date])
    Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = BaseCurrency _
        AND Month(Date) = dblMonth AND Year(Date) = dblYear") / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = PaymentCurrency _
        AND Month(Date) = dblMonth AND Year(Date) = dblYear")
End Sub

For some reason, it does not work. It's giving me a syntax error message. Basically, I'm trying to get the exchange rate of one currency to another based on USD. Instead of full date, I am only using Month and Year. I divide one rate to usd with another using the correct month/year.

2B||!2B

 

[jrbarnett]

Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & BaseCurrency & " AND Month(Date) = " & dblMonth & " AND Year(Date) = " & dblYear) / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & PaymentCurrency & " AND Month(Date) = " & dblMonth & " AND Year(Date) = " & dblYear)

You may also find there is trouble using Date as a fieldname, because it is a reserved word in Jet SQL.

John

 

[cydud3]

Thanks for your suggestion. I have now modified my code to the following:

Private Sub CurrentFXRate_GotFocus()
    Dim dblMonth
    Dim dblYear
    dblMonth = Month([PayDate])
    dblYear = Year([PayDate])
        Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & BaseCurrency & " AND Month(RateDate) = " & dblMonth & " AND Year(RateDate) = " & dblYear) / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & PaymentCurrency & " AND Month(RateDate) = " & dblMonth & " AND Year(RateDate) = " & dblYear)
End Sub

As you noticed I also changed the names of fields that were just plain "Date". I no longer get the syntax error but I get following instead:

Run-time Error '2001':

You canceled the previous operation.

Any idea what's causing this?

2B||!2B

 

[jrbarnett]

In order to find out what causes this, I'd need to know the steps that happen before you activate the CurrentFXRate textbox.

John

 

[cydud3]

I have a "locked" checkbox on the form and when the form loads, it runs a code that checks that checkbox and sets .Locked attributes of the controls based on that checkbox. Besides that, nothing really happens before I activate the CurrentFXRate textbox. Debugger always points the error at

Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & BaseCurrency & " AND Month(RateDate) = " & dblMonth & " AND Year(RateDate) = " & dblYear) / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & PaymentCurrency & " AND Month(RateDate) = " & dblMonth & " AND Year(RateDate) = " & dblYear)

I guess there's still something wrong with it. I tried to remove the date condition. The following code works

Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = BaseCurrency") / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = PaymentCurrency")

but this one does not

Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & BaseCurrency) / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & PaymentCurrency)

Obviously, without the date condition, it is not correct. Any more ideas? Thanks.

2B||!2B

 

[PHV]

So, the question is:
what are BaseCurrency and PaymentCurrency ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cydud3]

BaseCurrency and PaymentCurrency only contains a 3 character string which determines what currency it is (eg. USD, GBP, EUR) The textbox control and the table field for BaseCurrency and PaymentCurrency are named the same.

BaseCurrency control is display-only (taken from a different table) while PaymentCurrency can be changed and bound to the table.

There may be some confusion whether to use the field or textbox control. Can this cause the problem?

2B||!2B

 

[jrbarnett]

Ah, if they are text fields, you need to put quotes around them. Thus:

Me.CurrentFXRate.Value = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = '" & BaseCurrency & "'") / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = '" & PaymentCurrency & "'")

John

 

[cydud3]

This is my code now:

Private Sub txtCurrentFXRate_GotFocus()
    Dim dblMonth As Integer
    Dim dblYear As Integer
    
    dblMonth = Month(Me.txtPayDate)
    dblYear = Year(Me.txtPayDate)
    Me.CurrentFXRate = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = '" & BaseCurrency & "'" And Month(RateDate) = " & dblMonth & " And Year(RateDate) = " & dblYear") / _
        DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = '" & PaymentCurrency & "'" And Month(RateDate) = " & dblMonth & " And Year(RateDate) = " & dblYear")
End Sub

The error this time is Run-time error '13': Type mismatch. If I hover my cursor over RateDate it comes back as 'Empty'. Other variables have correct values. The only thing I can see where type may be mismatched is when I compare dblMonth and dblYear (which are Integers) to Month(RateDate) and Year(RateDate) which are originally date fields. Any way I can fix this?

2B||!2B

 

[PHV]

And this ?
Me.CurrentFXRate = DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & Me!BaseCurrency & " And Month(RateDate) = " & dblMonth & " And Year(RateDate) = " & dblYear") / _
DLookup("[RateToUSD]", "tblCurrencyRates", "CurrencyRateId = " & Me!PaymentCurrency & " And Month(RateDate) = " & dblMonth & " And Year(RateDate) = " & dblYear)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cydud3]

The problem is not with BaseCurrency and PaymentCurrency because they get correct values during debug. The problem is with RateDate because as I mentioned before, it does not get the correct values and comes back as "Empty".

2B||!2B

 

[cydud3]

Thanks for your help guys. I found out what the problem was. For some reason Month(RateDate) and Year(RateDate) is also treated by Access as Strings. So, after messing around with it, I just had to encapsulate them with single quotes "'" to make them work.

2B||!2B