Type Mismatch

[Alex Motilal]

Hello everybody,
The following funcion shows error number 13 (Type Mismatch):



Public Function GetTaxRate(SdInvDate As Date, TaxType As String) As Single

GetTaxRate = DLookup("[TaxRate%]", "tblTaxRates", "[TaxFrom]<= #" & SdInvDate & "# And Nz([TaxTo],#12/31/9999#)> #" & SdInvDate & "#" And "[TaxNature] = '" & [TaxType] & "'")

End Function

Can anyone point what error I am doing?

With thanks,
Alex

[RuralGuy]

Too many quotes in this sequence:
GetTaxRate = DLookup("[TaxRate%]", "tblTaxRates", "[TaxFrom]<= #" & SdInvDate & "# And Nz([TaxTo],#12/31/9999#)> #" & SdInvDate & "#" And "[TaxNature] = '" & [TaxType] & "'")

[Alex Motilal]

Thank u Rural Guy.
Can u please rephrase with correct quotes.
Ales

[RuralGuy]

GetTaxRate = DLookup("[TaxRate%]", "tblTaxRates", "[TaxFrom]<= #" & SdInvDate & "# And Nz([TaxTo],#12/31/9999#)> #" & SdInvDate & "# And [TaxNature] = '" & [TaxType] & "'")

[Alex Motilal]

Thank u RuralGuy.

Now there is no error message. However, the Query returns #Error. I am trying to figure out the reason.
I'll be very happy if you could solve the problem.

With lots of thanks,
Alex

[RuralGuy]

Just remove all but one criteria and see if the error goes away. Then add them back one at a time until the error comes back.

[Alex Motilal]

Thank u RuralGuy.
Here in India we do have two types of Sales Tax. One is CST (Central Sales Tax) and the other is VAT (Value Added Tax). Some Sale Contracts are against CST and some against VAT.
At present I have two seperate tables. One for CST and the other for VAT and has two functions:

Public Function GetCstRate(InvDate As Date) As Single

GetCstRate = DLookup("CSTrate", "tblCSTrates", "[CSTfrom]<= #" & InvDate & "# And Nz([CSTto],#12/31/9999#)> #" & InvDate & "#")

End Function

Public Function GetVatRate(SdInvDate As Date) As Single

GetVatRate = DLookup("VATrate", "tblVATrates", "[VATfrom]<= #" & SdInvDate & "# And Nz([VATto],#12/31/9999#)> #" & SdInvDate & "#")

End Function

In the Query both the Tax rates (in force on the date of Invoicing) are shown and the calculation is done on the basis of IIf function for which the contract is raised.
What I am trying to do is to create a single table, by introducing a new field called TaxNature. This I am doing to learn.
My present set up works fine.
So many times you have given me wonderful solutions. I am sure you can solve this.
With regards,
Alex

[RuralGuy]

I'm not sure I understand your issue but you should be aware that using special characters in names "%" is not a very good practice. http://support.microsoft.com/?id=826763

[RuralGuy]

Have we moved on to a different issue than we had in post #5?

[Alex Motilal]

Dear RuralGuy,
I removed the % from the field name and it works fine.
Thanks for tireless help.
Alex

[RuralGuy]

Excellent! Glad you hear you got it sorted.