• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Why doesn't this function work in the query?

Hi Experts,

I have the following:

<%@ language="vbscript" %>

<%
'The script for this graph is from customclick.asp from Chart Director www.advsofteng.com
%>

<!--#INCLUDE FILE="connection\sopenconn.asp"-->

<!--#INCLUDE FILE="functions\MyDateFormat.txt"-->

<%
Dim arrMonth(12)

Set cd = CreateObject("ChartDirector.API")

arrMonth(1) = "Jan"
arrMonth(2) = "Feb"
arrMonth(3) = "Mar"
arrMonth(4) = "Apr"
arrMonth(5) = "May"
arrMonth(6) = "Jun"
arrMonth(7) = "Jul"
arrMonth(8) = "Aug"
arrMonth(9) = "Sep"
arrMonth(10) = "Oct"
arrMonth(11) = "Nov"
arrMonth(12) = "Dec"

i = 1

'Query data for the line chart


Set rs=Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Partners.PartnerName, newformatDateTime([Monthly Allowance Usage].DateChk,'M4 - Y1',0),  Sum([Monthly Allowance Usage].ThisMonthsCalcAllwnce) AS SumOfThisMonthsCalcAllwnce, Sum([Monthly Allowance Usage].ThisMonthsUsage) AS SumOfThisMonthsUsage"
strSQL = strSQL & " FROM Partners INNER JOIN [Monthly Allowance Usage] ON Partners.PartnerID = [Monthly Allowance Usage].PartnerID"
strSQL = strSQL & " WHERE ((([Monthly Allowance Usage].DateChk) Between #" & Request("StartDate") & "# And #" & Request("EndDate") & "#))"
strSQL = strSQL & " GROUP BY Partners.PartnerName, newformatDateTime([Monthly Allowance Usage].DateChk,'M4 - Y1',0)"
strSQL = strSQL & " HAVING (((Partners.PartnerName)='" & Request("EmitterName") & "'))"
strSQL = strSQL & " ORDER BY Sum([Monthly Allowance Usage].ID);"

rs.Open strSQL,conn

I am getting this error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'newformatDateTime' in expression.
/intermodal/load_record/Graph_AllowUsage_RTM_All.asp, line 43

Line 43 is: rs.Open strSQL,conn

The newformatDateTime returns the date in the format "mmm - yy"    ( i.e. Jun - 06 )

How can I fix this?

Thanks
0
indyng
Asked:
indyng
  • 9
  • 6
1 Solution
 
indyngAuthor Commented:
I used the MonthName function but it doesn't work. I get this:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'MonthName' in expression.
/intermodal/load_record/Graph_AllowUsage_RTM_All.asp, line 43

THANKS
0
 
joeposter649Commented:
Try this for your group by...
strSQL = strSQL & " GROUP BY Partners.PartnerName, [Monthly Allowance Usage]"
0
 
indyngAuthor Commented:
joeposter649

This does not do what I want.  I need to format the field [Monthly Allowance Usage].DateChk as "mmm - yy". Hence I am using a function inside a query.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
alorentzCommented:
Format it AFTER you get it from database...in ASP.  

And, what kind of format has 3 "m" digits? mmm-yy?
0
 
indyngAuthor Commented:
alorentz,

I cannot I am using it in a graph

<%@ language="vbscript" %>

<%
'The script for this graph is from customclick.asp from Chart Director www.advsofteng.com
%>

<!--#INCLUDE FILE="connection\sopenconn.asp"-->

<LINK REL=stylesheet HREF="styles\styles.css" TYPE="text/css">


<!--#INCLUDE FILE="functions\MyDateFormat.txt"-->

<%
Dim arrMonth(12)

Set cd = CreateObject("ChartDirector.API")

arrMonth(1) = "Jan"
arrMonth(2) = "Feb"
arrMonth(3) = "Mar"
arrMonth(4) = "Apr"
arrMonth(5) = "May"
arrMonth(6) = "Jun"
arrMonth(7) = "Jul"
arrMonth(8) = "Aug"
arrMonth(9) = "Sep"
arrMonth(10) = "Oct"
arrMonth(11) = "Nov"
arrMonth(12) = "Dec"

i = 1

'Query data for the line chart


Set rs=Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Partners.PartnerName, [Month] & ' - ' & [Year] AS MonthYear,  Sum([Monthly Allowance Usage].ThisMonthsCalcAllwnce) AS SumOfThisMonthsCalcAllwnce, Sum([Monthly Allowance Usage].ThisMonthsUsage) AS SumOfThisMonthsUsage"
strSQL = strSQL & " FROM Partners INNER JOIN [Monthly Allowance Usage] ON Partners.PartnerID = [Monthly Allowance Usage].PartnerID"
strSQL = strSQL & " WHERE ((([Monthly Allowance Usage].DateChk) Between #" & Request("StartDate") & "# And #" & Request("EndDate") & "#))"
strSQL = strSQL & " GROUP BY Partners.PartnerName,  [Month] & ' - ' & [Year]"
strSQL = strSQL & " HAVING (((Partners.PartnerName)='" & Request("EmitterName") & "'))"
strSQL = strSQL & " ORDER BY Sum([Monthly Allowance Usage].ID);"

