Reporting services using access database

[rkrause]

I have an access database that i want to create a reporting services report from.
The data lies in 1 table called main.

THIS is my sql statement in my dataset but i get no results.
Is there something different i have to do when using parameters and getting data from Access, because if i use those type of parameters in sql it works fine.

SELECT employeename,EEnumber, writtenby, dateandtimeofevent, areaobserved, description, disciplinaryactiontaken, followuprequired, followuptime, autonumber
FROM MAIN
WHERE dateandtimeofevent = @date

 

[boblarson]

How are you connecting SSRS to Access anyway?

This might be helpful to you.

 

[rkrause]

I have an ODBC connection to the access datbase, and then using the ODBC connection in my Datasource.

i also get the error, ODBC does not support named parameters, Use unnamed parameters?

 

[boblarson]

I have an ODBC connection to the access datbase, and then using the ODBC connection in my Datasource.

i also get the error, ODBC does not support named parameters, Use unnamed parameters?

The problem is that you are trying to use a SQL Server type parameter:

WHERE dateandtimeofevent = @date

I'm not sure exactly what you would be passing to it in order to evaluate the parameter as I've not used SSRS connected to an Access database before. But that is the issue.

 

[rkrause]

I thought about that being wrong about the SQL parameter part. Anyone else have any ideas on what the access parameter would be when passing one through?

 

[boblarson]

You might be able to use a UDF in place there:

WHERE dateandtimeofevent = MyUDF()

And that udf return the date you have in @date.

 

[rkrause]

where would i set up the UDF?

 

[boblarson]

where would i set up the UDF?

The UDF is a User Defined Function in SQL Server. Are you using SQL Server in conjunction with Access or just Access for this report?

 

[boblarson]

Oh, and also - where are you getting the date where you were referring to @date?

 

[rkrause]

IM using just access for this report, the table in my access DB has a field called dateandtimeofevent and i want the user to imput a date they want to pull info for

 

[boblarson]

IM using just access for this report, the table in my access DB has a field called dateandtimeofevent and i want the user to imput a date they want to pull info for

So, that seems like we're getting beyond anything I've ever tried, so most of my suggestions are just guesses.

I'm not sure how you would convert the input to a usable parameter in this case. Puzzling.

 

[rkrause]

Thats what ive been find out. The current report is in Crystal report and we are moving away from that, and going to SSRS, which i like alot better.

 

[boblarson]

I've posted a request for any of our AWF VIP's to come help if they have SSRS experience. Hopefully someone does.

 

[rkrause]

Thanks. i hope someone out there has done this or seen this?

 

[SQL_Hell]

Hi there

It's been a while since I have used Reporting services but try the following

SELECT employeename,EEnumber, writtenby, dateandtimeofevent, areaobserved, description, disciplinaryactiontaken, followuprequired, followuptime, autonumber
FROM MAIN
WHERE dateandtimeofevent = ?