microsoft access problem

  widdy 10:03 12 Jan 08
Locked

I hava an access database with about 3000 names and details on and all queries are accessed via a main switchboard and the query is written so that i only need to enter the first few letters of the surname and firt few lettters of the first name of the club member.
i.e.
like (Enter Surname) & "*"
and
like (Enter First Name) & "*"

This works ok for a while and then eventually loops and asks me a second time, so I have to enter it again.

I correct this by rewriting the query and saving it again. I find this most irritating.

Any help would be much appreciated.

  silverous 22:00 12 Jan 08

Is that the exact syntax you use? Doesn't look right.

I'd expect the prompts to be in square brackets?

I'm guessing you get the syntax right otherwise I guess it would never work.

I'm not clear what you mean by works for a while, loops, and enter it again.

You mean you have to set up the whole query again or you have to enter the name again? Any query prompt you enter a value for isn't designed to be stored forever, just until the next time you run the query in my experience.

I have found that sometimes if you put a filter on in access you think you've filtered it, or you don't click off the filter before saving and it gets lost, but something built into a query should not be being lost if you are saying you have to re-add the "like" to the query?

Let me know and I'll help as much as I can.

  widdy 08:48 13 Jan 08

Thanks for your response.
Using the two criteria as written will produce the result I require several times.
i.e. if I am looking for a member with the name John Smith
I type in against the prompt that asks for Surname SM and against the prompt that asks for first name Jo and the database reurns all those whose names begin with Jo and SM.

after working fine for several uses, it asks for surname and first name again before responding with any names.

If I then rewrite part of the query i.e remove the wildcard part, save it, then put it in again and save it. The whole query works fine for a while.

I have tried with square brackets as well.

I hope this is clear

  silverous 12:54 13 Jan 08

That clarifies a bit, are you just opening the queries up direct from the Access "Queries" area when you do this?

That's the bit that confused me i.e. what exactly you are looking at in access and how you are doing the "several uses", I set up an example and EVERY time I go into the query it prompts me, as I would expect it to. Maybe your query is on a form or something? I still think I'm missing something as my understanding is that when a prompt is on a query, each execution of the query will cause a prompt - you seem to be wanting it NOT to do that which makes me think you are looking at something different.

If you can let me know, hopefully we can crack it.

  silverous 12:58 13 Jan 08

OR, when you say it asks for them "again" do you mean it prompts twice in succession in one execution of the query and does not return any results until you've put the details in twice ? i.e. you enter the details but before you get any results from that entry you get prompted a second time for the same details so you are effectively putting them in twice before getting any data back at all?

That's the bit I'm not clear on also.

  widdy 16:21 13 Jan 08

yes thats correct
I enter the details but before I get any results from that entry I get prompted a second time for the same details so I am effectively putting them in twice before getting any data back at all?

  silverous 17:14 13 Jan 08

OK, now I understand, could it be:

click here
(Access 2002 but link to 2000 in there also)

OR

click here

(I don't think it is the latter but posted it to save time if first is no good)

OK, so if it is the former it means you applied a sort in datasheet view rather than putting the sort into the design view properties of the query I think? Seems to be a workaround/fix suggested.

Let me know!

  widdy 18:08 13 Jan 08

Thanks for that, It sounds a bit confusing but will work on it and let you know. it might take a couple of days before i get round to it as work and decorating interferes. thanks again

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now