[AccessD] AXP Combobox Shows blank formatted date column

I've just been dealing with a similar problem where I was using Left() in a
combo's rowsource (I was taking the left 255 characters from a memo field so
that I could sort on it).  The combo would show blank rows on certain
machines.

Here is what I've found:

This is an Access 97 application. 
The application uses Outlook to send email, so it has a reference set to the
Outlook object library - for Outlook 97.
It is running on a group of machines, some of which have Outlook 2000 and
other have 97. This does not cause the 'usual' missing reference problem,
rather Left() and Now() and such would need seem to function in Queries.

Here's how I fixed it.

I wrote a little module that wrapped the VBA functions Left, Right, Mid, Now
and Date like this...

Option Compare Database
Option Explicit

' This module wraps a bunch of common VBA functions to help avoid problems
with
' different versions of Outlook

Function uLeft(strSource As String, nPos As Long) As String
   uLeft = VBA.Left$(strSource, nPos)
End Function

Function uRight(strSource As String, nPos As Long) As String
   uRight = VBA.Right$(strSource, nPos)
End Function

Function uMid(strSource As String, nStart As Long, Optional nEnd As Long =
0)
   If nEnd > 0 Then
      uMid = VBA.Mid$(strSource, nStart, nEnd)
   Else
      uMid = VBA.Mid$(strSource, nStart)
   End If
End Function

Function uNow() As Date
   uNow = VBA.Now()
End Function

Function uDate() As Date
   uDate = VBA.Date()
End Function

Function uFormat(sExpression As Variant, sFormat As String, Optional
nFirstDayOfweek As Long = vbSunday, Optional nFirstWeekOfYear As Long =
vbFirstJan1) As Variant
   uFormat = VBA.Format$(sExpression, sFormat, nFirstDayOfweek,
nFirstWeekOfYear)
End Function

What this code does is prefix the calls to the VBA function with the
explicit object library reference (VBA.) and this seems to have fixed my
problem, so perhaps it can do the same for yours.

Having written the module I then used Rick Fisher's Find and Replace to seek
out and change all the calls to these functions to my 'universal' versions
(hence the 'u' suffix in the function names).


HTH

Lambert

> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
> Sent:	Thursday, March 03, 2005 4:45 PM
> To:	Access Developers discussion and problem solving
> Subject:	RE: [AccessD] AXP Combobox Shows blank formatted date column
> 
> We're trying to find out *why* it's happening so we can prevent it, and
> the form is shared with the Access version, so an sproc or view is out
> of the question.  The client is in Oklahoma and using a runtime
> executable, so there isn't any printing to the immediate window.  The
> fact that the dates appear if we remove the Format function from the
> rowsource strongly suggests that the dates are there but that something
> is interfering in rendering them.
> 
> Charlotte Foust
> 
> 
> -----Original Message-----
> From: dmcafee at pacbell.net [mailto:dmcafee at pacbell.net] 
> Sent: Thursday, March 03, 2005 11:15 AM
> To: Access Developers discussion and problem solving
> Cc: Steve White
> Subject: RE: [AccessD] AXP Combobox Shows blank formatted date column
> 
> 
> Have you printed to the immediate window to see if the value is actually
> in the combo (just not displaying)?
> 
> What about changing the resource to a view or sproc that formats the
> number for you?
> 
> D
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
> Foust
> Sent: Thursday, March 03, 2005 11:04 AM
> To: AccessD at databaseadvisors.com
> Cc: Steve White
> Subject: [AccessD] AXP Combobox Shows blank formatted date column
> 
> 
> We have a client installation where certain machines running Windows
> 2000 are seeing an odd behavior in a combobox that displays two columns,
> a report number and a medium formatted date.  We have not been able to
> reproduce the behavior here even on Win2k, so I need some ideas. The
> back end is SQL Server 2000 and the combobox has this for a rowsource:
> 
> SELECT  ReportNo, ReportNo as [Number], Format(ReportDate, "dd-mmm-yy")
> FROM DailyDrillHdr WHERE
> ((DailyDrillHdr.WellID=[Forms]![frmDailyRpt]![cboWellID])) Order by
> ReportNo desc;
> 
> This works fine when the form opens and a WellID is selected.  It breaks
> when the user brings up a popup form that allows them to select a
> different WellID and hand that back to the initial form.  The initial
> form obligingly changes the WellID in its Well combobox, which
> repopulates the subform, and it specifically requeries the report number
> combobox in the AfterUpdate event of the WellID combobox.
> 
> At this point, however, on those problem machines, the date column of
> the report number combo is blank and it doesn't show dates again until
> the form is closed and reopened.  The combobox is being requeried,
> because the correct report numbers do show up in it, but not the dates
> associated with them.  The really odd part is that if you take the
> formatting out of the combobox rowsource, the dates show up every time.
> 
> 
> Has anyone else seen something like this?  I know I have, but it's been
> years ago and I can't remember what caused it!
> 
> Charlotte Foust
> Infostat Systems, Inc.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com