rs.Open strSQL,conn

'Response.Write "strSQL:" & strSQL & "<br>"

Set dbTable = cd.DBTable(rs)


'Create a XYChart object of size 500 x 300 pixels
Set c = cd.XYChart(1000, 600)

'Use a pale yellow background (0xffff80) with a black (0x0) edge and a 1 pixel
'3D border
Call c.setBackground(&Hffffff, &H0, 1)

'Set plotarea at (55, 45) with size of 420 x 200 pixels. Use white (0xffffff)
'background. Enable both horizontal and vertical grid by setting their colors to
'light grey (0xc0c0c0)
Call c.setPlotArea(55, 45, 920, 450, &Hffffff).setGridColor(&Hc0c0c0, &Hc0c0c0)

'Add a legend box at (55, 45) (top of plot area) using 8 pts Arial Bold font
'with horizontal layout Set border and background colors of the legend box to
'Transparent
Set legendBox = c.addLegend(55, 45, False, "arialbd.ttf", 10)
Call legendBox.setBackground(cd.Transparent)

'Reserve 10% margin at the top of the plot area during auto-scaling to leave
'space for the legends.
Call c.yAxis().setAutoScale(0.1)


'Add a title to the y axis
Call c.yAxis().setTitle("Loads")

'Set the labels on the x axis. Draw the labels vertical (angle = 90)
Call c.xAxis().setLabels(dbTable.getcol(1)).setFontAngle(90)


'Add a line layer to the chart
Set layer = c.addLineLayer()

'Set the default line width to 3 pixels
Call layer.setLineWidth(3)

'Add the data sets to the line layer
Call layer.addDataSet(dbTable.getcol(2), -1, "Allowance")
Call layer.addDataSet(dbTable.getcol(3), -1, "Usage")

'Create the image and save it in a temporary location
chart1URL = c.makeSession(Session, "chart1")


%>

<html>
<body onLoad="if(top.name!='MyWindow'){MyWindow=window.open(top.location,'MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes');MyWindow.moveTo(0, 0);MyWindow.resizeTo(screen.width,screen.height);opener=null;window.close()}; myForm.userid.focus(); DisableEnterAndTab();" oncontextmenu="return(false);">

<head>

</head>

<TABLE border="0">
  <tr>
    <td>
      <FONT CLASS="ftitle"><%=Request("EmitterName")%>&nbsp;&nbsp;FMA Allowances and Usage Graph</FONT>
    </td>
  </tr>
</TABLE>

<BR>

<img src="myimage.asp?<%=chart1URL%>" border="0" usemap="#map1">
<map name="map1">
<%=chartImageMap%>
<%=legendImageMap%>

</map>



</body>
</html>


0
 
alorentzCommented:
Meaning, group it in some regular SQL grouping, and then reformat for display in ASP.


Like:

GROUP BY Partners.PartnerName, month([Monthly Allowance Usage].DateChk)&year(([Monthly Allowance Usage].DateChk))

Then format the result in ASP like you want.  Don't do it in SQL.
0
 
alorentzCommented:
Hmmm...then you probably need some sort of lookup table for Quarter, because the date will not translaste to a quarter in SQL.

I assume you are using Access?
0
 
alorentzCommented:
OK, I haven't read this post thoroughly enough.

What is the stored format and datatype of ([Monthly Allowance Usage].DateChk)
0
 
alorentzCommented:
FYI, 'newformatDateTime' needs to be a function stored in the Access database...in the Modules section.  If you do that, it should work.  And ASP function will not work in this case.
0
 
indyngAuthor Commented:
alorentz,

I have stored newformatDateTime as a function in Access database in the Modules section and I am now getting this error:

Ambiguous name in query expression 'newformatDateTime([DateChk],"M4")

The newformatDateTime function is the following:




