Results 1 to 11 of 11
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Question Type Mismatch

    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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Too many quotes in this sequence:
    GetTaxRate = DLookup("[TaxRate%]", "tblTaxRates", "[TaxFrom]<= #" & SdInvDate & "# And Nz([TaxTo],#12/31/9999#)> #" & SdInvDate & "#" And "[TaxNature] = '" & [TaxType] & "'")

  3. #3
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thank u Rural Guy.
    Can u please rephrase with correct quotes.
    Ales
    Last edited by Alex Motilal; 02-12-2011 at 08:25 AM. Reason: Spelling mistake

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    GetTaxRate = DLookup("[TaxRate%]", "tblTaxRates", "[TaxFrom]<= #" & SdInvDate & "# And Nz([TaxTo],#12/31/9999#)> #" & SdInvDate & "# And [TaxNature] = '" & [TaxType] & "'")

  5. #5
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    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

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    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.

  7. #7
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    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

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    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

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have we moved on to a different issue than we had in post #5?

  10. #10
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Dear RuralGuy,
    I removed the % from the field name and it works fine.
    Thanks for tireless help.
    Alex

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Excellent! Glad you hear you got it sorted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. type mismatch in expression
    By lilg1924 in forum Database Design
    Replies: 3
    Last Post: 10-06-2010, 01:37 AM
  2. Type mismatch
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 08-07-2010, 06:54 AM
  3. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 04:15 PM
  4. mismatch type compare help needed
    By techexpressinc in forum Queries
    Replies: 3
    Last Post: 02-23-2010, 03:27 PM
  5. Type Mismatch - HELP!
    By jgelpi in forum Programming
    Replies: 3
    Last Post: 07-17-2009, 03:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums