Multiple Selections

[pbaldy]

Have you confirmed what the combo is returning? Can you post the db?

 

[Officeboy]

It's in Access 2007, I can post it sure, but I think it's rather large,

 

[pbaldy]

If it's too big to post, perhaps a sample with just the relevant object(s).

 

[Officeboy]

here is the basic database for the form in question,

have fun...

 

[pbaldy]

I'm not sure exactly what process you're having trouble with (it often helps with a sample db to give the steps necessary to recreate the problem). When selecting something in the left hand listbox, this SQL gets created:

SELECT tblTradeList.Trade, count(tblProjectInfo.ProjectKey) as Total, tblTradeList.[Trade Id] FROM tblTradeList INNER JOIN tblProjectInfo ON tblTradeList.[Trade Id] = tblProjectInfo.Trade.Value WHERE tblProjectInfo.[City] in (SELECT c.[City ID] FROM tblCities as c WHERE c.[County ID] in (196) ) tblProjectInfo.[newOnHold] = 0 GROUP by tblTradeList.Trade,tblTradeList.[Trade ID] ORDER BY tblTradeList.Trade

As noted earlier, there's no AND or OR between the subquery and tblProjectInfo.[newOnHold] = 0

 

[Officeboy]

Sorry, i'm still new with all this, The steps to create the issue, Open the form, select a market, and select a county, The trade field should populate. when you select from Active to all, you will see the trade field fill up with every proejct weather it's OK, Inactive, or on hold, Any other selections shows a blank screen. The same goes for printing out the projects when you hit "Customize"

 

[pbaldy]

It was selecting a county that I saw that, so that invalid SQL is part of your problem. You need to add AND or OR as appropriate. Also, newOnHold doesn't seem to be a valid field, as once I solve the other problem, I get a parameter prompt on that.

 

[Officeboy]

hold on, I used an old backup of the tables, Let me correct the "new on hold" field and email it to you

 

[pbaldy]

Before you bother attaching again, try to fix the other problem. That may fix everything.

 

[Officeboy]

Here is the corrected version. I'm still not understanding where I add the And or OR function. I've gone over every line and I just am not seeing it.

I'm sorry for sounding stupid.. LOL

 

[pbaldy]

You have

Variable = "Apples"
Variable = Variable & "Oranges"

which results in

"ApplesOranges"

Wouldn't you add AND and some spaces either to the end of the first or beginning of the second to end up with

"Apples AND Oranges"

 

[Officeboy]

I see what your saying, Unfortunatly, I didnt write the code in the first place, so alot of it is above my head, I'm just trying to fix it with the update change. The original coder I'm not able to get in touch with, if you can give me an exact line so I can see where in the code your see'ing, it might make more sense to me.

sorry to be a pest..

:-(

 

[Officeboy]

Ok... I finaly get what you were saying.. the original code was
Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0" Or "tblProjectInfo.[newOnHold] = 2"
End Select


I needed to put AND here....

Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
whereclause = whereclause & "AND tblProjectInfo.[newOnHold] = 0"
Case 2 ' only inactive projects
whereclause = whereclause & "AND tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "AND tblProjectInfo.[newOnHold] = 2"
Case 4 'only active projects and projects on hold
whereclause = whereclause & ("AND tblProjectInfo.[newOnHold] = 0" Or "AND tblProjectInfo.[newOnHold] = 2")
End Select


Thanks for all the help!