MS Access 2003 query problem

  SDJ 15:37 01 Apr 2004

Two questions re the above:-

1. I am trying to list codes in the criteria of the design view, it only allows me to list down nine entries before I have to exit re-open, it then puts all the listed codes in the first row putting in the respective ORs and "" marks.
Is it possible to be able to list more than nine at a time. the reason is the persons using this do not know how to do the formula and listing the numbers is all they are capable of doing.

2. I have two machines doing the above task, one is listing all of the codes in the first row after exiting and re-opening, the other is retaining them in a list format down the screen. When exiting and re-opening they are still listed down the screen, is there a setting somewhere preventing this.

Hope this makes sence, its been a long day and I have a couple of people nagging me.


  Sir Radfordin 15:49 01 Apr 2004

1) No don't think so but you could create a form tha they could use to create the query from.

Need to know a bit more about what you are trying to do that has so many variables to help.

2) Can't see anything in XP that would change this setting and my trial of 2003 ran out yesterday...sorry!

  SDJ 19:38 01 Apr 2004

1. I have a table which is linked bringing in data from an external source. I have then created a simply query.
The data required is as follows:-
Client Number, Client Name, Address 1, Address 2, etc etc.
Each senior gets a client list provided by the external program and we indicate whether to send a certain letter to the client.

Due to the external program not having various functions, getting the names and addresses into a word document has to be done through access.

I have told them to list the codes (from which we have indicated) within the design view so as that the query will only select said codes. This can then be easily mail-merged into word.

Hope this makes sence.

  Sir Radfordin 20:15 01 Apr 2004

Still not entirely sure what you have to do but here goes...first a couple of questions:

1) Does the query (qr_1) select data from the link table (tl_link)?

2) Where does this list of client numbers come from, and does it change?

3) Does your query say something like Select [Fields] from [tl_link] where [Client Number]= List of Client Numbers?

If so I'm doing well :)

Two ways of doing this. One is to create another table [tl_clientselect] with one row [clientnumber] of same type as Client Number in [tl_link]

Then in your query in the Criteria Box type:


When you run the query it will only include the rows where the client number is in table [tl_clientselect]. How you populate that table is up to you.

Thats the first way of doing things. The second would be to create a relationship in the query between [tl_link] and [tl_clientselect] on [client number]. You do this by putting both tables into the query and clicking to select [tl_link]![client number] and drag until you are over [tl_clientselect]![client number]. By default your join type will mean the query will only return rows from [tl_link] where there is a corrseponding value in [tl_clientselect]

Hope that helps. Post back if you (or I!) don't understand the problem.

If needs be I can give you an address to send a copy of the DB to me and I'll have a look at it. Click the yellow envelope if you want.

Off out now but will return late tonight!

  SDJ 23:43 01 Apr 2004

I had originally setup something similar, however I had trouble with the formatting, but I think this was because I got them to list the client numbers in Excel, imported the file into Access and created the relationship between the client numbers of the main source and the excel imported table. The result was that I was getting an error, I cant remeber what it said exactly but it equated to something along the lines of the two tables not wanting to talk to one another.

Maybe I will show them how to create the list by creating a table in access, ensuring that the properties of the client code column in each table are the same.

I must confess, when doing what I did the other day, it was a quick fix and didnt have any trouble until I advised someone in another one of our offices to do the same thing.

The database is at work so I will try in the morning, thanks for your help and I will post back tomorrow.


  Sir Radfordin 00:08 02 Apr 2004

The error you would have got would have been because when it imported the data I'm fairly sure it gets imported as Text. You would then get a data mismatch error message when you tried to create a relationship.

The other option would be to create a form with a text box and tell them to type in "Code1","Code2","Code3" "CodeN"...

And then use that field as the criteria on the query. You would have to launch the query from that form for this method to work.

  SDJ 11:13 02 Apr 2004

Came into work this morning and created another table for them to list the relevant codes as required.
setup the relationship accordingly and it now works.

Thanks for your help, most appreciated.

  Sir Radfordin 11:21 02 Apr 2004

Another happy customer thats what I like to see :)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

WWDC history: Apple's product launches since 2005

Comment importer des contacts d’un iPhone à un autre iPhone ?