SQL statement with LIKE need to show Numeric paceholders

[Billp]

I have a need to find 4 digit or 5 digit numbers.

strAppend = "INSERT INTO PurchasingTable ( PurchaseOrderNumber, WorksNo,
Code, PO_Sum )" _
& " SELECT [OrderNumbers Query1].PurchaseOrderNumber, [OrderNumbers
Query1].WorksNo, [OrderNumbers Query1].Code, Sum([OrderNumbers
Query1].[SumOfSum Of ExtPrice]) AS PO_Sum" _
& " From [OrderNumbers Query1]" _
& " GROUP BY [OrderNumbers Query1].PurchaseOrderNumber, [OrderNumbers
Query1].WorksNo, [OrderNumbers Query1].Code" _
& " HAVING ((([OrderNumbers Query1].WorksNo) Like '%____%' Or ([OrderNumbers
Query1].WorksNo) Like '%_____%') And ((Sum([OrderNumbers Query1].[SumOfSum Of
ExtPrice])) >= 0))" _
& " ORDER BY [OrderNumbers Query1].PurchaseOrderNumber;"

Like #### or like ##### which are placeholders but do not work in the sql
statement.

Thanks for your help

 

[John Spencer]

Since you seem to be using ANSI compliant SQL try the following to find 4 or 5
consecutive numbers within the string.

WorksNo Like '%[0-9][0-9][0-9][0-9]%' OR WorksNo Like
'%[0-9][0-9][0-9][0-9][0-9]%'

If the string can be exactly a four-digit character string or a five-digit
character string the you can remove the leading and trailing "%" wildcards.

Also as Marshall mentioned it would be better to move this part of your
criteria into a where Clause. And since this is an INSERT query it makes
almost no sense to have an order by clause.

strAppend = "INSERT INTO PurchasingTable ( PurchaseOrderNumber, WorksNo,
Code, PO_Sum )" _
& " SELECT [OrderNumbers Query1].PurchaseOrderNumber, [OrderNumbers
Query1].WorksNo, [OrderNumbers Query1].Code, Sum([OrderNumbers
Query1].[SumOfSum Of ExtPrice]) AS PO_Sum" _
& " From [OrderNumbers Query1]" _
& " WHERE WorksNo Like '%[0-9][0-9][0-9][0-9]%' " _
& " OR WorksNo Like '%[0-9][0-9][0-9][0-9][0-9]%'" _
& " GROUP BY [OrderNumbers Query1].PurchaseOrderNumber, [OrderNumbers
Query1].WorksNo, [OrderNumbers Query1].Code" _
& " HAVING Sum([OrderNumbers Query1].[SumOfSum Of ExtPrice] >= 0" _
& " ORDER BY [OrderNumbers Query1].PurchaseOrderNumber;"

Storing derived data like this is usually NOT a good idea. It is almost
always better to calculate the values in a query when needed to ensure
accuracy of the data. Sometimes it is necessary to do what you are doing for
performance reasons or because the underlying data for the calculation is not
retained.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County