I thought this was solved, but I guess not. Why am I getting this erorr?

Hi Experts,

I thought this was solved, but I guess not. Why am I getting this erorr?

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

I have the function newFormatDateTime saved in the Modules of the database I am querying from.

Thanks
LVL 1
indyngAsked:
Who is Participating?
 
TimCotteeHead of Software ServicesCommented:
Hi indyng,

You cannot use functions in a module in access from any other query. ASP, VB etc cannot connect to your function if it is in a module in the access database. This cannot be worked around, it is a limitation of ADO.

Tim Cottee
0
 
indyngAuthor Commented:
This is my query:


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

rs.Open strSQL,conn
0
 
alorentzCommented:
Again, if the function was in the database, it would work...seems you're still mixing up databases.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
SimonBlakeCommented:
Does "newFormatDateTime" exist on the database as a function? or is it a typo?

Simon
0
 
indyngAuthor Commented:
I have the function in the database I am using and it still doesn't work in ASP. When I run the query in Access it works but not in ASP.
0
 
indyngAuthor Commented:
newFormatDateTime does exist on the database as a function

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

I saved it as Format Month Year
0
 
alorentzCommented:
Sounds like DB mix up still...because it would work from ASP if it works in Access.  Despite calling from ASP, the code is still executed from the database.
0
 
alorentzCommented:
>>You cannot use functions in a module in access from any other query

Hmmm, I've never tried it...but presumably it should work.
0
 
indyngAuthor Commented:
TimCottee,

THanks
0
 
alorentzCommented:
0
 
TimCotteeHead of Software ServicesCommented:
alorentz,

You found it before I could look it up.

Tim
0
 
alorentzCommented:
Just guessing at this point...but you may be able to save the query in Access and call it from ASP.

Save this part in Access as "query1":

strSQL = "SELECT Partners.PartnerName, Sum([Monthly Allowance Usage].ThisMonthsCalcAllwnce) AS SumOfThisMonthsCalcAllwnce, Sum([Monthly Allowance Usage].ThisMonthsUsage) AS SumOfThisMonthsUsage, [Monthly Allowance Usage].DateChk, newFormatDateTime([DateChk],'M4 - Y1',0) AS MonthYear"
strSQL = strSQL & " FROM Partners INNER JOIN [Monthly Allowance Usage] ON Partners.PartnerID = [Monthly Allowance Usage].PartnerID"
strSQL = strSQL & " GROUP BY Partners.PartnerName, [Monthly Allowance Usage].DateChk"


Then call from ASP like this:

SQL = "SELECT * from Query1 WHERE (((Partners.PartnerName)='" & Request("EmitterName") & "') AND (([Monthly Allowance Usage].DateChk) Between #" & Request("StartDate") & "# And #" & Request("EndDate") & "#))" & _
" ORDER BY Sum([Monthly Allowance Usage].ID);"


Not sure if that would work...I haven't used VBA for long time.




0
 
indyngAuthor Commented:
alorentz,

Your suggestion did not work. It doesn't make sense why it wouldn't work. oh well
0
 
alorentzCommented:
It should...maybe with some tweaking and debugging.  I'm just trying to establish the method of executing the function you need.  Up to you if you want to keep trying...
0
 
indyngAuthor Commented:
I want to keep trying. Thanks
0
 
indyngAuthor Commented:
alorentz,

Don't worrry. I have a work around. Thanks for the help.
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.