dynamic query question

[mukund9876]

Hi,

I have the following query:

SELECT *
FROM qryForCreateReportSold
WHERE (((qryForCreateReportSold.Fund) Like '*STP*') AND ((qryForCreateReportSold.[Sale Date])>=#7/1/2005# And (qryForCreateReportSold.[Sale Date])<=#6/14/2006#));

The user fills out a form and hits run query and the value STP gets passed into the query criteria.

My situation is that there are claaifications such as S/STP, E/STP etc and the problem is all these projects also show up whereas I want projects ONLY

under the STP classification.

I guess the problem is due to the fact of the *STP* which means pick all records that have an STP in them...

If I manually get into the query and remove the * * then the projects ONLY with STP show up.( which is exactly what I need)

Can some one suggest something to remedy the situation.

Thanks

 

[pbaldy]

Perhaps I'm dense, but you've said that removing the wildcards gives you exactly what you want, so remove the wildcards.

 

[mukund9876]

dynamic query value passing

I can remove the wildcards manually, but the query gets the value from the form when the user chooses STP from the drop down list...This value STP is passed from the form to the query and for some reason the value is passed as *STP*

How do I tell accesss to get rid of the * and just pass 'STP' to the query?

Thanks in advance.

 

[workmad3]

You probably need to fiddle around with the VBA code then. At a guess the person who created the form put it into the code that the values from the combobox should be wildcard comparisons, so the code puts the * * around the value before passing it to the query.

 

[mukund9876]

dynamic query

Hi,

Thanks for your response workmad. I thought ur suggestion fixed it but the user just reported another problem.

The funding classifications in my DB are as under:

STP
CMAQ
E-STP
S-STP

Some projects have a combination of funding from STP and CMAQ, hence I suppose the previous coder had the * while passing the value from the form to the query, but now what is happening is :

1. All projects that have a STP in them show up , so projects such as E-STP also show when they should not.( I just want to show projects which are STP funded by since E-STP has an "STP" in it, I guess Access picks it too.)

AND

2. when I remove the * projects that are partly funded by STP example say STP and CMAQ - do NOT show up.

Any suggesions will be highly appreciated.

Thanks in advance.