microsoft access problem

  widdy 10:03 12 Jan 2008

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.
like (Enter Surname) & "*"
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 2008

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 2008

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 2008

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 2008

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 2008

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 2008

OK, now I understand, could it be:

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


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 2008

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.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Converse's new logo: the trainer brand looks to its heritage for a fresh identity

Mac power user tips and hidden tricks

Comment lancer Windows 10 en mode sans ├ęchec ?