Hello,

I'm creating a tool in Access 2000 that will massage data (mainly adjusting
dates for weekends and holidays) from our corporate Oracle database so it can
be manually entered into someone else's website.  I have no control over the
Oracle database.  Here's the setup.  User enters number on form in unbound
control and clicks command button.   Data is pulled from Oracle into a
recordset.   The recordset data is displayed in more unbound controls on the
form only if certain criteria are met (and after a date is massaged).  

I think the problem is the recordset is making the memo field a "adVarWChar"
instead of a "adLongVarWChar".   This the part of the SQL that refers to the
memo field:  First(VRSC_MCS_SVC_REQ.MSR_COMMENTS) AS Comments.

I have no idea how to change the data type of a recordset.   I've searched
and searched!  Please help!

Here's the offending code with some parts removed for an attempt a brevity
and clarity!

Thanks in advance!

Karrie

----
Private Sub cmdSSInfo_Click()
On Error GoTo Err_cmdSSInfo_Click

Dim rst As ADODB.Recordset, frm As Form
Dim cnn As ADODB.Connection
Dim strSR As String, dtSMSDate As Date
Dim sqlSR As String,

Set cnn = CurrentProject.Connection
Set frm = [Forms]![frmMain]
Set rst = New ADODB.Recordset

'Code here for Getting SR from form
strSR = Nz(frm![txtSR], "")
If strSR = "" Then
 MsgBox "Please enter SR number and try again", vbOKOnly
 Exit Sub
Else
 sqlSR = "(((VRSC_MCS_SVC_REQ.MSR_SR_NUM)=" & strSR & ")"
'Get the SR data from SMS as a record set
 rst.Open "SELECT VRSC_MCS_SVC_REQ.MSR_SR_NUM AS SR,
VRSC_MCS_SVC_REQ.MSR_RESP AS Response, " _
 & "Max(VRSC_MCS_SVC_REQ_ACTION.MSRA_ACTION_NUM) AS Trip,
VRSC_MCS_SVC_REQ.MSR_TICKET_NUM AS Ticket, " _
 & "VRSC_MCS_SVC_REQ.MSR_OPEN_DATE_TIME AS [Open Date],
Min(VRSC_MCS_SVC_REQ_ACTION.MSRA_ARRIVE_TIME) " _
 & "AS [Work Start], Max(VRSC_MCS_SVC_REQ_ACTION.MSRA_DEPART_TIME) AS [Work
End], " _
 & "First(VRSC_MCS_SVC_REQ.MSR_COMMENTS) AS Comments, " _
 &
"(Max([VRSC_MCS_SVC_REQ_ACTION].[MSRA_DEPART_TIME])-Max([VRSC_MCS_SVC_REQ].[MSR_OPEN_DATE_TIME]))*24 AS KPI " _
 & "FROM VRSC_MCS_SVC_REQ INNER JOIN VRSC_MCS_SVC_REQ_ACTION ON
VRSC_MCS_SVC_REQ.MSR_SR_NUM = " _
 & "VRSC_MCS_SVC_REQ_ACTION.MSRA_SR_NUM GROUP BY
VRSC_MCS_SVC_REQ.MSR_SR_NUM, " _
 & "VRSC_MCS_SVC_REQ.MSR_RESP, VRSC_MCS_SVC_REQ.MSR_TICKET_NUM, " _
 & "VRSC_MCS_SVC_REQ.MSR_OPEN_DATE_TIME, VRSC_MCS_SVC_REQ.MSR_CUST_ID,
VRSC_MCS_SVC_REQ.MSR_SR_STATUS " _
 & "HAVING " & sqlSR & " AND ((VRSC_MCS_SVC_REQ.MSR_CUST_ID)='SG02222'))", _
 cnn, adOpenForwardOnly, adLockReadOnly
End If

'Check to make the recordset has data otherwise give error message
If Not rst.BOF And Not rst.EOF Then
 rst.MoveFirst
 dtSMSDate = rst.Fields("Open Date")
 ' check business day and time
   If BusinessDay(dtSMSDate) = True Then
--snip---
   Else
     'get next business day
--snip--
   End If
 ' Check number of trips and display data as needed.
 If rst.Fields("Trip") > 1 Then
 ' If multiple trip display message
 frm![txtInfo] = "There are multiple trips.  Do not enter data before
checking with supervisor."
 Else
 ' If single trip check for pass fail and display data
 frm![txtInfo] = "Use information below to update."
 frm![txtTicket] = rst.Fields("Ticket")
 frm![txtWorkStart] = Format(rst.Fields("Work Start"), "Short Date")
 frm![txtWorkEnd] = Format(rst.Fields("Work End"), "Short Date")
 frm![txtComments] = rst.Fields("Comments")
 End If

Else
 MsgBox "There is no data for this SR number.  Please try again", vbOKOnly
 Exit Sub
End If

Exit_cmdSSInfo_Click:
   Exit Sub

Err_cmdSSInfo_Click:
   MsgBox Err.Description
   Resume Exit_cmdSSInfo_Click
   
End Sub