search form not giving desired results

V

vandy

Hi All,

I am using a search form with text values. when i enter a text string and
hit the search button values corresponding to the selection would be queried
and displayed. This search form works for all the values except for Stock no .
I have a feild called stock no - Input mask "SN"99999 type text
for some strange reason some of the stock numbers are stored as SN223345
and some as 22222 (for SN22222)

when i search for SN223345 in the text box it gives the corresponding value
but in the second case if i search for SN22222 it does not return any value
but if i search for 222222 it returns the result. Any idea why it is behaving
this way.

I checked for default value it is not set right now.

I have 2 tables tblprojects and tblitems from which i get values. It works
fine for all other feilds except for Stock no.

This is my code:

SELECT tblprojects.Pno, tblprojects.Pname, tblitems.Itemno,
tblitems.ItemDesc, tblitems.PoMpino, tblitems.Stock_no,
tbltransactions.Location
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
WHERE (((tblprojects.Pno) Like [forms]![searchform].[qpno] & "*") AND
((tblprojects.Pname) Like [forms]![searchform].[qpname] & "*") AND
((tblitems.Itemno) Like [forms]![searchform]![qitemno] & "*") AND
((tblitems.ItemDesc) Like [forms]![searchform].[qitemdesc] & "*") AND
((tblitems.PoMpino) Like [forms]![searchform].[qpompi] & "*") AND
((tblitems.Stock_no) Like [forms]![searchform]![qstockno] & "*") AND
((tbltransactions.Location) Like [forms]![searchform].[qloc] & "*"));

any help will be greatly appreciated.

thanks
 
O

Ofer Cohen

If there are cases when the SN apear, and sometimes it doesn't, try replacing
the SN with a wild card, that way both cases will apear

Something like

SELECT tblprojects.Pno, tblprojects.Pname, tblitems.Itemno,
tblitems.ItemDesc, tblitems.PoMpino, tblitems.Stock_no,
tbltransactions.Location
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
WHERE (((tblprojects.Pno) Like [forms]![searchform].[qpno] & "*") AND
((tblprojects.Pname) Like [forms]![searchform].[qpname] & "*") AND
((tblitems.Itemno) Like [forms]![searchform]![qitemno] & "*") AND
((tblitems.ItemDesc) Like [forms]![searchform].[qitemdesc] & "*") AND
((tblitems.PoMpino) Like [forms]![searchform].[qpompi] & "*") AND
((tblitems.Stock_no) Like Replace([forms]![searchform]![qstockno],"SN","*")
& "*") AND
((tbltransactions.Location) Like [forms]![searchform].[qloc] & "*"));
 
V

vandy

Thanks Ofer,

It works like a charm



Ofer Cohen said:
If there are cases when the SN apear, and sometimes it doesn't, try replacing
the SN with a wild card, that way both cases will apear

Something like

SELECT tblprojects.Pno, tblprojects.Pname, tblitems.Itemno,
tblitems.ItemDesc, tblitems.PoMpino, tblitems.Stock_no,
tbltransactions.Location
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
WHERE (((tblprojects.Pno) Like [forms]![searchform].[qpno] & "*") AND
((tblprojects.Pname) Like [forms]![searchform].[qpname] & "*") AND
((tblitems.Itemno) Like [forms]![searchform]![qitemno] & "*") AND
((tblitems.ItemDesc) Like [forms]![searchform].[qitemdesc] & "*") AND
((tblitems.PoMpino) Like [forms]![searchform].[qpompi] & "*") AND
((tblitems.Stock_no) Like Replace([forms]![searchform]![qstockno],"SN","*")
& "*") AND
((tbltransactions.Location) Like [forms]![searchform].[qloc] & "*"));

--
Good Luck
BS"D


vandy said:
Hi All,

I am using a search form with text values. when i enter a text string and
hit the search button values corresponding to the selection would be queried
and displayed. This search form works for all the values except for Stock no .
I have a feild called stock no - Input mask "SN"99999 type text
for some strange reason some of the stock numbers are stored as SN223345
and some as 22222 (for SN22222)

when i search for SN223345 in the text box it gives the corresponding value
but in the second case if i search for SN22222 it does not return any value
but if i search for 222222 it returns the result. Any idea why it is behaving
this way.

I checked for default value it is not set right now.

I have 2 tables tblprojects and tblitems from which i get values. It works
fine for all other feilds except for Stock no.

This is my code:

SELECT tblprojects.Pno, tblprojects.Pname, tblitems.Itemno,
tblitems.ItemDesc, tblitems.PoMpino, tblitems.Stock_no,
tbltransactions.Location
FROM tblprojects RIGHT JOIN (tblitems INNER JOIN tbltransactions ON
tblitems.ItemID = tbltransactions.TranItemID) ON tblprojects.ProjectID =
tbltransactions.PID
WHERE (((tblprojects.Pno) Like [forms]![searchform].[qpno] & "*") AND
((tblprojects.Pname) Like [forms]![searchform].[qpname] & "*") AND
((tblitems.Itemno) Like [forms]![searchform]![qitemno] & "*") AND
((tblitems.ItemDesc) Like [forms]![searchform].[qitemdesc] & "*") AND
((tblitems.PoMpino) Like [forms]![searchform].[qpompi] & "*") AND
((tblitems.Stock_no) Like [forms]![searchform]![qstockno] & "*") AND
((tbltransactions.Location) Like [forms]![searchform].[qloc] & "*"));

any help will be greatly appreciated.

thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

check box query 1
Display Search Results in Form 1
Need help with a Search Form and code 2
Search Form Question 0
search form not working properly 1
search form not to load all data up to start with 1
Form can not display search results 0
Compound Search Criteria Using a Form 1

Top