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
LVL 1
indyngAsked:
Who is Participating?
 
alorentzConnect With a Mentor Commented:
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:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.