Skip to main content
D
DDewbre
Inspiring
March 6, 2011
Question

MS Access Query/CFMAIL Problem

  • March 6, 2011
  • 2 replies
  • 470 views

Hi All,

  I'm having a bit of a problem...and I figure its a simple error on my part but I'm not sure what I'm missing.  Any help would be appreciated.

Situation:  I'm trying to have the system send an e-mail to our Marketing reps every day IF they have something on the calendar.

Problem: For some reason it appears that my system isn't returning anything from the DB to send the e-mail...even though something is there.

Supporting Tools: MS Access DB  (Date Field Formatted mm/dd/yyyy)

Get Code:

     
            <cfoutput query="getTodayMkEvents">
              <br />
               User EMail: #uWkEmail#
             <br />
            <b>Event: </b> #mkEvent#
            <br />
             <b>Date/Location:</b> #DateFormat(mkDate, 'MM/DD/YYYY')# / #mkLocation#
            <br />
             <b>Time:</b> #mkTime#
            <br />
             <b>Note:</b> #mkNote#
             <br />
             <br />
            </cfoutput>

Query Code:

<!--- Retrieve Today's Marketing Events --->
<cffunction name="listTodayMkEvents" returntype="query">
<cfargument name="ODBCTODAY" required="yes" type="date">

<cflock timeout="30">
<cfquery name="getTodayMkEvents" datasource="#REQUEST.DataSource#">
SELECT *
FROM claimrepappt
    WHERE mkDate = #ODBCTODAY#
      
  </cfquery>
</cflock>

<cfreturn getTodayMkEvents>
</cffunction>

    This topic has been closed for replies.

    2 replies

    ilssac
    ilssac
    Inspiring
    March 7, 2011

    drdagwood wrote:

    Supporting Tools: MS Access DB  (Date Field Formatted mm/dd/yyyy)

    If the field is truly a Date Field as you say here, then it is NOT formatted 'mm/dd/yyyy'.   If it is a date field, the value in the database is probably some very large integer that is the number of seconds (or milliseconds) since some epoch date.  I'm not sure how Access does this, but that is how just about every other computer system deals with date data.

    Since the makers of tools, like Access, know that humans are not going to like dealing with very large integers for dates, they format the date in some human readable way, such as 'mm/dd/yyyy'.

    But, again taking your word that the field is a date field like it should be, you need to pass a computer date value to the query, not a human one.

    The easiest way to do this is with the <cfqueryparam ... cf_sql_type="cfsqldate"> (or cfsqltimestamp or whatever is the appropriate type) with or without a createODBCObject() function.

    J
    JMF3
    Participating Frequently
    March 6, 2011

    Out of curiosity, make sure that the query is passing the date correctly

    to Access:

    </cfquery

    D
    DDewbreAuthor
    Inspiring
    March 6, 2011

    It does appear to be passing it in the same format as in the DB.  mm/dd/yyyy

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices