Passing date

[rwpear...@gmail.com]

I have been trying to pass a date/s to sql but to no avail.

The start and end dates are in the tblConfiguration and I've tried using DLookup as shown below......no workie!!

What am I doing wrong????

Dim StartDate As String
Dim EndDate As String

StartDate = DLookup("[YearBegins]", "[tblConfiguration]")
EndDate = DLookup("[YearEnds]", "[tblConfiguration]")


Me.RecordSource = "SELECT * FROM tblIncome where [tblIncome].[TransactionDate] Between StartDate and EndDate"

TIA

[Patrick Finucane]

> TIA4

You put date variables in #'s. Startdate/Enddate are variables. not part of the SQL string. So between #" & StartDate & "# And #" & EndDate & "#" would work better.

[Ron Paii]

Expanding on Patrick's post

"between and" is short hand for "[TransactionDate] >= datestart and [TransactionDate] < dateend"

Since date columns can include time if [TransactionDate] values includes time; the query will not return transactions on the end date and may miss some on the start date. you should do the following

between #" & datevalue(startdate) & "# and #" & datevalue(dateadd("d",1, enddate) & "# "




PS: I am a BIG fan of named and typed parameters in queries. They eliminate errors caused by type mismatches when Access builds the query plan. You can check the parameter values before attempting to run the query; making you code much easier to debug. They can also be noticeably faster because Access less work to do interpreting parameter types.

[Ulrich Möller]

Hi Ron,

Am 19.04.2018 um 14:25 schrieb Ron Paii:
> On Wednesday, April 18, 2018 at 10:03:55 PM UTC-5, Patrick Finucane wrote:
>> On Wednesday, April 18, 2018 at 10:06:33 PM UTC-4, rwpear...@gmail.com wrote:
>>> I have been trying to pass a date/s to sql but to no avail.
>>>
>>> The start and end dates are in the tblConfiguration and I've tried using DLookup as shown below......no workie!!
>>>
>>> What am I doing wrong????
>>>
>>> Dim StartDate As String
>>> Dim EndDate As String
>>>
>>> StartDate = DLookup("[YearBegins]", "[tblConfiguration]")
>>> EndDate = DLookup("[YearEnds]", "[tblConfiguration]")
>>>
>>>

Since dlookup returns a variant type, the data type should also be
defined like this, instead of using a string type, otherwise problems
with nulls may occur.

>>> Me.RecordSource = "SELECT * FROM tblIncome where [tblIncome].[TransactionDate] Between StartDate and EndDate"
>>>
>>> TIA4
>> You put date variables in #'s. Startdate/Enddate are variables. not part of the SQL string. So between #" & StartDate & "# And #" & EndDate & "#" would work better.
> Expanding on Patrick's post
>
> "between and" is short hand for "[TransactionDate] >= datestart and [TransactionDate] < dateend"

please note that the upper operator is "less or equal"  not simple
"less". This is something different.

> Since date columns can include time if [TransactionDate] values includes time; the query will not return transactions on the end date and may miss some on the start date. you should do the following

that's the point

> between #" & datevalue(startdate) & "# and #" & datevalue(dateadd("d",1, enddate) & "# "

and to take this into account the easiest way is to compare only the
date part like  "where DateValue([Transactiondate]) between #" &
startdate & "# and #" & enddate "# "

see also http://www.vb123.com.au/200309_ds_aa.htm for some other useful
tips using dates in access databases.

Ulrich

[Ron Paii]

Another site I like for Access is http://allenbrowne.com/tips.html

I assumed [YearBegins] could be January 1st and [YearEnds] would be December 31st of the same year; or whatever range of dates are used for the companies business year. In realty using only column names that was a risky assumption. I would add code to verify startdate and enddate are dates in acceptably ranges. That site's "Fun with Access Dates" is another reason to use typed query parameters. They convert your text date into a SQL date handling most formatting issues. Comes in handy when referencing non Access databases.

[Ulrich Möller]

Am 19.04.2018 um 15:44 schrieb Ron Paii:

<...>
> Another site I like for Access ishttp://allenbrowne.com/tips.html

>
> I assumed [YearBegins] could be January 1st and [YearEnds] would be December 31st of the same year; or whatever range of dates are used for the companies business year. In realty using only column names that was a risky assumption. I would add code to verify startdate and enddate are dates in acceptably ranges. That site's "Fun with Access Dates" is another reason to use typed query parameters. They convert your text date into a SQL date handling most formatting issues. Comes in handy when referencing non Access databases.

Parameter queries in connection with date types are a good idea.

Another approach I use is to store the date in fixed-length string
fields in iso format without separators. The advantage is that even
incomplete entries such as month/year or only year are possible and
facilitate data exchange with other applications/systems, e.g. SPC-controls.
For easier handling I created a wrapper class for a text field as a new
"Datebox" control and a module for handling the new data type "Stringdate".

Ulrich

[rwpear...@gmail.com]

Thank you all for your input......with your help I have now solved the problem. Thanks again!!!