Dates from MSAccess in SQLReporting?????

[mrgreen]

I am attempting to create some reports based on my 2003 MSAccess tables. I am trying to get some Week Numbers, Month Numbers etc... I take it MSSQL doesn't like the way MSAccess handles date/time? I'm trying to start out slow here because I'm new to all of this. Below is a simple query that obviously doesn't work. I get an error saying
Error Source: obcjt32.dll
Undefined function "MonthName"


SELECT MONTHNAME([Date entered]) AS MyMonth
FROM tblmain

I apologize if this has been posted before but any help would sure be appreciaed.

Thanks

 

[pdx_man]

Well, I'm guessing you are doing this query in Access ... so there is no MONTHNAME function ... try: Format([Date Entered], "MMMM")

If you are looking to do this in SQL, you will have to do a CASE statement for the month:

CASE MONTH([Date Entered])
WHEN 1 THEN "January"
WHEN 2 THEN "February"
:
:

 

[mrgreen]

Actually I'm doing this in SQL Reporting 2005. It's an Access database.

 

[pdx_man]

I'm guessing you are looking for the DateName function.

SELECT DATENAME(MONTH, [Date Entered]) AS Month_Name

 

[mrgreen]

pdx man,

Thanks for the reply but this doesn't work with what I'm trying to do. I am trying to create a report in MSSQL 2005 based on an Access 2003 database. I can find the monthname in Access no problem but when I try to do it in MSSQL 2005 I run into problems. I receive an [Error 42000] [Microsoft] [ODBC Microsoft Access Driver] Undefined function 'DateName' in expression (obcjt32.dll).

 

[pdx_man]

OK, let's line things out here ...
You are running SQL 2005 Reporting Services
You have a MS Access database as the data source where the data is stored locally in the Access database.

My guess is that you are trying to reference a query in that Access database which uses an Access function. That isn't going to work, I don't believe as the Access database is not running, but I am not totally sure.

How am I doing so far?

If the above is true, I would create a linked server to the Access DB and write all of my queries in TSQL. I am assuming there is some requirement to have this data in Access and not just have it in SQL and then do linked tables to the Access database, and I am also assuming there is some reason why you are not able to use the reporting features inherent to Access.

Or you could have an Access query devoid of all functions and then re-create and reference that functionality in SQL.

 

[mrgreen]

pdx_man, thanks for being patient with me as I'm not an expert here. I do have

You are running SQL 2005 Reporting Services
You have a MS Access database as the data source where the data is stored locally in the Access database. - Yes


My guess is that you are trying to reference a query in that Access database which uses an Access function. That isn't going to work, I don't believe as the Access database is not running, but I am not totally sure. - Creating a Query in the Reporting Services based on my Access Table (No query).

Basically, I am new to the whole thing and was wondering why I couldn't reformat the date from Date and Time to just a date. In Access I have just a date but after exporting it the new tables have date and time. I'll keep plugging a long and look into what you've suggested. I'm not sure what TSQL is but I'll check it out.

Thanks