Parameter question in Access Query

[DRCI]

Hello

I am passing two parameters to an Access Query from a Crystal Report. Parameter_A will tell the query to use invoices dated CURRENT DATE if Parameter_A = 1 (1 being a number, not text, parameter type) .

If Parameter_A is not 1, then it will ignore Parameter A and utilize the date in Parameter_B (a date type parameter).

I have already created the PARAMETERS in the Query, naming them the exact same as in the Cyrstal Report that will be using the query.

I know I need to BUILD an Expression in the CRITERIA of the Invoice_Date Field in my query. This is where I need help. Building this expression.

In Crystal reports, the formula would look like this:

if {?Parameter_A} = 1
then {Invoice_Date} = currentdate
else
{Invoice_Date} = {?Parameter_B}

What would the Access Expression be?

Thanks in advance.

Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[DRCI]

Didnt help.

I then tried choosing the function from within the BUILD window in Design View, which gave me this code:

<=WhichDate("«PeriodEnding»","«EndingDate»")

But when I go to run it I get a DATA TYPE MISMATCH IN CRITERIA EXPRESSION



Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[AlexCuse]

Period Ending should not be in quotes, since it is an integer not a string.

Ignorance of certain subjects is a great part of wisdom

 

[DRCI]

Yeah I realized that after I posted. Its because at te time i had put the module back to AS STRING temporarily.

So then I went back and put the mocudule back to AS DATE, saved it, and went into BUILD and when I chose the function the quotes were gone

<=WhichDate(«PeriodEnding»,«EndingDate»)

But it still has the same error.

Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[lespaul]

Is EndingDate a date field or a string field? if it's a date why are you passing it in to WhichDate as a string?

PeriodEnding is an integer field, correct?

And ACCDAT_0 is also a date?

 

[DRCI]

EndingDate is a string parameter. I made it a string for a reason but now as I think of it I could probably change to a date type. But for now it is a string. The code makes it into a date field (Dateserial)

PeriodEnding is an number. Perhaps Integer is the wrong number format? Is there something else (like SINGLE) that also acts as a number?

PeriodEnding will always be either 0,1,2,or 3.

ACCDAT_0 is a date field. The Criteria (WHERE) is creating a Date (DateSerial) to use in selecting the records returned.

Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[lespaul]

are you sure the DateSerial function is returning the correct information. In the help, it indicates that the parameters (year, month, day) should be integers (the Mid, Left and Right functions return strings) and the return of DateSerial is a Variant(Date). In any case, the example shows the variable not being declared as ANY type, like this:

Function WhichDate(PeriodEnding As Integer, EndingDate As String)

[b]Dim out[/b]

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

WhichDate = out

End Function

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[DRCI]

Hi everyone

I have had sucess with using the module approach. I use the module in the WHERE critertia and the query gives me what i want.

Thanks

Howevever now when I try and use the Query in a Crystal Report, 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

 

[DRCI]

Hi everyone.

Does anyone have any ideas about the UNDEFINED FUNCTION error I get in Crystal?
should better post this in the Crystal forum now, since its probably more a Crystal issue.


Regards,

Mark H. Edwards

http://www.datareportconsulting.com

 

[AlexCuse]

Sounds like a crystal issue to me.

One thing you might try is saving your querydef in access as 'myQry' or something.

Then in Crystal you could possibly just use 'select * from myQry' or something like that? I have a feeling if you are generating the SQL in Crystal, that the extensions you have made to Access SQL within your database might not be available.

*note I have never touched Crystal Reports before, so take this with a grain of salt

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom