Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: IMARGUMENT(complex()

    How can i use the excel function of IMARGUMENT(complex()) in a SQL query in access?

    Thank you very much!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Short of replicating that function I doubt you can. You can use access vba functions and user written functions in JET SQL. but not excel functions
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL doesn't support complex numbers as a native SQL data type. How are you storing the complex values? Once you separate the real and imaginary parts of the complex number, the calculation is pretty straightforward.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2013
    Posts
    6
    Quote Originally Posted by healdem View Post
    Short of replicating that function I doubt you can. You can use access vba functions and user written functions in JET SQL. but not excel functions
    well what about writing a vba script and then calling it. I found and used something like that for ATAN2() which let me use ATAN2 in access. It was vba script made in to public function,

    i was hoping for something similar but my vba is not there so that i could write it.

    or what about calling an excel function in access, i have seen a lot of stuff about that but can not find any detail information on how to do that.

  5. #5
    Join Date
    Jan 2013
    Posts
    6
    Quote Originally Posted by Pat Phelan View Post
    SQL doesn't support complex numbers as a native SQL data type. How are you storing the complex values? Once you separate the real and imaginary parts of the complex number, the calculation is pretty straightforward.

    -PatP
    I dont know to be honest. i havent even go that far just yet. here is the full excel function i am trying to replicate.

    IMARGUMENT(complex(North1-North2;East1-East2))*180/PI()+180

    the only thing i have really is the North1, North2 and East1, East2

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as said before
    you cannot use an Excel VBA function in 'standard' Access VBA
    you can use an Excel function in Access if you are working in Excel Spreadsheet as an OLE or similar object. BUT in that event you are using EXCEL functions in an EXCEL object inside an Access VBA function

    This page shows you the logic so 'all' you need do is code this function yourself

    you may also need to replicate the COMPLEX function as well
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You can use Excel worksheet functions in Access VBA if you add a reference to the Excel VBA library. You will also need to declare an Excel.Application object, and use an incredibly drawn-out and non-intuitive syntax to get the required result.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jan 2013
    Posts
    6
    Quote Originally Posted by healdem View Post
    as said before
    you cannot use an Excel VBA function in 'standard' Access VBA
    you can use an Excel function in Access if you are working in Excel Spreadsheet as an OLE or similar object. BUT in that event you are using EXCEL functions in an EXCEL object inside an Access VBA function

    This page shows you the logic so 'all' you need do is code this function yourself

    you may also need to replicate the COMPLEX function as well
    Thank you so much for you help and insight. Please correct me if i am worng, But you are saying i could link the excel worksheet to the access database by make it OLE but then to make any changes or calcuatiuons in the database it would have to be done in excel? Did i get that correct?

  9. #9
    Join Date
    Jan 2013
    Posts
    6
    Quote Originally Posted by weejas View Post
    You can use Excel worksheet functions in Access VBA if you add a reference to the Excel VBA library. You will also need to declare an Excel.Application object, and use an incredibly drawn-out and non-intuitive syntax to get the required result.
    Right that is what i did for ATAN2, i added the a refrence to the excel VBA libray and then was able to find the syntax need for the rest. Do you have any idea on who or where i could find the syntax for what i am wanting to do now?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what actually are you trying to do?

    Access has native trig functions so you shouldn't need to resort to EXCEL trickery to use TRIG

    are you trying to find the bearing or distance?
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2013
    Posts
    6
    Quote Originally Posted by healdem View Post
    what actually are you trying to do?

    Access has native trig functions so you shouldn't need to resort to EXCEL trickery to use TRIG

    are you trying to find the bearing or distance?
    Compass bearing between two points.

    Essentially we have two points, one at X1Y1 and one at X2Y2.


    From that we can get offset to the north:
    Delta Y
    And the offset to the East:
    Delta X
    Then from that we can get offset distance:
    SQR([DeltaY]^2+[DeltaX]^2)
    And the offset angle:
    (ATAN2([DeltaY], [DeltaX]))*(180/3.14159265358979)


    But we cannot figure out how to get the offset bearing, other than using the excel equation.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    write as function that does it for you
    you can get from the EXCEL equations on moveabletype.co.uk

    its a PITA but its doable
    I have several classes that do that, however they are in .NET not VBA
    they may be relatively easily ported to VBA, however they way the classes were designed there are several inner classes designed to handle any GPS format, handle latitudes/longitudes separately

    Ill see if I can exhume them from where they are currently lurking
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •