• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2063
  • Last Modified:

Crystal Reports Accept Multiple Parameters

I have a report where I use a command to grab fields and there is a parameter that should be able to accept multiple inputs, however, when I run the report with multiple I get the following error:

Failed to retrieve data from the database
Details: ADO Error code 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: Incorrect Syntax near ','.
SQL State: 42000
Native Error: 102 [Database Vendor Code : 102]

The query I have is below - the ASSIGNED_TO  is the problem.  The parameter has the box checked to allow multiple values - I'm not sure what the issue is here.


select nxstar_e.tar_num, (select nxsnotes.description from nxsnotes where id = nxstar_e.tar_num and nxsnotes.type = '1stcontact') as notedes, nxstar_e.assigned_to, nxstar_e.tar_status, nxstar_e.entered_date, nxstar_e.description, nxstar_e.bus_name
from nxstar_e where nxstar_e.entered_date >= {?EntryDate}
and nxstar_e.entered_date < DATEADD("d",1,{?EntryDate})
and nxstar_e.tar_status <> 'CLOSED'
and nxstar_e.assigned_to in ({?ASSIGNED_TO})

Open in new window

0
rondre
Asked:
rondre
1 Solution
 
mlmccCommented:
Are you running a command or is that a view in the database?

ry the last line as

and nxstar_e.assigned_to in {?ASSIGNED_TO}

mlmcc
0
 
rondreAuthor Commented:
I am running a command - I thought I'd already tried that but I must have been wrong as that worked - thanks for the help and sorry to have wasted time :)
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Two things:

1)  Quotes around the "d" in the dateadd function.  Double quotes denote a field name, which it isn't.  You're using Crystal syntax, not SQL syntax.

2)  You don't need the parentheses around the {?ASSIGNED_TO} parameter

Try the following code sample.

~Kurt

 
select
  nxstar_e.tar_num,
  (
  select
    nxsnotes.description
  from
    nxsnotes
  where
    id = nxstar_e.tar_num
    and nxsnotes.type = '1stcontact'
  ) as notedes,
  nxstar_e.assigned_to,
  nxstar_e.tar_status,
  nxstar_e.entered_date,
  nxstar_e.description,
  nxstar_e.bus_name
from
  nxstar_e
where
  nxstar_e.entered_date >= {?EntryDate}
  and nxstar_e.entered_date < DATEADD(day,1,{?EntryDate})
  and nxstar_e.tar_status <> 'CLOSED'
  and nxstar_e.assigned_to in {?ASSIGNED_TO}

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now