Public Function newFormatDateTime(dtmInput, strFormat, intOffset)
    '/* D1 = 1 : D2 = 01 : D3 = 1st */
    '/* W1 = M : W2 = Mon : W3 = Monday */
    '/* M1 = 1 : M2 = 01 : M3 = J : M4 = Jan : M5 = January */
    '/* Y1 = 06 : Y2 = 2006 */
    '/* H1 = 12-hr clock : H2 = 12-hr clock with zero : H3 = 24-hr clock : H4 = 24-hr clock with zero */
    '/* N1 = minutes : N2 = minutes with zero */
    '/* S1 = seconds : S2 = seconds with zero */
    '/* F1 = am/pm : F2 = AM/PM */
    '/* ISO = Y2-M1-D1 H1:N1:S1 */

    Dim intDay, intMonth, intYear, intHour, intMinute, intSecond, intDayOfWeek

    If IsNull(dtmInput) Then
        Exit Function
    End If
    If (Not IsDate(dtmInput)) Then     '/* fail - not a real date */
        Exit Function
     End If
    If (Not IsNumeric(intOffset)) Then     '/* fail - not a useful time offset */
        Exit Function
    End If

    '/* set the timezone difference if needed, this also works with negative offset */
    dtmInput = DateAdd("h", intOffset, dtmInput)

    intDay = DatePart("d", dtmInput)
    intMonth = DatePart("m", dtmInput)
    intYear = DatePart("yyyy", dtmInput)
    intHour = DatePart("h", dtmInput)
    intMinute = DatePart("n", dtmInput)
    intSecond = DatePart("s", dtmInput)
    intDayOfWeek = Weekday(dtmInput)

    If (strFormat = "ISO") Then     '/* useful for entering into databases */
        newFormatDateTime = intYear & "-" & leadingZero(intMonth) & "-" & leadingZero(intDay) & " " & leadingZero(intHour) & ":" & leadingZero(intMinute) & ":" & leadingZero(intSecond)
        Exit Function
    End If
   
    strFormat = Replace(strFormat, "D1", intDay, 1, -1, 0)
    strFormat = Replace(strFormat, "D2", leadingZero(intDay), 1, -1, 0)
    If (intDay = 1) Or (intDay = 21) Or (intDay = 31) Then
        strFormat = Replace(strFormat, "D3", intDay & "st", 1, -1, 0)
    ElseIf (intDay = 2) Or (intDay = 22) Then
        strFormat = Replace(strFormat, "D3", intDay & "nd", 1, -1, 0)
    ElseIf (intDay = 3) Or (intDay = 23) Then
        strFormat = Replace(strFormat, "D3", intDay & "rd", 1, -1, 0)
    Else
        strFormat = Replace(strFormat, "D3", intDay & "th", 1, -1, 0)
    End If

    strFormat = Replace(strFormat, "W1", Left(WeekdayName(intDayOfWeek), 1), 1, -1, 0)
    strFormat = Replace(strFormat, "W2", WeekdayName(intDayOfWeek, 1), 1, -1, 0)
    strFormat = Replace(strFormat, "W3", WeekdayName(intDayOfWeek, 0), 1, -1, 0)

    strFormat = Replace(strFormat, "M1", intMonth, 1, -1, 0)
    strFormat = Replace(strFormat, "M2", leadingZero(intMonth), 1, -1, 0)
    strFormat = Replace(strFormat, "M3", Left(MonthName(intMonth), 1), 1, -1, 0)
    strFormat = Replace(strFormat, "M4", MonthName(intMonth, 1), 1, -1, 0)
    strFormat = Replace(strFormat, "M5", MonthName(intMonth), 1, -1, 0)

    strFormat = Replace(strFormat, "Y1", Right(intYear, 2), 1, -1, 0)
    strFormat = Replace(strFormat, "Y2", intYear, 1, -1, 0)

    If (intHour < 12) Then
        strFormat = Replace(strFormat, "H1", intHour, 1, -1, 0)
        strFormat = Replace(strFormat, "H2", leadingZero(intHour), 1, -1, 0)
        strFormat = Replace(strFormat, "F1", "am", 1, -1, 0)
        strFormat = Replace(strFormat, "F2", "AM", 1, -1, 0)
    Else
        strFormat = Replace(strFormat, "H1", (intHour - 12), 1, -1, 0)
        strFormat = Replace(strFormat, "H2", leadingZero(intHour - 12), 1, -1, 0)
        strFormat = Replace(strFormat, "F1", "pm", 1, -1, 0)
        strFormat = Replace(strFormat, "F2", "PM", 1, -1, 0)
    End If
    strFormat = Replace(strFormat, "H3", intHour, 1, -1, 0)
    strFormat = Replace(strFormat, "H4", leadingZero(intHour), 1, -1, 0)

    strFormat = Replace(strFormat, "N1", intMinute, 1, -1, 0)
    strFormat = Replace(strFormat, "N2", leadingZero(intMinute), 1, -1, 0)

    strFormat = Replace(strFormat, "S1", intSecond, 1, -1, 0)
    strFormat = Replace(strFormat, "S2", leadingZero(intSecond), 1, -1, 0)

    newFormatDateTime = strFormat
End Function

Public Function leadingZero(intInput)
    Dim strNumber

    If Not IsNumeric(intInput) Then
        Exit Function
    End If

    If (intInput >= 0) And (intInput <= 9) Then
        strNumber = "0" & intInput
    Else
        strNumber = intInput
    End If

    leadingZero = strNumber
End Function




0
 
alorentzCommented:
Run the query in Access to test, NOT ASP.  Just hardcode test values in Access query (same query as ASP), and the error will be pinpointed exactly in VBA compiler.
0
 
alorentzCommented:
Any luck?
0
 
indyngAuthor Commented:
alorentz,

I did run the query in Access and not ASP. That was the error. Thanks
0
 
alorentzCommented:
Then the error should take you to the error line.

OR, call the function from a FORM...on button click or something, and that will show you where the error is.

You gotta do a little debugging yourself...I don't have your database.
0
 
indyngAuthor Commented:
alorentz,

It works! I was using a wrong database. haha

Thanks
0
 
alorentzCommented:
>>I was using a wrong database

Yes, that would surely do it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now