|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Print between two datesI am using the following code in a DLL. Public Function CountTotalYearPayments(Optional StMnt As String, Optional EndMnt As String, Optional Yr As String, Optional CompID As String, Optional VendorID As String) As Object 'Find Total Payments Made. Dim strSQL As String On Error GoTo SelectErr strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo Between '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & Yr & "' AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & "'" Set CountTotalYearPayments = cnBills.Execute(strSQL) Exit Function SelectErr: MsgBox Err.Number & " - " & Err.Description CountTotalYearPayments = Err Err.Clear End Function This works as I need. Question. When I try to find the above, but the year is 2 different years, it does not work. ex: strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo Between '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & StYr & "' AND '" & EndYr & "' AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & "'" How can I retreive the data from two different years -- Thanks in advance bob rober***@mountaincable.net "Robert" <rober***@mountaincable.net> wrote in message <cut>news:%235F8XlrLIHA.5400@TK2MSFTNGP04.phx.gbl... > strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo Between try something like> '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & StYr & "' AND '" & > EndYr & "' AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & > "'" ....where ((yr=" & styr & " and monthno>=" & stmnt & _ ") or (yr=" & endyr & " and monthno<=" & endmnt & ")) and compid='" & .... Note that I'm assuming the Yr and MonthNo columns are numeric so I removed the ' delimiters to eliminate the unecessary conversions. If they are character fields then you'll want to add those back. Bob Butler wrote:
Show quote > "Robert" <rober***@mountaincable.net> wrote in message where> news:%235F8XlrLIHA.5400@TK2MSFTNGP04.phx.gbl... > <cut> >> strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo >> Between '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & StYr & "' >> AND '" & EndYr & "' AND CompID = '" & CompID & "' AND VendorID = '" & >> VendorID & "'" > > try something like > > ...where ((yr = " & styr & " and monthno >=" & stmnt & _ > ") or (yr=" & endyr & " and monthno <=" & endmnt & ")) and compid='" & .... > > Note that I'm assuming the Yr and MonthNo columns are numeric so I > removed the ' delimiters to eliminate the unecessary conversions. If > they are character fields then you'll want to add those back. > > Bob (yr = styr and monthno >= stmnth) OR (yr = endyr and monthno <= endmnt) doesn't quite gel with me. Something to do with years being on the right side as well Thinking about it Graham "argusy" <arg***@slmember.on.net> wrote in message Not sure I understand the issue...news:13khf35r1pbe86d@corp.supernews.com... > where > (yr = styr and monthno >= stmnth) OR > (yr = endyr and monthno <= endmnt) > > doesn't quite gel with me. > Something to do with years being on the right side as well assuming styr=2006, stmnt=6, endyr=2007, endmnt=5 (my first reply had 'stmnth' instead of 'stmnt' because those variables names I find very hard to read) it becomes ((yr=2006 and monthno>=6) or (yr=2007 and monthno<=5)) Bob Butler wrote:
Show quote > "argusy" <arg***@slmember.on.net> wrote in message I was trying to get the "between" dates.> news:13khf35r1pbe86d@corp.supernews.com... >> where >> (yr = styr and monthno >= stmnth) OR >> (yr = endyr and monthno <= endmnt) >> >> doesn't quite gel with me. >> Something to do with years being on the right side as well > > Not sure I understand the issue... > > assuming styr=2006, stmnt=6, endyr=2007, endmnt=5 (my first reply had > 'stmnth' instead of 'stmnt' because those variables names I find very > hard to read) it becomes > > ((yr=2006 and monthno>=6) or (yr=2007 and monthno<=5)) > s'OK. The 'or' threw me, until I thought it through. Works for one year What happens with July 2004 through to Jun 2006 ? I see Jul to Dec 2004, and Jan to Jun, 2006 2005 ? Graham
Show quote
"argusy" <arg***@slmember.on.net> wrote in message That case isn't covered; you could get it by addingnews:13ki522rak92m8b@corp.supernews.com... > Bob Butler wrote: >> "argusy" <arg***@slmember.on.net> wrote in message >> news:13khf35r1pbe86d@corp.supernews.com... >>> where >>> (yr = styr and monthno >= stmnth) OR >>> (yr = endyr and monthno <= endmnt) >>> >>> doesn't quite gel with me. >>> Something to do with years being on the right side as well >> >> Not sure I understand the issue... >> >> assuming styr=2006, stmnt=6, endyr=2007, endmnt=5 (my first reply had >> 'stmnth' instead of 'stmnt' because those variables names I find very >> hard to read) it becomes >> >> ((yr=2006 and monthno>=6) or (yr=2007 and monthno<=5)) >> > s'OK. > I was trying to get the "between" dates. > The 'or' threw me, until I thought it through. > Works for one year > What happens with July 2004 through to Jun 2006 ? > I see Jul to Dec 2004, and Jan to Jun, 2006 > 2005 ? ... or (yr>styr and yr<endyr) Bob Butler wrote:
Show quote > "argusy" <arg***@slmember.on.net> wrote in message Yep. But then I re-read the OP's post.> news:13ki522rak92m8b@corp.supernews.com... >> Bob Butler wrote: >>> "argusy" <arg***@slmember.on.net> wrote in message >>> news:13khf35r1pbe86d@corp.supernews.com... >>>> where >>>> (yr = styr and monthno >= stmnth) OR >>>> (yr = endyr and monthno <= endmnt) >>>> >>>> doesn't quite gel with me. >>>> Something to do with years being on the right side as well >>> >>> Not sure I understand the issue... >>> >>> assuming styr=2006, stmnt=6, endyr=2007, endmnt=5 (my first reply had >>> 'stmnth' instead of 'stmnt' because those variables names I find very >>> hard to read) it becomes >>> >>> ((yr=2006 and monthno>=6) or (yr=2007 and monthno<=5)) >>> >> s'OK. >> I was trying to get the "between" dates. >> The 'or' threw me, until I thought it through. >> Works for one year >> What happens with July 2004 through to Jun 2006 ? >> I see Jul to Dec 2004, and Jan to Jun, 2006 >> 2005 ? > > > That case isn't covered; you could get it by adding > .. or (yr>styr and yr<endyr) > Robert's just doing a total for one year, so your expression is just fine for what he's after. (that 'or' - really could't see it at first) In article <#5F8XlrLIHA.5***@TK2MSFTNGP04.phx.gbl>, robert11
@mountaincable.net says... Show quote > I am using VB6.0 SP5 You've had several suggestions, but I'd structure it completely > > I am using the following code in a DLL. > > Public Function CountTotalYearPayments(Optional StMnt As String, Optional > EndMnt As String, Optional Yr As String, Optional CompID As String, Optional > VendorID As String) As Object > 'Find Total Payments Made. > > Dim strSQL As String > > On Error GoTo SelectErr > > strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo Between > '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & Yr & "' AND CompID = '" & > CompID & "' AND VendorID = '" & VendorID & "'" > > Set CountTotalYearPayments = cnBills.Execute(strSQL) > > Exit Function > > SelectErr: > MsgBox Err.Number & " - " & Err.Description > CountTotalYearPayments = Err > Err.Clear > > End Function > > This works as I need. > > Question. > When I try to find the above, but the year is 2 different years, it does not > work. > ex: > strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo Between '" > & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & StYr & "' AND '" & EndYr & > "' AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & "'" > > How can I retreive the data from two different years differently from their suggestions, by combining the year and month into one expression (assuming they're both numbers; if they're strings then you need to do it a slightly differently, but the same principals apply: begindate = StYr * 100 + StMo endDate = EndYr * 100 + EndMo strsql = "select sum(amtpd) as amtpd from payments where yr * 100 + monthno between" & begindate & " and " & enddate -- Remove the ns_ from if replying by e-mail (but keep posts in the newsgroups if possible). Thanks to all that replied.
I may have not been to clear, but the following code gives me the data between 2 dates. strSQL = "SELECT * FROM Payments WHERE MonthNo BETWEEN '" & StMnt & "' AND '" & EndMnt & "' AND Yr BETWEEN '" & StYr & "' AND '" & EnYr & "' AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & "' ORDER BY Yr DESC, MonthNo DESC" Show quote "Robert" <rober***@mountaincable.net> wrote in message news:%235F8XlrLIHA.5400@TK2MSFTNGP04.phx.gbl... >I am using VB6.0 SP5 > > I am using the following code in a DLL. > > Public Function CountTotalYearPayments(Optional StMnt As String, Optional > EndMnt As String, Optional Yr As String, Optional CompID As String, > Optional VendorID As String) As Object > 'Find Total Payments Made. > > Dim strSQL As String > > On Error GoTo SelectErr > > strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo > Between '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & Yr & "' AND > CompID = '" & CompID & "' AND VendorID = '" & VendorID & "'" > > Set CountTotalYearPayments = cnBills.Execute(strSQL) > > Exit Function > > SelectErr: > MsgBox Err.Number & " - " & Err.Description > CountTotalYearPayments = Err > Err.Clear > > End Function > > This works as I need. > > Question. > When I try to find the above, but the year is 2 different years, it does > not work. > ex: > strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments WHERE MonthNo Between > '" & StMnt & "' AND '" & EndMnt & "' AND Yr = '" & StYr & "' AND '" & > EndYr & "' AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & > "'" > > How can I retreive the data from two different years > > -- > Thanks in advance > bob > rober***@mountaincable.net > "Robert" <rober***@mountaincable.net> wrote in message I really doubt that is working as you think it is.news:%23%23FeR8IMIHA.280@TK2MSFTNGP03.phx.gbl... > Thanks to all that replied. > > I may have not been to clear, but the following code gives me the data > between 2 dates. > > strSQL = "SELECT * FROM Payments WHERE MonthNo BETWEEN '" & StMnt & "' AND > '" & EndMnt & "' AND Yr BETWEEN '" & StYr & "' AND '" & EnYr & "' AND > CompID = '" & CompID & "' AND VendorID = '" & VendorID & "' ORDER BY Yr > DESC, MonthNo DESC" For example, if you want everything from 10/2006 to 2/2007 you'd be requesting "month between 10 and 2" and "year between 2006 and 2007". Even if the month part doesn't mind the reversed order you won't get Jan 2007 because month 1 is not included. You either have to use a more complex set of ranges or convert everything to dates. Samples of both approaches have been suggested. I tried your example, but I keep getting an error message.
"Yr is not defined in the Aggregate Expression" strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments where ((yr = '" & StYr & "' and monthno >= '" & StMnt & "') or (yr= '" & EnYr & "' and monthno <= '" & EndMnt & "')) AND CompID = '" & CompID & "' AND VendorID = '" & VendorID & "' ORDER BY Yr DESC, MonthNo DESC" '------------------ Show quote "Bob Butler" <noway@nospam.ever> wrote in message news:eM9cTJJMIHA.2268@TK2MSFTNGP02.phx.gbl... > "Robert" <rober***@mountaincable.net> wrote in message > news:%23%23FeR8IMIHA.280@TK2MSFTNGP03.phx.gbl... >> Thanks to all that replied. >> >> I may have not been to clear, but the following code gives me the data >> between 2 dates. >> >> strSQL = "SELECT * FROM Payments WHERE MonthNo BETWEEN '" & StMnt & "' >> AND '" & EndMnt & "' AND Yr BETWEEN '" & StYr & "' AND '" & EnYr & "' AND >> CompID = '" & CompID & "' AND VendorID = '" & VendorID & "' ORDER BY Yr >> DESC, MonthNo DESC" > > I really doubt that is working as you think it is. > > For example, if you want everything from 10/2006 to 2/2007 you'd be > requesting "month between 10 and 2" and "year between 2006 and 2007". > Even if the month part doesn't mind the reversed order you won't get Jan > 2007 because month 1 is not included. > > You either have to use a more complex set of ranges or convert everything > to dates. Samples of both approaches have been suggested. > > "Robert" <rober***@mountaincable.net> wrote in message I don't see anything obvious;news:ePAPBjJMIHA.1168@TK2MSFTNGP02.phx.gbl... >I tried your example, but I keep getting an error message. > > "Yr is not defined in the Aggregate Expression" > > strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments where ((yr = '" & StYr > & "' and monthno >= '" & StMnt & "') or (yr= '" & EnYr & "' and monthno <= > '" & EndMnt & "')) AND CompID = '" & CompID & "' AND VendorID = '" & > VendorID & "' ORDER BY Yr DESC, MonthNo DESC" What do you get when you do Debug.Print strSQL after that assignment? Is this Access, SQL Server, Oracle, something else? Are the columns in the table named 'Yr' and 'MonthNo'? What are the data types in the table for Yr and MonthNo?
Show quote
"Bob Butler" <noway@nospam.ever> wrote in message On second reading I do... you are selecting just Sum(AmtPd) but then trying news:%23OEOBdKMIHA.4684@TK2MSFTNGP06.phx.gbl... > "Robert" <rober***@mountaincable.net> wrote in message > news:ePAPBjJMIHA.1168@TK2MSFTNGP02.phx.gbl... >>I tried your example, but I keep getting an error message. >> >> "Yr is not defined in the Aggregate Expression" >> >> strSQL = "SELECT SUM(AmtPd) AS AmtPd FROM Payments where ((yr = '" & StYr >> & "' and monthno >= '" & StMnt & "') or (yr= '" & EnYr & "' and monthno >> <= '" & EndMnt & "')) AND CompID = '" & CompID & "' AND VendorID = '" & >> VendorID & "' ORDER BY Yr DESC, MonthNo DESC" > > I don't see anything obvious; to sort by Yr and MonthNo. That 'order by' wasn't there before. If you want totals by month you need to Select Yr, MonthNo, Sum(AmtPd) As TotalPd ....  |
|||||||||||||||||||||||
Other interesting topics