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

[David McAfee]

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
>>
>
>