What is the syntax for an OR statement ? 2

[ruthcali]

i am using Access97.

i have a form (called frmRptTotals) with a combo box (called cboCity) showing city codes such as SEA, SFO, LAN.

This form opens a report.

the query the report is based on contains the following critieria for the City column:
[Forms]![frmRptTotals]![cboCity]

So the user picks the city from the form that they want the report to be based on. Everything works fine except i want to add 'All California' to my cboCity combo box.

so, i am trying to make an entry in the combo box, but i don't know the syntax.

"SFO" or "LAN" doesn't work
'SFO' or 'LAN' doesn't work
SFO "or" LAN doesn't work
SFO & "OR" & LAN doesn't work
"SFO or LAN" doesn't work

any ideas??
thanks,
ruth ruth.jonkman@wcom.com

 

[RickSpr]

You can't do it this way, I'm afraid. When you put "[Forms]![frmRptTotals]![cboCity]" into the criteria like this, it means you want the query to return only rows in which that column matches the contents of the combo box. When the combo box contains just "SFO", it works fine. When the combo box contains "SFO or LAN", the query tries to match that entire 10-byte string to the column. This doesn't match anything, of course, so your report comes up empty.

Another way to say it is that what's in the combo box must be a value, not an expression. You can't use operators in the combo box--only a data string.

One simple way to get what you want is to make a special version of your report's underlying query, changing the criteria to "SFO or LAN". (It'll work there because a criteria cell is supposed to be an expression.) Next make a special version of your report, and just change its underlying query to the new query you just built. Finally, write code to check the form combo box when the user clicks OK to print the report. If it contains "All California", open the special version of the report instead of the normal one.

(This isn't an efficient solution, since you're duplicating the query and report, but it's an easy one. A more efficient one would involve trying to modify the query or the report at run time--I'll leave that as an exercise for the reader.)

 

[ruthcali]

Hi,
thanks for your response. it makes me both happy and sad. Happy, because now i know it doesn't work and i can stop trying different combinations of text:
"SFO" or "LAN"
'SFO' or 'LAN'
SFO "or" LAN , etc.

Happy also because you took the time to help me.

but sad because it doesn't work and i'll have to create a new query for each of the 'combination cities'.

what i will do is create new queries and hard code the combination cities for the critieria. But, i'll still keep just one report. i'll just change the report's recordsource depending on what value the user selects from the combo box.

i just hate hardcoding my queries like that. and what i hate more is creating more queries. my database contains 86 different queries at the moment! I don't want this database to grow so much that it becomes uncontrollable.

thanks again for your advice. i really appreciate it. you saved me from pulling out more of my hair.

Ruth

ruth.jonkman@wcom.com

 

[THoey]

Ruth,

What about the possibility of creating a multi-select list box that the user could use to select the cities they wanted to include. Then, after they have made their selection, you could loop through the ItemsSelected property and build your query on the fly?

Another option is to have a table with all the city codes and their state codes and their region codes. Then, a user could select one or more cities or a whole state or a particular region (probably three different list boxes) and have the query based on that selection.

Hope that gives you some options...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[RickSpr]

Hey, if you're comfortable with changing your report's recordsource, then you don't need the hard-coded queries. You can just stick the SQL statement directly in the RecordSource property!

If you need help coding the SQL statement, just create the hard-coded query--but don't save it. Instead, switch the query to SQL view and just copy the statement Access generates. This then becomes the string constant you load into RecordSource.

 

[ruthcali]

Hi,
thanks for writing.

Terry, i had a question about your statement:
Another option is to have a table with all the city codes and their state codes and their region codes. Then, a user could select one or more cities or a whole state or a particular region (probably three different list boxes) and have the query based on that selection.

I did have a table called tblCities that has two columns: Name and City. Sample data is:

Name City
SFO SFO
LAN LAN
All California SFO or LAN
Mid Atlantic WDC or NYC or NJY
etc.

Then, on my form (frmRptTotals), i have a combo box called cboCity whose row source is tblCities.

Then the query that the report is based on has the following for the critieria for the city field:
[Forms]![frmRptTotals]![cboCity]

But, that was my problem. the query didn't recognize the format 'SFO or LAN'.

My query is pretty big (i copied and pasted it into Word and it took up almost the whole page!). so i don't want to slow down the report any further by writing SQL into the code of the report. I like having the query saved and named.

Is there a way to only have one saved query but still reference the combo box on my form?

thanks,
ruth ruth.jonkman@wcom.com

 

[JerryDennison]

There is a way to directly manipulate the exact records you want returned for your report without altering the recordsource or underlying query. This is the Filter property. The filter property can be set and called within the same statement that calls and opens the report. The filter is a SQL WHERE statement without the word 'WHERE'. It can be built "on the fly" and used to great effect in limiting a report to very specific combinations of records. Terry's suggestion of using a multi-select list box works extremely well in building the SQL filter that can be passed to the report. I like to use an unbound form that allows the user to pick the exact combination of criteria they want to view. As they select one or more items in the listbox or series of comboboxes, code behind each triggers the building of the WHERE claus. It sounds complicated but is actually quite easy and if very effective. I can send you a sample db that illustrates this technique if you'd like.

Send your request via e-mail to: jerrydennison@yahoo.com.

 

[ruthcali]

Using Jerry's idea of the Filter property, i was able to accomplish what i wanted without hard-coding anything!

My tblCities has 2 columns, Name and City
Sample data is:
Name City
BLT 'BLT'
PIT 'PIT'
NJY 'NJY'
WIL 'WIL'
MidAtl 'BLT' or [city]='PIT' or [city]='NJY' or [city]='WIL'

Then, in my form, I have a combo box (cboCity) with a row
source of: SELECT DISTINCTROW tblCities.Name, tblCities.City FROM tblCities;

Then, in my form, I have the following code:

DoCmd.OpenReport stDocName, acPreview, , "[city]=" & Me![cboCity].Column(1)

If Jerry hadn’t suggested using the Filter property, I would have never thought of it.

Thanks,
ruth.jonkman@wcom.com