commas in combo box problem

[PWise]

I have got a similar problem with commas in combo box my table has a id field that has a category letter and a number

f001
f002
f003
s001
s002
s003
j001
j002
j003
g001
g002
g003....
I have to be able to run a report either all or 1 category. what I am trying to do is change my reports recordsource query on my form I created a combobox
2 column
boundcolumn=1
columnwidth=0";3"
rowsource=valuelist
This is what I tried for the rowsource
;-All-;left(id,1)='f';freshman;left(id,1)='s)';sophomore;left(id,1)='j'; junior ;left(id,1)='g'; senior ;
and the rowsource is translating the commas as field delimitaters
I tried left(id & chr(44)& 1)='f'; freshman that does not work either.
This is the code
dim mydb as database
set mydb=currentdb
mydb.querydefs("queryname&quot.sql="select..." & iif(nz(me!combobox,"&quot>""," where" & mecomboboxname,"&quot& ";"
any help out there

 

[KenReay]

HI

Why not do the decent thing and have a table of Id's.

The TAble wouyld have two columns

Id (PrimeKey)
Description

Then you base you combo on an SQL statement so:

SELECT Id, Description FROM tblId
UNION
SELECT &quot;*&quot; as Id, &quot;<All>&quot; As Description FROM tblId
ORDER BY Id

The Combo has two columns (Id, Description, width 0;2in), and the criteria for you report is:

Like Forms!MyForm!cboId

so Like * gives all
Like F gives only those beginning F

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pelegs]

I'd redesign the table. I'd split the ID into to parts, Category and Number. That's what the ID really means. If you need to display something like &quot;f003&quot;, just paste them back together. It is usually easier to paste columns back together on-the-fly when needed than to take them apart. Plus, it is immeasurably more flexible. Try your combo boxes based on this mod to the table. You can then use the table more directly in RowSource.

If you want to have the combo display, for example, &quot;freshman&quot; instead of &quot;f&quot;, then you can just use a query that that calls a function to do the mapping, something like:

Select MapID(ID) as MappedID, Column1, ... ColumnN From Table Order By MappedID

What you were trying to do is a valiant effort, but it is not allowed by Access. The values must be simple scalars, not functions. It would be nice if it would work, but it doesn't. Besides, Value Lists are really intended for relatively small, static lists. There are other ways to do what you want.
Peleg
PelegNOSPAM@PStrauss.net