[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-06-08
16
Medium Priority
?
230 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:indyng
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 1

Author Comment

by:indyng
ID: 16861398
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
 
LVL 31

Expert Comment

by:alorentz
ID: 16861410
Again, if the function was in the database, it would work...seems you're still mixing up databases.
0
 
LVL 7

Expert Comment

by:SimonBlake
ID: 16861428
Does "newFormatDateTime" exist on the database as a function? or is it a typo?

Simon
0
Technology Partners: 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!

 
LVL 1

Author Comment

by:indyng
ID: 16861429
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
 
LVL 1

Author Comment

by:indyng
ID: 16861520
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
 
LVL 31

Expert Comment

by:alorentz
ID: 16861521
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
 
LVL 43

Accepted Solution

by:
TimCottee earned 1000 total points
ID: 16861523
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
 
LVL 31

Expert Comment

by:alorentz
ID: 16861543
>>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
 
LVL 1

Author Comment

by:indyng
ID: 16861570
TimCottee,

THanks
0
 
LVL 31

Assisted Solution

by:alorentz
alorentz earned 1000 total points
ID: 16861618
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16861648
alorentz,

You found it before I could look it up.

Tim
0
 
LVL 31

Expert Comment

by:alorentz
ID: 16861668
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
 
LVL 1

Author Comment

by:indyng
ID: 16861806
alorentz,

Your suggestion did not work. It doesn't make sense why it wouldn't work. oh well
0
 
LVL 31

Expert Comment

by:alorentz
ID: 16861820
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
 
LVL 1

Author Comment

by:indyng
ID: 16861828
I want to keep trying. Thanks
0
 
LVL 1

Author Comment

by:indyng
ID: 16861948
alorentz,

Don't worrry. I have a work around. Thanks for the help.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question