criteria in a report's underlying query

[Robway]

I have a report "Provenance" based on a multi-table query. It sorts all the names alphabetically and includes a lot of descriptive material for each provenance. I'd like to print each group (the As, Bs, Cs etc) as separate reports, but not have to go in and put a criteria in the query for each one. If I could get a little box to pop up that says "Enter first letter of name" when I open the report it would be perfect, but I don't know how to write the expression to put in the query.

Help please. Cheers, Robyn

 

[scriverb]

Here is an example SQL for a query to do what you ask:
Select A.*
FROM tblYourTableName as A
WHERE (((A.YourFieldName) Like [Enter first letter of name] & "*&quot);

Just update the red table and field name designators in the sql and this should work as you requested.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Robway]

Thanks Bob,

I'm afraid I'm not very expert at this and have never worked with SQL. Do I add this to the SQL version of the existing query? I normally work in design view for these things and add very simple criteria.

Cheers, Robyn

 

[scriverb]

Robyn: Just add this to the criteria row in the column with the name:

Like [Enter first letter of name] & "*"

Let me know how this works for you.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Robway]

Hi Bob,

It works like a dream! Exactly what I needed. Thank you very much. I had fiddled and fiddled with things like this, but couldn't get it just right. This will save me heaps of time. Thanks again.

Robyn
Melbourne, Oz

 

[scriverb]

Robyn, it was a please helping you with this problem. Good luck with your project.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???