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

[John Colby]

Is the data in sql server?  It appears to be so.  Run the query direct 
in sql server and get it running there.


On 7/22/2016 1:36 PM, David McAfee 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

-- 
John W. Colby