Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameter question in Access Query

Status
Not open for further replies.
DRCI

DRCI

MIS
May 1, 2003
48
US
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
 
Sort by date Sort by votes
Alex:

I tried the following (and varitions thereof regarding parens, etc):

Ending_Date_TY: IIf([Period Ending]=3,DateSerial(Left([Ending Date],4),Mid([Ending Date],6,2),Right([Ending Date],2)),IIf([Period Ending]=0,DateSerial(Year(Date()),Month(Date()),Day((DateSerial(Year(Date()),Month(Date())+1,1)-1))),IIf([Period Ending]=2,DateSerial(Year(Date()),Month(Date()),1)-1)),IIf([Period Ending]=1,DateSerial(Year(Date()),Month(Date()),Day(Date()))

and

Ending_Date_TY: IIf([Period Ending]=3,DateSerial(Left([Ending Date],4),Mid([Ending Date],6,2),Right([Ending Date],2)),IIf([Period Ending]=0,DateSerial(Year(Date()),Month(Date()),Day((DateSerial(Year(Date()),Month(Date())+1,1)-1))),IIf([Period Ending]=2,DateSerial(Year(Date()),Month(Date()),1)-1)),Date())

Remember option 1which comes last returns the current date, which is why the second set of code simply ends with the Date() function.

Thank you, sir.

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
I think your expression might be too complex. Try a VBA function like this (pasted into a code module)

Code: 
Function WhichDate(PeriodEnding As Integer, EndingDate As String)

Dim out As String

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

And call it in your query like this:

Code: 
WhichDate([Period Ending], [Ending Date]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Alex

Thanks

Like I said the nested IIF did work at one point (unfortunately i dont have a saved copy of the code that worked)

I have never created a code module in Access but i think I see how to. I'll give it a shot.

Mark

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Alex

I added the function to the Modules section

I named it WhichDate.

I then went to the query and put WhichDate([Period Ending], [Ending Date]) in as a BUILD in the FIELD (Expression) and it doesnt give me an error.

However, when I go to run th query, it says:

Undefined Function 'WhichDate' in expression

What did I miss?

Mark





Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Wasn't this supposed to be in the where clause of your query?

Can you post the SQL view of the query you currently have?

Thanks,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Even in the WHERE clause, it says the same error

I am putting it both in the WHERE as well a making it a field just so i can what value it returns (since eventually this will be one part of a date range).


Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Ok here is the SQL view with your function call. Again, the query wont actually run. It says the function is undefined.
Note that I had to add a closing paren to your code.

SELECT GACCENTRYA.ACCDAT_0, GACCENTRYA.CCE_0, GACCENTRYA.CCE_1, GACCENTRYA.CNA_0, GACCENTRYA.AMTLOC_0, GACCENTRYD.SNS_0, GACCENTRYA!AMTLOC_0*GACCENTRYD!SNS_0 AS Amount
FROM GACCENTRYA INNER JOIN GACCENTRYD ON (GACCENTRYA.TYP_0 = GACCENTRYD.TYP_0) AND (GACCENTRYA.NUM_0 = GACCENTRYD.NUM_0) AND (GACCENTRYA.LIG_0 = GACCENTRYD.LIG_0)
WHERE (((GACCENTRYA.ACCDAT_0)=WhichDate([Period Ending],[Ending Date])));



Regards,

Mark H. Edwards
 
Upvote 0 Downvote
I don't think you can name the Module "WhichDate" AND have the function named "WhichDate"...from what you said above:

I added the function to the Modules section

I named it WhichDate.
Click to expand...

It sounds like that's what you did.

leslie
 
Upvote 0 Downvote
Ah I missed that. Good Catch Leslie!

The module should be named something like 'Utilities' or 'DateFunctions'.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
OK I renamed the Module

I also modified Alex's code to remove the spaces in the parameters since the module didnt have spaces and also added the >= sign:

>=WhichDate([PeriodEnding],[EndingDate])

Now when I run it the error message is:

THE EXPRESSION IS TYPED INCORRECTLY, OR IT IS TOO COMPLEX TO BE EVALUATED. FOR EXAMPLE, A NUMERIC EXPRESSION MAY CONTAIN TOO MANY COMPLICATED ELEMENTS. TRY SIMPLIFYING THE EXPRESSION BY ASIGNING PARTS OF THE EXPRESSION TO VARIABLES.


Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Is your column of data type DATE or data type TEXT?

I'm running out of ideas here, but I think that probably it is a date? If so, I think you can just change this part of the function:

Dim out As String

to this:

Dim out As Date

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
and can you post the code from your WhichDate function and the SQL you're trying to run with the function?

Leslie
 
Upvote 0 Downvote
Yes the Date field is a Date.

I changed the DIM to AS DATE, still getting the same error.

Here is the WhichDate Function:
--------------------------------------------
Function WhichDate(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

WhichDate = out

End Function

----------------------------------------

Here is the SQL

SELECT GACCENTRYA.ACCDAT_0, GACCENTRYA.ACCDAT_0 AS ACCDAT_1, GACCENTRYA.CCE_1, GACCENTRYA.CNA_0, GACCENTRYA.AMTLOC_0, GACCENTRYD.SNS_0, GACCENTRYA!AMTLOC_0*GACCENTRYD!SNS_0 AS Amount
FROM GACCENTRYA INNER JOIN GACCENTRYD ON (GACCENTRYA.TYP_0 = GACCENTRYD.TYP_0) AND (GACCENTRYA.NUM_0 = GACCENTRYD.NUM_0) AND (GACCENTRYA.LIG_0 = GACCENTRYD.LIG_0)
WHERE (((GACCENTRYA.ACCDAT_0)>=WhichDate([PeriodEnding],[EndingDate])))
ORDER BY GACCENTRYA.ACCDAT_0 DESC;
----------------------------------------------

I was just trying another approach too and it showed promise but failed.

I thank you all for your help. I got so close. But I think
I'm going to need to find an Access specialist to hire onto this project today. If you see something above please post it and I'll try it. Otherwise I again thank you for all your help and if I do bring the specialist in, I'll be sure to post the code once he/she creates it for me.




Regards,

Mark H. Edwards
 
Upvote 0 Downvote
What about these:

Code: 
GACCENTRYA!AMTLOC_0*GACCENTRYD!SNS_0 AS Amount

THose exclamations points should be periods.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
OK, what about putting pound signs around the date?

Code: 
WHERE (((GACCENTRYA.ACCDAT_0)>=#WhichDate([PeriodEnding],[EndingDate])#))



Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Guess I should've clarified that ;-)

# is Jet SQL's date delimiter.

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
497
dhookom
dhookom
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
510
combo
combo
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
510
lameid
lameid
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
395
TheresAlwaysAWay
TheresAlwaysAWay
JimStrand
  • Locked
  • Question
Crosstab query - returning 1 record per week instead of 1 record
Replies
1
Views
280
JimStrand
JimStrand

Part and Inventory Search

Sponsor

Back
Top