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

[David McAfee]

ahhh, thank goodness for this code snippet:

SELECT DISTINCT MSysObjects.Name, MSysQueries.Expression
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id
WHERE (((MSysQueries.Expression) Like "*" & "frmBuild" & "*"));

Shows me where I referenced my form in all my queries in the database.  :)


On Fri, Jul 22, 2016 at 2:50 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> My thought too - Comboboxes and listboxes (as opposed to listviews) are
> rows of textboxes.
> The don't have underlying bound values of other data types.
>
> The alternative would be to use:
>
> Format$(Me.txtFYStart,"yyyy-mm-dd") = Me.cboFY.Column(1)
> and
> ? forms!frmBuildCosReport!cboFY.column(1)<Format$(now(),"yyyy-mm-dd")
>
> --
> Stuart
>
> On 22 Jul 2016 at 12:02, Charlotte Foust wrote:
>
> > 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
> > >
> > --
> > 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
>