Why is this not working???

[rmork]

I have been all over this forum trying to figure out how to add "all" to my dropdown list. I have tried everything, but I`m stuck.
My version of access is 2003, and its in Norwegian if it matters. (I know there is something with ; insted of ,)

The error message I get form the RowScource property below is something like: "Found characters after the SQL-sentence"

I hope you can help me. You have been good to me before

SELECT "*" AS Principals.PrincipalID; "ALL" AS Principals.PrincipalName; 0 AS SortOrder FROM Principals UNION SELECT Principals.PrincipalID; Principals.PrincipalName; Principals.PrincipalNumber; Principals.Active;1 FROM Principals ORDER BY SortOrder; Principals.PrincipalName;

 

[rmork]

Update...
I got this code to work as far as the "ALL" is showing in the combo, but it is not listing all in the report...
If I choose one of the principals in the dropdown list its shoving that particular Principal in the report...

SELECT Principals.PrincipalID, Principals.PrincipalName, Principals.PrincipalNumber, Principals.Active FROM Principals UNION SELECT "*", "<All>", "", "" FROM Principals ORDER BY Principals.PrincipalName;

 

[PHV]

Could you, please, post the code launching the report ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmork]

Very kind of you to help me... here is the code:

Private Sub cmdReportPreview_Click()

On Error GoTo Err_cmdReportPreview_Click
DoCmd.OpenReport "rptAVD02PrincipalCommission", acPreview

Exit_cmdReportPreview_Click:
Exit Sub

Err_cmdReportPreview_Click:
MsgBox Err.Description
Resume Exit_cmdReportPreview_Click

End Sub

 

[PHV]

OK, you don't use the criteria named parameter of the OpenReport method, so could you, please, post the WHERE clause of the rptAVD02PrincipalCommission's underlaying SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmork]

Thankyou for quick response. I'm working on this now, so this is perfect..

Hope this is what you are asking for...:
WHERE (((Principals.PrincipalID)=[Forms]![rptAVD02PrincipalCommission]![PrincipalID]) AND ((AVD02Projects.OrderDate) Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]));

 

[PHV]

You may try this:
WHERE (Principals.PrincipalID=[Forms]![rptAVD02PrincipalCommission]![PrincipalID] OR [Forms]![rptAVD02PrincipalCommission]![PrincipalID]='*')
AND (AVD02Projects.OrderDate Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmork]

Sad to say that did not do the trick... Got an error message saying "the expression is to complicated to be evaluated...."

When I changed the wiew from SQL to what I normally use(not sure of the english word here, but it's the one with grids and you can se the tables and queries) i noticed that the OrderDate column had gotten two Conditions:

Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]

Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]

And there was a new column added called:
[Forms]![rptAVD02PrincipalCommission]![PrincipalID]
with ='*' as condition

Any idea?

 

[rmork]

could it have something to do with fact that not all of the options in the list will generate a result. There are principals in the list that has no orders. If I try to select these principals, the report wil generate, but with "#error" instead of values. Is there a way to ignore all principals that will not produce a result??

 

[rmork]

Hi PHV...
I figured it out,but could not have done this without you. You put me on the right track...
I played with the code that you precented me and I finally got it to work...
The code that worked for me is:

WHERE (((AVD02Projects.OrderDate) Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]) AND ((Principals.PrincipalID)=[Forms]![rptAVD02PrincipalCommission]![PrincipalID])) OR ((([Forms]![rptAVD02PrincipalCommission]![PrincipalID])='*'));

Have a star..

 

[PHV]

If think you have parenthesis issue.
you may try this:
WHERE (AVD02Projects.OrderDate Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo])
AND (Principals.PrincipalID)=[Forms]![rptAVD02PrincipalCommission]![PrincipalID] OR [Forms]![rptAVD02PrincipalCommission]![PrincipalID]='*');

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
WHERE (AVD02Projects.OrderDate Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo])
AND (Principals.PrincipalID=[Forms]![rptAVD02PrincipalCommission]![PrincipalID] OR [Forms]![rptAVD02PrincipalCommission]![PrincipalID]='*');

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgramError]

Is this the same thread as : thread702-1413164

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[rmork]

I'm afraid that is right. They started out as two different problems, and have merged into one big one..

 

[PHV]

Do you understand why the OR must be inside parentheses ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmork]

To be honest with you I'm a highshool teacher, and all of this is pretty new to me. I'm trying to learn, but I feel the learning curve is a bit steep...
Have been kind of thrown into this by a company that in the beginning was just looking to change some reports layout...