locked
Strange & Weird ACCESS 97 VBA Compiler behavior - Help Please... RRS feed

  • Question

  • Hi all,

    Here is my situation.

    I am working with a legacy Access 97 DB, in Access 97.

    I have a Variant variable set to "14 Jan 2013"

    I have this line of code (which I don't like):

    Dum = WritePrivateProfileString("SPEQ", "DataDate", Format$(GVdataDate, "d mmm yyyy"), LSiniFile)

    naturally (like you do) I tried to change it to this:

    Dum = WritePrivateProfileString("SPEQ", "DataDate", VBA.Format(GVdataDate, "d mmm yyyy"), LSiniFile)

    and it refuses to compile, with a Type Mismatch error at runtime...

    now, obviously, this isn't life or death, nor is there any reason why I can't just leave the old code the way it is, but can anyone explain this compiler behaviour - cos it is a little strange.

    Why is the result of the old Format$ acceptable to WritePrivateProfileString API Function, but Format (or Vba.Format) isn't?

    Surely an Any argument to the 'WritePrivateProfileString' functions is an Any whereever it comes from?

    Whoever can explain this will have dignity

    Philip

    Wednesday, January 16, 2013 12:23 PM

Answers

  • I agree that it shouldn't matter, but note that Format and Format$ are slightly different functions. (see the Access 97 Help file for details: unfortunately, the difference is no longer discussed in Access 2010, which is the only version I have installed on this machine!)

    To be perfectly honest, I don't see the need to disambiguate with VBA. but if you really want to, see whether this works any better:

    Dum = WritePrivateProfileString("SPEQ", "DataDate", VBA.Format$(GVdataDate, "d mmm yyyy"), LSiniFile)
    Any reason why you're using a Variant rather than a Date variable? That harkens back to the Access 2.0 days!


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:02 AM
    Wednesday, January 16, 2013 12:39 PM
  • Hi,

    I'm not exactly sure what the problem is, but a few observations...

    The Type Declaration Character ($) at the end of the Format() function in the first example forces a String return.  Check Help for Format(), and although it's "generally accepted" that it returns a string, it may be possible that it actually returns a Variant with a string subtype (some of the built-in functions do that, thus the inclusion of the typed calls, such as Left$, etc).

    Now, that doesn't necessarily explain a compile error, because if anything, that ought to be a runtime error rather than a compile error.

    Also a note on the "Any" type for VBA API Declarations - this doesn't necessarily mean that the API will accept any type of data, it just means that VBA will not perform type checking before passing the information along to the API.  Except in a few rare cases (and this may be one of them, I don't know), the Any type in a declaration is better left avoided.  Always explicitly type the declaration unless there's specific reason NOT to do so.  APIs are extremely picky on mapping datatypes.

    This doesn't answer any questions really - sorry I don't have better info, but I haven't used '97 enough to really know for sure.  You might have already known this, but if not, possibly it will give a little bit of insight to some of the odd behavior.

    Cheers,


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)


    Wednesday, January 16, 2013 12:44 PM
  • Strange indeed - I can't explain it. This, however, works:

    Dim ValueString As String
    ValueString = VBA.Format(GVdataDate, "d mmm yyyy")
    WritePrivateProfileString "SPEQ", "DataDate", ValueString, LSIniFile

    So despite the declaration lpString as Any, the function apparently requires this argument to be a string...

    Regards, Hans Vogelaar

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:03 AM
    Wednesday, January 16, 2013 12:48 PM
  • Another thing to note is that sometimes when passing information to an API call, you may be better off having the information in a variable instead of using some sort of expression to pass the data into the call.  APIs very often rely on pointers to data, and declaring a variable and passing the variable through instead of the expression ensures that the API has a solid pointer to the data to work with.

    This isn't always required, but I've had odd bugs with API calls fixed by doing so.

    Also, I have to ask why this data is in a Variant form to begin with, and why you are attempting to return a string from Format(), which appears to be the exact same format that the variant(string) is in anyway.  Would something like this not be better:

    Dim MyString As String

    MyString = CStr(YourVariantData)

    then call your API using the MyString variable?


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:03 AM
    Wednesday, January 16, 2013 12:49 PM
  • Format$() returns a String; Format() returns a Variant(String) -- in other words, the returned variant will hold a String value if it isn't Null.  WritePrivateProfileString requires a String argument in that position, not a Variant.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:03 AM
    Wednesday, January 16, 2013 5:26 PM

