2 query questions

[KenshiroUK]

Hi guys, I have a 2 questions on a couple of queries I have built. The first is on total quantity on products. For example we send 2 products of X to a company and then send another 3 products of Y to the company which would be 5 in total. I wish to set up a quantity total to invoice them.

My second question is I have a list of Inventory that uses a Sku, with many different codes, at the moment I have to add in the exact Sku in order to use the select query, is there anyway I can search for the nearest identical number(s)?

 

[llkhoutx]

Sum the quantity in the invoice (report) footer.

What do you mean by "nearest identical"? It has no exact meaning.

 

[KenshiroUK]

Sum the quantity in the invoice (report) footer.

What do you mean by "nearest identical"? It has no exact meaning.

For example if we have a code of ZAALEX2234, if I typed in ZAALEX it would return all the ZAALEX codes starting with that. If I typed in 2234, it would return all the codes with that number in the field.

 

[pr2-eugin]

Have you used LIKEhttp://www.techonthenet.com/sql/like.php operator?? However this is not recommended it will return what you want..

Apart from the Fact that the LIKE operator discards any indexing put on the table, it might not always return you the exact result..

WHERE theField LIKE "ZAALEX*"

would return anything that starts with ZAALEX..

WHERE theField LIKE "*2234"

would return anything that ends with 2234..
Normally if you take the data fed into the query via a Form then you would have..

WHERE theField LIKE "*" & Form!formName!controlName & "*"