Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Re: Transfer Access Function to SQL Function

Expand Messages
  • John Warner
    You didn t develop this Access application did you? I gather from your answer you don t actually understand what a code module in MS Access actually is other
    Message 1 of 7 , Dec 11, 2007
    View Source
    • 0 Attachment
      You didn't develop this Access application did you? I gather from your
      answer you don't actually understand what a code module in MS Access
      actually is other then it is written in a procedural language called Visual
      Basic for Applications (the macro language for MS Office). There is no
      direct equivalent to VBA in SQL Server. You can use VBA to access data from
      SQL Server. We might if we understood what is happening in this function be
      able to duplicate much of the logic here. Thing is I wonder of a cursor
      looping through a Select from a table would be a performance improvement
      since I imagine the code calling this function expects the value to be in a
      specific form.

      Since this works and your knowledge and understanding of what is happening
      is rather limited, my advice is do not fix what isn't in fact broken. I can
      recommend so good books on VBA if you would like to learn that and also a
      couple of good books on T-SQL.


      John Warner




      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
      > Sent: Tuesday, December 11, 2007 11:23 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Re: Transfer Access Function to
      > SQL Function
      >
      >
      > The info I provided is exactly how it is written in the MS Access
      > Database Module, and is working. The Access program is using
      > pass- through tables from a SQL DB, and the queries are a
      > mixture of pass- through SPs and some local queries. I'd like
      > to make this on the SQL
      > side to inprove response time and reduce the load on the Access DB.
      >
      > Commrate is the table that the function is reading through.
      > rs!rate is the returned value when the search answers "true" between
      > the "low" and "high".
      >
      > I'm not looking for a "basically" response because this obviously
      > doesn't help me. I'm looking for an "exactly" response so I can paste
      > it in the function properties and use it.
      >
      > Thanks again,
      >
      > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
      > wrote:
      > >
      > > You haven't told us anything about the data here. What is commrate?
      > What
      > > is rs!rate? What is markup. From what I can see I'd say no this
      > can't be
      > > done, but then I can't see much.
      > >
      > > John Warner
      > >
      > >
      > > > -----Original Message-----
      > > > From: SQLQueriesNoCode@yahoogroups.com
      > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
      > > > Sent: Monday, December 10, 2007 10:13 PM
      > > > To: SQLQueriesNoCode@yahoogroups.com
      > > > Subject: [SQLQueriesNoCode] Transfer Access Function to SQL
      > Function
      > > >
      > > > I'm trying to change some Access queries and functions over to
      > the SQL
      > > > database hoping to get faster results. Below is the Access
      > function.
      > > > Can anyone assist me in changing this to a SQL function? Then how
      > will
      > > > I deploy this function in the SQL SP?
      > > >
      > > > Function GetMarkup(Markup As Double) As Double
      > > > Dim rs As DAO.Recordset
      > > > Set rs = CurrentDb.OpenRecordset("commrate")
      > > > While Not rs.EOF
      > > > If Markup >= rs!Low And Markup < rs!High Then
      > > > GetMarkup = rs!rate
      > > > End If
      > > > rs.MoveNext
      > > > Wend
      > > > End Function
      > > >
      > > >
      > > > Thank you in advance.
      > >
      >
      >
    • marange263
      Jerome - I m guessing your function is being called from a query or a form It appears to be determining commission rate based on the markup of a product - a
      Message 2 of 7 , Dec 13, 2007
      View Source
      • 0 Attachment
        Jerome -
        I'm guessing your function is being called from a query or a form
        It appears to be determining commission rate based on the markup of a
        product - a value (markup % as double)is passed to the function and a
        predetermined markup rate is returned from the Table commrate.
        here is sql from access that emulates the function

        SELECT commrate.Low, commrate.High, commrate.Rate, IIf([markup]>=
        [Low] And [markup]<[High],[rate],0) AS xMarkup
        FROM commrate, Markup;

        --- In SQLQueriesNoCode@yahoogroups.com, "jerome3141"
        <jerome3141@...> wrote:
        >
        > The info I provided is exactly how it is written in the MS Access
        > Database Module, and is working. The Access program is using pass-
        > through tables from a SQL DB, and the queries are a mixture of pass-
        > through SPs and some local queries. I'd like to make this on the
        SQL
        > side to inprove response time and reduce the load on the Access DB.
        >
        > Commrate is the table that the function is reading through.
        > rs!rate is the returned value when the search answers "true"
        between
        > the "low" and "high".
        >
        > I'm not looking for a "basically" response because this obviously
        > doesn't help me. I'm looking for an "exactly" response so I can
        paste
        > it in the function properties and use it.
        >
        > Thanks again,
        >
        > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
        > wrote:
        > >
        > > You haven't told us anything about the data here. What is
        commrate?
        > What
        > > is rs!rate? What is markup. From what I can see I'd say no this
        > can't be
        > > done, but then I can't see much.
        > >
        > > John Warner
        > >
        > >
        > > > -----Original Message-----
        > > > From: SQLQueriesNoCode@yahoogroups.com
        > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
        jerome3141
        > > > Sent: Monday, December 10, 2007 10:13 PM
        > > > To: SQLQueriesNoCode@yahoogroups.com
        > > > Subject: [SQLQueriesNoCode] Transfer Access Function to SQL
        > Function
        > > >
        > > > I'm trying to change some Access queries and functions over to
        > the SQL
        > > > database hoping to get faster results. Below is the Access
        > function.
        > > > Can anyone assist me in changing this to a SQL function? Then
        how
        > will
        > > > I deploy this function in the SQL SP?
        > > >
        > > > Function GetMarkup(Markup As Double) As Double
        > > > Dim rs As DAO.Recordset
        > > > Set rs = CurrentDb.OpenRecordset("commrate")
        > > > While Not rs.EOF
        > > > If Markup >= rs!Low And Markup < rs!High Then
        > > > GetMarkup = rs!rate
        > > > End If
        > > > rs.MoveNext
        > > > Wend
        > > > End Function
        > > >
        > > >
        > > > Thank you in advance.
        > >
        >
      • Paul Anca
        ... IIF is not a SQL function, that is an VB function, you can t use it in a query like that. You should add all conditions on WHERE clause. Also, do not
        Message 3 of 7 , Dec 13, 2007
        View Source
        • 0 Attachment
          >Jerome -
          >I'm guessing your function is being called from a query or a form
          >It appears to be determining commission rate based on the markup of a
          >product - a value (markup % as double)is passed to the function and a
          >predetermined markup rate is returned from the Table commrate.
          >here is sql from access that emulates the function
          >
          >SELECT commrate.Low, commrate.High, commrate.Rate, IIf([markup]>=
          >[Low] And [markup]<[High],[rate],0) AS xMarkup
          >FROM commrate, Markup;

          IIF is not a SQL function, that is an VB function, you can't use it in a query like that.

          You should add all conditions on WHERE clause.

          Also, do not forgot about join conditions, at above example you use 2 tables but no link conditions between them, you can use either JOIN (Inner join, outer, join, left join whatever is most useful for you) or regular WHERE clause, e.g.:

          SELECT * FROM tbl1, tbl2 WHERE tbl1.ID = tbl2.ID

          this is equivalent to:

          SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID

          Paul Anca

          paul@...
          www.ancasolutions.com
        Your message has been successfully submitted and would be delivered to recipients shortly.