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

[David McAfee]

I will eventually, it's me going through 200+ queries right now that are
hardcoded with date values.

I want to turn this manual mess into an SSIS package or even just a
scheduled SQL job(s)

I used to love Access, but I hate the QBE more and more each day. :)

SSMS is so much better.



On Fri, Jul 22, 2016 at 1:05 PM, John Colby <jwcolby at gmail.com> wrote:

> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>