Criteria in a query not working at times (1 Viewer)

[anishkgt]

Hi,

My query table is from another query. so does that preven some fields from displaying when using the criteria as [CODE[Like "*" forms!frmsearch!txtSerialNumber "*"[/CODE] one field works but when the next field criteria is added then the query result goes blank.

So does the search criteria need to be changed as per the data type ? not sure how to change that but am hoping somebody could help. Now with just the word the criteria returns the result. its just the like code does not seem to be working.

Thanks in advance.

 

[arnelgp]

what is the sql of your query?

 

[anishkgt]

I had send you my db. It should be clear

 

[anishkgt]

Its the query qrysearch

 

[arnelgp]

what db? there are lots of db i download. can you upload it here.

 

[anishkgt]

the file is here

 

[anishkgt]

Any luck ?

 

[Minty]

Can you post up the SQL from the query - there are dozens in the database and it would be easier to deal with here.

 

[arnelgp]

try this onehttps://www.dropbox.com/s/djt78vf2q6hqhwh/Inventory_Local(1).zip?dl=0

 

[Minty]

Even with no criteria selected your search form appears to only return 3 records ?

I think you need to look at your query design and alter the joins to left joins as you are restricting your result set by requiring all the fields to have values in the underlying tables - which they don't.

 

[anishkgt]

Yea i was doing it all over again and it was the customer table that needs to be looked into.

 

[anishkgt]

try this onehttps://www.dropbox.com/s/djt78vf2q6hqhwh/Inventory_Local(1).zip?dl=0

Will have a look at it.

 

[anishkgt]

try this onehttps://www.dropbox.com/s/djt78vf2q6hqhwh/Inventory_Local(1).zip?dl=0

That did not work. I've re-designed the query and the SQL string is

SELECT tblDelivery.DelID, tblPCC.AgencyName, tblInventoryType.InventoryType, tblModel.Model, tblinventoryDetail.SerialNumber, tblCapex.Capex
FROM tblCustomer INNER JOIN ((tblCapex INNER JOIN (tblModel INNER JOIN (tblInventoryType INNER JOIN tblinventoryDetail ON tblInventoryType.InvTypeID = tblinventoryDetail.InvTypeID_FK) ON tblModel.ModelID = tblinventoryDetail.ModelID_FK) ON tblCapex.CapexID = tblinventoryDetail.CapexID_FK) INNER JOIN (tblPCC INNER JOIN tblDelivery ON tblPCC.PCCID = tblDelivery.PCCID_FK) ON tblinventoryDetail.InvID = tblDelivery.[InvID-FK]) ON tblCustomer.CustomerID = tblDelivery.CustomerID_FK
WHERE (((tblPCC.AgencyName) Like "*" & [Forms]![qryA1]![AgencyName] & "*") AND ((tblInventoryType.InventoryType) Like "" & [Forms]![qryA1]![InventoryType] & "*") AND ((tblModel.Model) Like "" & [Forms]![qryA1]![Model] & "*") AND ((tblinventoryDetail.SerialNumber)=[Forms]![qryA1]![SerialNumber]) AND ((tblCapex.Capex)=[Forms]![qryA1]![Capex]))
ORDER BY tblDelivery.DelID DESC;

Can't really understand this whole thing, everything works without the criteria but when criteria with a form shows nothing.

 

[The_Doc_Man]

This is just one example, but...

 ( ( tblPCC.AgencyName ) Like "*" & [Forms]![qryA1]![AgencyName] & "*" )

probably should be more like

 ( tblPCC.AgencyName  Like "'*" & [Forms]![qryA1]![AgencyName] & "*'" )

It is possible that other parts of your query are similarly affected.

 

[anishkgt]

Thanks for the reply Doc ! but what does the extra ' do here ? treat that value as a string ?

 

[anishkgt]

Tried that just for that and the others also, but neither worked.

 

[The_Doc_Man]

Yes, the embedded quote makes the LIKE command treat the resultant quoted string a literal string - which it SHOULD be if the [AgencyName] happens to be a string.

Let's look at an isolated part of this query.

If you have ....

... ( X LIKE *field*) AND...."

that gets parsed as an incomplete expression because * is the multiplication operator. So the parser would find EITHER that "LIKE * field" is an expression followed by an * and a parenthesis OR it would find that "LIKE * field" is an expression preceded by a variable X for which there is no operator between X and LIKE. Not to mention that it would also probably kvetch about using a reserved word (LIKE) as a variable name.

On the other hand,

... ( X LIKE '*field*') AND...."

looks like a simple "LIKE" clause with a simple pair of comparands.

Remember that VBA is free-form, so I can write "A*B*C*D" without spaces inbetween the operators and the operands. Thus, cramming the asterisks next to the control reference doesn't change their nature. Further, you can use a control reference in an expression and it will work just fine, particularly if the control is the right data type for that expression. In essence, the tick-marks are there to give the expression parser hints about how you wanted that expression analyzed.

 

[The_Doc_Man]

Sorry for the delay in following up, but my wife was ill for a while. (Better now, thanks!)

As to things not working, the question is whether you have attended to the issues that can arise with use of controls. For instance, if this is based on query with a LEFT or RIGHT join then you could possibly get nulls for some bound controls. Mishandled nulls will hose you to tears if you don't anticipate them.

It is also important to assure that the data type for your control is correctly represented. For instance, if your control is a combo box (with single-selection) then the bound field has to be the right data type even if the selection is made through a different field of a different data type.

There is no substitute for finding every reference to a control in your query and verifying that the answer returned for that control is reasonable. If this is something you are doing in VBA code, for example, you could put a breakpoint just before you try to execute the query. Then you could open an immediate window and do a Debug.Print command on each separate criterion element of the query to see if you are getting back a null or an unexpected data format.

Here, the solution will ALWAYS be of the form "divide and conquer" - take apart the query and find what part is not working. Don't forget that if you have inadvertently included two mutually exclusive criteria and used the AND logical operator, you will return nothing even if ALL of the individual elements are perfectly well-defined. For instance, (and this is a contrived example)

SELECT * FROM TRUCKS WHERE [BodyColor] = 'WHITE' AND [BodyColor] = 'RED' ...

That would never return anything because of the AND joining two mutually-exclusive logical operands. You could expect something if you had used OR in place of the AND, but as written, you should expect nothing.