X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Brain Fart - Query Design, Any Version    
post Mar 19 2017, 10:52 AM

Posts: 87
Joined: 8-June 04

So, I'm having a little brain fart. I can't remember how to design a query that references a "Year" control box on a form and uses the "Year" value in a date range query.

I am trying to query everything between 1/1 and 1/31 (all of January) and have the year driven by the "Year" control box on the form.

Here is what i have so far, but it keeps running an error about an invalid date value:

between #1/1/# And [Forms]![frmBankLedger]![comboyear] and #1/31/# And [Forms]![frmBankLedger]![comboyear]

Go to the top of the page
post Mar 19 2017, 11:00 AM

UtterAccess VIP
Posts: 4,434
Joined: 5-June 07
From: UK

you need a column in your query something like

checkyear: year([mydate])

then in the criteria you can compare this value. in SQL, something like

"where year([mydate]) = " & whatever

Dave (Male)

(Gemma was my dog)
Go to the top of the page
tina t
post Mar 19 2017, 01:31 PM

Posts: 5,340
Joined: 11-November 10
From: SoCal, USA

or, as an alternative solution, you can use the DateSerial() function in the query's WHERE clause, as

>= DateSerial([Forms]![frmBankLedger]![comboyear],1,1) And <= DateSerial([Forms]![frmBankLedger]![comboyear],1,31)


"the wheel never stops turning"
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd April 2018 - 12:21 AM