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
I think you need something like this in your query (not tested)?

Code: 
where DateColumn = iif([parm a] = 1, Date(), #[parm b]#)

Hope it helps,

Alex

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

Thanks

But I am getting an error message when creating the expression. THE EXPRESSION YOU ENTERED HAS AN INVALID DATE VALUE

Parameter_B seems to be the issue. I set the parameter up as a Date/Time in Access. Is this the issue?

Mark

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
I'm not sure, I never have set up a parameter to have any specific value. You might try removing the # signs arount the parameter if Access already recognizes it as a date time?

That or the date column is set as a text column could be worth looking into.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Glad it worked Mark!

Good Luck on the rest of your project :)

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

I am making good headway. The final piece would be an IF THEN ELSE type statement.

Basically Parameter_A will have four options.

Option 1 = USE CURRENT DATE
Option 2 = USE CURRENT MONTH END DATE
Option 3 = USE PRIOR MONTH END DATE
Option 4 = USE PARAMETER_B

I have set up expressions that return the CURRENT MONTH END DATE and PRIOR MONTH END DATE fields. So those can be utilized in a Criteria expression. I named those CUREND and PRIOREND

So I basically need an expanded IF THEN ELSE formula in the CRITERIA. It would look like this in Crystal:

if {?Parameter_A} = 1
then {Invoice_Date} = CurrentDate
else
if {?Parameter_A} = 2
then {Invoice_Date} = {CUREND}
else
if {?Parameter_A} = 3
then {Invoice_Date} = {PRIOREND}
else
if {?Parameter_A} = 4
then {Invoice_Date} = {?Parameter_B}

Can Access do that?

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Maybe something like this? I try to avoid nested iif's like this, but I am not sure what other choice there is (other than building your query in crystal before its' sent, and I don't know if crystal can do that)

Code: 
where Invoice_Date = iif([Parameter_A]=2, [CUREND],
                     iif([Parameter_A]=3, [PRIOREND],
                     iif([Parameter_A]=4, [Parameter_B],
                     Date())))

Like I said though, I would use only one parameter, and set its' value from the reporting tool. I don't know if Crystal can do it, but in many others you can.

Hope this helps,

Alex

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

Hi.

That looked like it was going to work great, but now when i go to run the query. Access thinks the CUREND and PRIOREND expressions are themselves parameters, and its prompting me for those values (after prompting me for PARAMETER_A and PARAMETER_B).

Any ideas?

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Hey Mark,

Where are CUREND and PRIOREND coming from? Are these stored in your database, or are they passed from the crystal reports app? I was under teh impression that all four were parameters you were passing.

Almost there :)

Alex


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

CUREND and PRIOREND are expressions (fields) I created in the query

PRIOREND: DateSerial(Year(Date()),Month(Date()),1)-1
This returns the prior month ending date, which as of today would be 03/31/2007


CUREND is a two-step Expression.

CURENDSTEP1: DateSerial(Year(Date()),Month(Date()),28)+4
(this step creates a date sometime in the next month. If run today, it would return 05/04/2007)

CUREND: DateSerial(Year([CURENDSTEP1]),Month([CURENDSTEP1]),1)-1
(this takes CURENDSTEP1, converts it to the the first of the next month, then subtracts one day to return the last day of the current month. If run today, it would take 05/04/2007, turn it into 05/01/2007, then subtract 1 day to return April 30, 2007.

Hmm, based on your reply it just made me think that perhaps I should create an Access Table to store those values, if the query continues to treat them as parameters?

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
Ah, Ok.

You cannot use a calculated expression (by name) on the right side of your where clause. I know this will be a pain, but you will need to replace CUREND and PRIOREND with the calculations you are using to get them. For priorend its' easy (just copy and paste in place of PRIOREND in the expression I gave), but for CUREND you may wis hto use this:

Code: 
DateAdd("m",DateDiff("m",#1/1/1900#,Date()) + 1,#1/1/1900#)-1

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
If you are trying to refer to those expressions like this:
Code: 
SELECT DateSerial(Year(Date()),Month(Date()),1)-1 As PRIOREND From TableName WHERE [b]PRIOREND[/b] = SomeDate

you can't do that....you have to refer to the whole expression, not the alias:
Code: 
SELECT DateSerial(Year(Date()),Month(Date()),1)-1 As PRIOREND From TableName WHERE [b]DateSerial(Year(Date()),Month(Date()),1)-1[/b] = SomeDate



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
 
Upvote 0 Downvote
Hi everyone

I seem to have figured it out. once the query is 100% done and proofed I'll try and come back to leave the code.

The nest IIF is working. Its quite an involved nested IIF too....

Regards,

Mark H. Edwards
 
Upvote 0 Downvote
You're very welcome Mark. Good luck with the testing (that is always the worst part ;-) )

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Hi everyone.

Everything was proceeding pretty well until I realized I needed to make a change. The change shouldn't have been a big deal but the I am adding some piece of code that Access just doesn't like.

I am trying to do a nested iif with four options. The nested iif worked fine before. The first section of code (named ENDING_DATE_TY) below works fine, it includes three of the four parameters. The second section (named TEST) runs the fourth sucessfully. When I try and add the second section of code to the first, thats where Access tells me I have an error no matter how I tweak it (for parens, etc)

If anyone can help me, it would be great. This code can be added to any access query and it will work and maybe you can see what I am missing.

It will prompt you for two parameters. PERIOD ENDING is options 0 through 3. ENDING ENDING DATE only needs to be filled in if PERIOD ENDING is 3. For the sake of testing, use 2007/02/28 as the ENDING DATE.

The results should be as follows (when run today, May 2nd)

Period Ending: 0 returns 5/31/2007 (end of current month)
Period Ending: 1 returns 5/2/2007 (today's date)
Period Ending: 2 returns 4/30/2007 (end of prior month)
Period Ending: 3 returns 2/28/2007 (the hand entered date in ENDING DATE)

Here is the code:

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)))

Test: IIf([Period Ending]=1,DateSerial(Year(Date()),Month(Date()),Day(Date())))

Thanks!






Regards,

Mark H. Edwards
 
Upvote 0 Downvote
I do not have time at the moment to make sure I've got the parentheses right, so I am not going to post code. But if 1 is the ONLY option (if the parameter value <> one of the other 3), then you do not need an additional iif. Just have the Date expression for this value in the 'else' (or Value when False) portion of the iif.

Remember, this is the syntax for iif:

Code: 
iif(Boolean Expression, Value When True, Value When False)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Can you post the code you tried using?

Hope this 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
511
dhookom
dhookom
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
524
combo
combo
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
524
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