Query ignores user prompt

[Rik_StHelens]

Hi

I have a query which asks the user to input a value to find a specific record, and when i run the query, access does not prompt the user to enter a value, and just returns all records which do not have a null value in the said field.

Is there a reason as to why it might do this?

 

[Galaxiom]

Your query is not what you expect. It doesn't prompt for the value because it doesn't ask for it.

Post the sql of the query.

 

[Rik_StHelens]

SELECT AllCasingJobs.CUSTOMER, AllCasingJobs.INVNUM, AllCasingJobs.[CAN Number], AllCasingJobs.DELIVERY, AllCasingJobs.INVDATE, AllCasingJobs.Destination, AllCasingJobs.REGNUM, AllCasingJobs.POSITION, AllCasingJobs.TREADM, AllCasingJobs.SERIALON, AllCasingJobs.SERIALOFF, AllCasingJobs.SECTION, AllCasingJobs.PROFILE, AllCasingJobs.RIM, AllCasingJobs.SIZE, AllCasingJobs.[New or Rems], AllCasingJobs.STCODE, AllCasingJobs.Brand, AllCasingJobs.[Removal Description], AllCasingJobs.DESCRIPN, AllCasingJobs.[Post Code], AllCasingJobs.OLDADVNUM, AllCasingJobs.[Retreader Agent], AllCasingJobs.SUMCODE, AllCasingJobs.NAME1, AllCasingJobs.VAN, AllCasingJobs.Status
FROM AllCasingJobs
WHERE (((AllCasingJobs.[CAN Number])=[Enter CAN number]));



Hope this helps!

Thanks for your time =]

 

[Galaxiom]

Your query looks fine and my only suggestion seems vanishingly unlikely.
I have thought hard about this and the only scenario I can imagine that fits the symptoms is a field in [AllCasingJobs] named [Enter CAN Number].

Warned you it was unlikely.

Sorry I can't do better. Please do post back when you work it out.

 

[DCrake]

PARAMETERS [Enter CAN number] Text ( 10 );
SELECT CUSTOMER, INVNUM, [CAN Number], DELIVERY, INVDATE, Destination, REGNUM, POSITION, TREADM, SERIALON, SERIALOFF, SECTION, PROFILE, RIM, [SIZE], [New or Rems], STCODE, Brand, [Removal Description], DESCRIPN, [Post Code], OLDADVNUM, [Retreader Agent], SUMCODE, NAME1, VAN, Status
FROM AllCasingJobs
WHERE [CAN Number]=[Enter CAN number];

Try this sql in your query

Warning:
The word Size is an Access reserved word (need to change or suround with [])

Tip:
If using only 1 table in query you do not need to keep referring to the table for each field.

David

 

[namliam]

Alternatively you could try changing the [enter can number] to [Please enter the can number in this stupid annoying ugly popup] and see if that works, to prevent the column from existing.

Alternatively make a "proper" search form to make things much nicer and less annoying/ugly

 

[Rik_StHelens]

Thanks for all your help

I will try it out when i get back to work and let you know if anything works out....

 

[Rik_StHelens]

Many thanks for your help,

DCrake's solution was just what i needed.

Thanks a lot.

 

[Galaxiom]

Was the problem the reserved word, Size?
Or did the parameter have to be explicit?
If the latter, why did the query not accept the implicit parameter.

 

[Rik_StHelens]

Was the problem the reserved word, Size?
Or did the parameter have to be explicit?
If the latter, why did the query not accept the implicit parameter.

No it wasnt a reserved word because i have queries pulling the same data but with different parameters.

And im really not sure why it wouldnt accept the implicit parameter, but once i declared it at the top of the sql it was fine.

although now i have another query which is asking for a parameter which is not even in the query design/sql. god knows why

 

[DCrake]

Is this new query based on an underlying query? Have you done any traceability?

David

 

[Rik_StHelens]

No it is just based on a single table.

I just copied and pasted a query which used identical fields, and removed the old parameter and added a new one to the field required. I've checked the sql and there was nothing abnormal in there compared with my other queries

 

[DCrake]

Access is a funny bugger at times... WYSIWYG is not always true, I'll bet if you recreate the query in stages, it will go away.


How did you create the new query? Have you actually removed the parameter from the parameters list in the query properties? Removing it from the SQL pane does not always remove it from the parameters list.

David

 

[Rik_StHelens]

Yep i removed it from both the sql and the parameters.

 

[DCrake]

Is it asking for the same parameter?
If you have a query that has computated fields and there is a spelling error or the field that is referenced does not appear in the underlying query Access will ask you to provide a value. Check these if they exist.

David

 

[Rik_StHelens]

No thats not the problem either

hmmm

 

[namliam]

Have you got a unquoted string someplace that you are trying to search for?

Select..... where lastname = namliam

will prompt for the parameter namliam while I should actually do:
Select..... where lastname = "namliam"

 

[Rik_StHelens]

Have you got a unquoted string someplace that you are trying to search for?

Select..... where lastname = namliam

will prompt for the parameter namliam while I should actually do:
Select..... where lastname = "namliam"

nope.

unfortunately not.

 

[Galaxiom]

... now i have another query which is asking for a parameter which is not even in the query design/sql.

I have had this problem more than once. It happened after changing the name of a table or field. Even though the autocorrect carried the change through to the query design and sql, Access kept looking for the old name.

I solved it each time by copying the sql into a new query.

Clearly what is actully run by the engine is neither the query design or the sql but some internal representation of the query. Apparently some glitch means this sometimes doesn't update when changes are made in the design or sql by the autocorrect.