CODEWITHSUNDEEP
sqldatabasems-access
jaypabs
jaypabs

Reputation: 1567

How to use dash (-) as value for criteria

I don't know if this is possible in MS Access, but what I want to do is detect dash (-) and use Between in SQL statement and or Comma.

Ex. I have a table called "Books" with fields: BookID, Title, Subject.

Now how can I query Books table that allows a user to input a value in a textbox like:

1 or 1-5 or 1,3,4.

If the value is 1 the sql statement should be:

SELECT * FROM Books WHERE BookID = 1

If the value of 1-5 then the sql statement should be:

SELECT * FROM Books WHERE BookID BETWEEN 1 And 5

If the value of 1,3,4 then the sql statement should be:

SELECT * FROM Books WHERE BookID IN (1,3,4)

Upvotes: 2

Views: 375

Answers (2)

Alex K.
Alex K.

Reputation: 175766

Cut from something I already have;

s = "SELECT * FROM Books WHERE BookID" & parseSarg("11,22,33")

using;

Public Function parseSarg(str As String) As String
    Dim re As Object: Set re = CreateObject("vbscript.regexp")
    re.Global = True
    Select Case True
        '//is number
        Case reTest(re, "^\d+$", str):          parseSarg = " = " & str
        '//is number-number
        Case reTest(re, "^\d+-\d+$", str):      parseSarg = " BETWEEN " & Replace$(str, "-", " AND ")
        '//is number,number[,number]
        Case reTest(re, "^\d+(?:,\d+)*$", str): parseSarg = " IN (" & str & ")"
        '//is >number
        Case reTest(re, "^>\s*\d+$", str):      parseSarg = " >" & Mid$(str, 2)
        Case Else
            parseSarg = " = 0 AND 1=0"
    End Select
End Function

Function reTest(re As Object, pattern As String, value As String) As Boolean
    re.pattern = pattern
    reTest = re.Test(value)
End Function

Upvotes: 3

Ruth Rose
Ruth Rose

Reputation: 1

SELECT Books.Title FROM Books WHERE Books.BookID > 1 AND Books.BookID < 5;

Upvotes: 0

Related Questions