All replies

  • I agree that it shouldn't matter, but note that Format and Format$ are slightly different functions. (see the Access 97 Help file for details: unfortunately, the difference is no longer discussed in Access 2010, which is the only version I have installed on this machine!)

    To be perfectly honest, I don't see the need to disambiguate with VBA. but if you really want to, see whether this works any better:

    Dum = WritePrivateProfileString("SPEQ", "DataDate", VBA.Format$(GVdataDate, "d mmm yyyy"), LSiniFile)
    Any reason why you're using a Variant rather than a Date variable? That harkens back to the Access 2.0 days!


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:02 AM
    Wednesday, January 16, 2013 12:39 PM
  • Hi,

    I'm not exactly sure what the problem is, but a few observations...

    The Type Declaration Character ($) at the end of the Format() function in the first example forces a String return.  Check Help for Format(), and although it's "generally accepted" that it returns a string, it may be possible that it actually returns a Variant with a string subtype (some of the built-in functions do that, thus the inclusion of the typed calls, such as Left$, etc).

    Now, that doesn't necessarily explain a compile error, because if anything, that ought to be a runtime error rather than a compile error.

    Also a note on the "Any" type for VBA API Declarations - this doesn't necessarily mean that the API will accept any type of data, it just means that VBA will not perform type checking before passing the information along to the API.  Except in a few rare cases (and this may be one of them, I don't know), the Any type in a declaration is better left avoided.  Always explicitly type the declaration unless there's specific reason NOT to do so.  APIs are extremely picky on mapping datatypes.

    This doesn't answer any questions really - sorry I don't have better info, but I haven't used '97 enough to really know for sure.  You might have already known this, but if not, possibly it will give a little bit of insight to some of the odd behavior.

    Cheers,


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)


    Wednesday, January 16, 2013 12:44 PM
  • Strange indeed - I can't explain it. This, however, works:

    Dim ValueString As String
    ValueString = VBA.Format(GVdataDate, "d mmm yyyy")
    WritePrivateProfileString "SPEQ", "DataDate", ValueString, LSIniFile

    So despite the declaration lpString as Any, the function apparently requires this argument to be a string...

    Regards, Hans Vogelaar

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:03 AM
    Wednesday, January 16, 2013 12:48 PM
  • Another thing to note is that sometimes when passing information to an API call, you may be better off having the information in a variable instead of using some sort of expression to pass the data into the call.  APIs very often rely on pointers to data, and declaring a variable and passing the variable through instead of the expression ensures that the API has a solid pointer to the data to work with.

    This isn't always required, but I've had odd bugs with API calls fixed by doing so.

    Also, I have to ask why this data is in a Variant form to begin with, and why you are attempting to return a string from Format(), which appears to be the exact same format that the variant(string) is in anyway.  Would something like this not be better:

    Dim MyString As String

    MyString = CStr(YourVariantData)

    then call your API using the MyString variable?


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:03 AM
    Wednesday, January 16, 2013 12:49 PM
  • Format$() returns a String; Format() returns a Variant(String) -- in other words, the returned variant will hold a String value if it isn't Null.  WritePrivateProfileString requires a String argument in that position, not a Variant.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Dummy yoyo Friday, January 25, 2013 5:03 AM
    Wednesday, January 16, 2013 5:26 PM
  • Hi Philip,

    Welcome to the MSDN forum.

    I temporarily marked the replies as answers and you can unmark them if they provide no help.

    Please feel free to let us know if you have any concern/question.

    Thanks for your understanding and have a nice day.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 25, 2013 5:03 AM