Trying to convert a sql server query to an Access 2007 query

[Proctor]

Hello: I have a query that I need to convert for use in Access. I have linked in the tables needed and also created query "qry_RawData" that is needed to run this query.

The query makes a call to a function "GetAcctPeriod_Month" that I wrote in sql server and I don't know how to go about creating this function to call it in my Access query? Is this possible; or, do I need to write out code that does the same things my function is doing in my new query I'm trying to create?

Here's the query that needs to be converted so you can see how i am calling this function in it:

SELECT     JobID, CASE WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) = 1 THEN 'JAN' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) 
                      = 2 THEN 'FEB' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) = 3 THEN 'MAR' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) 
                      = 4 THEN 'APR' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) = 5 THEN 'MAY' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) 
                      = 6 THEN 'JUN' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) = 7 THEN 'JUL' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) 
                      = 8 THEN 'AUG' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) = 9 THEN 'SEP' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) 
                      = 10 THEN 'OCT' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) = 11 THEN 'NOV' WHEN dbo.GetAcctPeriod_Month(JobCreatedDate) 
                      = 12 THEN 'DEC' END AS MONTH, YEAR(JobCreatedDate) AS YEAR, DATEPART(Week, JobCreatedDate) AS WEEK, JobCreatedBy, RequestRcvdDate, JobCreatedDate, 
                      JobCommitedDate, AssignedDate, JobOpenedDate, JobModifiedDate, AssignedTo, Name, SignName, JobStatus, Rush, + '''' + JobBomStatus AS JobBomStatus, 
                      TotalBoms, JobOpenedBy, Salesperson, Territory, Program, Initials, Comments, JobDueDate, JobModifiedBy, JobCommited, AssignedBy, PrepTime, 
                      REPLACE(PrepComments, ASCII(13), ' ') AS prepcomments, Units, JobBomID, JobBomSequenceID, BomNumber, AddNote, BomStatus, JobBomCreatedBy, 
                      JobBomCreatedDate, JobBomModifiedBy, JobBomModifiedDate, TotalPs, JobBomGeID, GeNumber, Height, Stroke, Color, + '''' + CanDepth AS CanDepth, Vinyl, 
                      ModulesPerFoot, ModulesPerMeter, UnitsOnCenter, [Ge Comments], Pn, TotalLeds, NoOfRows, ModulesPerPowerSupply, WattagePerPowerSupply, Footage, Spacing, 
                      JobBomGeCreatedBy, JobBomGeCreatedDate, JobBomGeModifiedBy, JobBomGeModifiedDate, GuidelineOption, CASE WHEN BOMNUMBER = 1 AND 
                      GENUMBER = 1 THEN 1 ELSE 0 END AS HEADER
FROM         qry_RawData

Here's the function that's in sql server:

ALTER     function [GetAcctPeriod_Month] (
@inDate datetime = '1/1/2006'
)
returns varchar(10)
as
begin

declare @Year varchar(4)
declare @Month varchar(2)

select @Month = case 
when @inDate between per_start##1 and per_end##1 then 1
when @inDate between per_start##2 and per_end##2 then 2
when @inDate between per_start##3 and per_end##3 then 3
when @inDate between per_start##4 and per_end##4 then 4
when @inDate between per_start##5 and per_end##5 then 5
when @inDate between per_start##6 and per_end##6 then 6
when @inDate between per_start##7 and per_end##7 then 7
when @inDate between per_start##8 and per_end##8 then 8
when @inDate between per_start##9 and per_end##9 then 9
when @inDate between per_start##10 and per_end##10 then 10
when @inDate between per_start##11 and per_end##11 then 11
when @inDate between per_start##12 and per_end##12 then 12
when @inDate between per_start##13 and per_end##13 then 12
end

from tblPeriods
where fiscal_year = year(@inDate)

if len(@month) = 1
	set @month = '0' + @month
--return @month+'-'+cast(year(@inDate) as varchar(4))

return @month
end

Thank you for your help.
Proctor

[gibra]

Sorry, but the Access database:

1. doesn't support CASE WHEN ...
So you replace CASE WHEN using Iif/Then ...


2. some internal Access functions not works in each scenario.
i.e. You own Access 2007 (and this is ok, no problems), but if you deploy your application to customers which own Access 2010 then the REPLACE (and many other functions) no longer work.
They work only when used internally in MSAccess environment.

Thank to Microsoft

[Proctor]

Gibra: Thank you for your help. I will replace my Case statements with If/then statements then....but
can i call a function in a query in access? Perhaps you explained below and I didn't understand....if so I apologize.

Thanks,
Proctor