Can we see the script and the actual error messsage for the second case (including line number). The first case just needed quotes adding.

Yes.

use strict;
use warnings;
use DBI;
use Data::Dumper;
use JSON::XS;

my $dbh = DBI->connect
  (q{DBI:ODBC:test})
  or die $DBI::errstr;

my $sth = $dbh-> prepare("SELECT val from buf WHERE dd_mm_yyyy= ? ") o
+r die $dbh->errstr();
$sth->execute('2012-07-22 00:00:00') or die $dbh->errstr();
my $table;
my $tbl_ary_ref;
$tbl_ary_ref = $sth->fetchall_arrayref;
     if (defined $tbl_ary_ref){
         $table->{date} = $tbl_ary_ref;
     }
    $tbl_ary_ref  = encode_json $table;
    print qq{JSON-------------------JSON\n};
    print $tbl_ary_ref."\n";
    
$sth->finish;
$dbh->disconnect;
[download]

And output:

Error at Line : syntax error near ?
JSON-------------------JSON
{"date":[]}
[download]

That Perl looks syntactically correct to me so I assume the message comes from MS Access and yet if that were the case your script would have died at line 11 or 12. The output with the JSON seems to confirm that. Are you sure that is the script you are running?