Re: Attempting to SELECT BETWEEN dates - [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Message has been deleted

mar...@gmail.com

unread,

Oct 10, 2012, 7:05:56 AM10/10/12

to

For the record it's an MS Access 2007-2013 database and 'created' is a column of the Date/Time specification.

LastOfTheMonth() returns an integer indicating the amount of days in the given month.

dtmArchiveMonthBeignsOn and dtmArchiveMonthEndsOn are formatted as dd.mm.yyyy whereas data in 'created' are formatted as dd.mm.yyyy hh:mm:ss

Bob Barrows

unread,

Oct 10, 2012, 3:43:37 PM10/10/12

to

mar...@gmail.com wrote:
> I am trying to select all the entries between the first and the last
> of any given month, but I am experiencing a data type mismatch as you
> can see.

Well ... no ... I cannot see. I'm not looking at your screen. Which line
generates the error?

> The format of the dates are dd.mm.yyyy.
The format of the date is irrelevant. Access stores dates as Decimals, with
the whole number portion representing the number of days since the seed
date, and the decimal representing the time of day.

> I have been banging
> my head into my desk for some time now trying to figure this out. Can
> anyone see anything wrong here?
>
> viewPort = Request.QueryString("view")
>
> Select Case viewPort
> Case "category"
> sSQL = "SELECT * FROM blog_entries WHERE category = '" &
> Request.QueryString("category") & "'"
> Case "archive"
> dtmArchiveMonthBeginsOn = Request.QueryString("when")
> dtmArchiveMonthEndsOn = DateAdd("d",
> (LastOfTheMonth(dtmArchiveMonthBeginsOn)-1), dtmArchiveMonthBeginsOn)
> sSQL = "SELECT * FROM blog_entries WHERE created BETWEEN '" &
> dtmArchiveMonthBeginsOn & "' AND '" & dtmArchiveMonthEndsOn & "'"

You cannot debug a sql statement without knowing what it is. Use
Response.Write SQL
to make the sql statement being passed to Access visible.
The goal with dynamic sql is to create a string that can be copied and
pasted into the SQL View of an Access Query Builder window and run as-is.

Show us the string your concatenation generates.

Martin Severin Steffensen

unread,

Oct 10, 2012, 4:16:31 PM10/10/12

to

Apologies for not providing the query. This is the result of the concatenation:
SELECT * FROM blog_entries WHERE created BETWEEN '01.10.2012' AND '31.10.2012'

Michael Bednarek

unread,

Oct 10, 2012, 7:42:20 PM10/10/12

to

On Wed, 10 Oct 2012 13:16:31 -0700 (PDT), Martin Severin Steffensen
<mar...@gmail.com> wrote in microsoft.public.scripting.vbscript:

>Apologies for not providing the query. This is the result of the concatenation:
>SELECT * FROM blog_entries WHERE created BETWEEN '01.10.2012' AND '31.10.2012'

AFAIK literal dates in MS Access have to be specified as #mm/dd/yyyy#

Try ... BETWEEN #10/1/2012# AND #10/31/2012#

--
Michael Bednarek, Brisbane "ONWARD"

Bob Barrows

unread,

Oct 11, 2012, 1:06:59 PM10/11/12

to

That is true, but Access will also successfully interpret literals supplied
with ISO format: yyyy-mm-dd.

The problem can be avoided, I believe, by using parameters.

sql = ""SELECT * FROM blog_entries WHERE created " & _
"BETWEEN ? AND ?"
set cmd=createobject("adodb.command")
set cmd.activeconnection = cn
cmd.commandtext=sql
cmd.commandtype=1 'adCmdText
cmd.parameters.append cmd.createparameter("sdate", _
135,1,,dtmArchiveMonthEndsOn)
cmd.parameters.append cmd.createparameter("edate", _
135,1,,dtmArchiveMonthEndsOn)
set rs=cmd.execute