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

[Paul Hartland]

In that case I reckon it must have something to do with one or more of the
union queries not converting or casting correctly, but out on town at
moment so cant do any testing, if you want to send me sample data and the
query offline, would be happy to look at it, my email is
paul.hartland at googlemail.com

On 22 Jul 2016 19:00, "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
>