Crystal problem with Access Query using a Module

[DRCI]

Hello all.

I have a question that was brought up here:

http://www.tek-tips.com/viewthread.cfm?qid=1276962

But the workaround was not actually posted by SOUTHWESTNUT.

I have an Access module named EndDate (Function name End_Date) to identify one of four date options from a parameter. I put that module in the WHERE statement of an Access Query.

When I try and use the Query in a Crystal Reports, Crystal won't let me add the query to the report. I get the following error:

Query Engine Error:'42000:[Microsoft][ODBC Microsoft Access Driver] Undefined Function'End_Date' in expression.'

Any ideas?

This is my modified module now named End_Date:

Function End_Date(PeriodEnding As Integer, EndingDate As String)

Dim out As Date

Select Case PeriodEnding
Case 3
out = DateSerial(Left(EndingDate, 4), Mid(EndingDate, 6, 2), Right(EndingDate, 2))
Case 0
out = DateSerial(Year(Date), Month(Date), Day((DateSerial(Year(Date), Month(Date) + 1, 1) - 1)))
Case 2
out = DateSerial(Year(Date), Month(Date), 1) - 1
Case Else
out = Date
End Select

End_Date = out

End Function

My WHERE in the Query is :

WHERE (((GACCENTRYA.ACCDAT_0)<=End_Date([«PeriodEnding»],[«EndingDate»])));

Thanks


Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[GoStillers]

Why not just create a formula within Crystal?

 

[DRCI]

There's alot going on in the query. Access has to use the date parameters to choose the records before rolling them up into YYYYMM buckets.

By the time the data is fed to Crystal, true dates are gone. Only YYYYMM buckets remain.

And sometimes the YYYYMM buckets are only supposed to be a partial month (ie: Last year MTD May)

Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[GoStillers]

I'm not sure if Crystal can use an Access module like that, but I would do the manipulation within Crystal. Sorry I couldn't be more helpful!

-Brian

 

[DRCI]

OK

To be clear, the module is inside an Access Query. I am trying to use the Access Query in Crystal.

Regards,

Mark H. Edwards

http://www.datareportconsulting.com