[AccessD] Date in Combo box with SQL view as rowsource returning as string

[Charlotte Foust]

Comboboxes normally return strings when you reference other than their
bound column.  In the later versions of Access, you can't necessarily get
away with inferring a datatype.  But unless you're using the time portion
of Now(), you would be better off using Date() for your comparison.

Charlotte Foust
(916) 206-4336

On Fri, Jul 22, 2016 at 11:18 AM, David McAfee <davidmcafee at gmail.com>
wrote:

> I even tried this on the OnChange event of the combo box:
> Me.txtFYStart = CDate(Me.cboFY.Column(1))
> Me.txtFYEnd = CDate(Me.cboFY.Column(2))
>
> And the results:
> ? forms!frmBuildCosReport!txtFYStart < now()
> False
>
>
> I swear, I've never HAD to do this in queries before:
> ? CDATE(forms!frmBuildCosReport!txtFYStart) < now()
> True
>
>
> Oh well, off to modify queries with CDate()...
>
>
> On Fri, Jul 22, 2016 at 10:59 AM, David McAfee <davidmcafee at gmail.com>
> wrote:
>
> > I've tried:
> >  CONVERT(VARCHAR(10),StartDate , 101) AS FyStartDate,
> >
> > Same results.
> >
> > I shouldn't even have to cast or convert if it is a date field in the
> > table.
> > Same with the text boxes.
> >
> > I seriously think the gremlins are out to get me! :)
> >
> > D
> >
> > On Fri, Jul 22, 2016 at 10:49 AM, Paul Hartland <
> > paul.hartland at googlemail.com> wrote:
> >
> >> I assume you have, but have you tried using CONVERT instead of cast
> >>
> >> On 22 Jul 2016 18:38, "David McAfee" <davidmcafee at gmail.com> wrote:
> >>
> >> > I've been updating existing queries other users have created that use
> >> > hardcoded values to use a form with a combo box.
> >> >
> >> > The combo box uses a SQL view as it's row source.
> >> > The view pulls from a table:
> >> >
> >> > SELECT FY, StartDate, EndDate, sFY, dFY, uFY FROM tblFiscalYears
> >> >
> >> > The results are as expected
> >> > (actually the original view was simply selecting values unioned
> together
> >> > without a table)
> >> > 1314 2013-07-01 2014-06-30 14/15 14-15 14_15
> >> > 1415 2014-07-01 2015-06-30 14/15 14-15 14_15
> >> > 1516 2015-07-01 2016-06-30 15/16 15-16 15_16
> >> > 1617 2016-07-01 2017-06-30 16/17 16-17 16_17
> >> >
> >> > Most of the queries use one of the variations of 1516, 15-16... and
> are
> >> > compared as text.
> >> > Today I noticed a query returning incorrect results and I found that
> the
> >> > 2nd and 3rd (date) columns are being treated as text, rather than
> dates.
> >> >
> >> > I tried casting as dates, but still no difference:
> >> >
> >> > SELECT FY, CAST(StartDate as datetime) AS FyStartDate, CAST(EndDate as
> >> > DATETIME) AS FyEndDate, sFY, dFY, uFY FROM tblFiscalYears WITH
> (NOLOCK)
> >> >
> >> > I even made two hidden text boxes on the form, with format set to
> short
> >> > date.
> >> > The on change event of the combo box populates the text boxes.
> >> >
> >> > Everything I tried always results in a fail:
> >> > ? forms!frmBuildCosReport!cboFY.column(1)<now()
> >> > False
> >> > ? forms!frmBuildCosReport!txtFYStart < now()
> >> > False
> >> >
> >> > This works:
> >> > ? CDATE(forms!frmBuildCosReport!cboFY.column(1))<now()
> >> > True
> >> > But, I don't think I should have to do this (not to mention update
> many
> >> man
> >> > y queries).
> >> >
> >> > Am I going nuts?
> >> >
> >> > TIA,
> >> > David
> >> > --
> >> > 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
>