I'm making a simple form to fill a table. One of the fields is to contain some IDs, either individual, or ranges, like this: "65,73,99-114" with commas and dashes as seperators. I need Access to look up this IDs in another table and provide some data from other columns into the target table. This should be done when a form is submitted. While this task might sound simple and I know a bit of C# and js, it's my first time being challenged by VBA. I've never dealt with such applications and manuals I found are too vague and/or off-topic. Basically, I need to know what to start from and some general tips, but you're welcome to provide any advanced help. p.s. Access 2016
4
-
You could use a zero width, zero height text box for each of the segment's and dlookup on these make it easy, or look at using the split function, this will split by a delimiter to an array.Nathan_Sav– Nathan_Sav2016-02-29 19:59:20 +00:00Commented Feb 29, 2016 at 19:59
-
I need an idea of how to pull data from external table using data from a form.Quinteger– Quinteger2016-02-29 20:01:03 +00:00Commented Feb 29, 2016 at 20:01
-
That's not your question, but the answer is dlookup, and try googling data from tables in forms, bound controls etc.Nathan_Sav– Nathan_Sav2016-02-29 20:40:34 +00:00Commented Feb 29, 2016 at 20:40
-
Write some code and show it to us. You know C# and js, this should be easy.nicomp– nicomp2016-02-29 22:49:07 +00:00Commented Feb 29, 2016 at 22:49
Add a comment
|
1 Answer
There nothing stopping you from writing some code to “parse” out the given text, and create the required SQL. How such code would look in say c#, or VBA is not really the hard part. The real challenge is writing the code to parse out. (writing code! - the approach would be much the same I c#).
I just air coded this, and it looks to work quite well. This code assumes the column in question is a number column (so no quotes is required around each value).
The code looks like this:
Function MyWhereRanges(strRanges, strField As String) As String
' take the raganges, and return a where clause
Dim vTokens As Variant
Dim strRa As String ' sql for ranges
Dim strIn As String ' sql for "in" clause
Dim s As Variant
Dim strResult As String ' return value
vTokens = Split(strRanges, ",")
For Each s In vTokens
If InStr(s, "-") Then
' this is a range - append the range
If strRa <> "" Then
strRa = strRa & " and "
End If
strRa = strRa & "(" & strField & " between " & _
Split(s, "-")(0) & " and " & Split(s, "-")(1) & ")"
Else
' append to the "in" clause
If strIn = "" Then
strIn = "(" & strField & " in ("
Else
strIn = strIn & ","
End If
strIn = strIn & s
End If
Next s
If strIn <> "" Then strIn = strIn & ")) "
strResult = strIn
If strRa <> "" Then
If strResult <> "" Then strResult = strResult & " and "
strResult = strResult & strRa
End If
MyWhereRanges = strResult ' return the value
End Function
So, from the debug window, you can type in this:
? mywhereranges("5,6,12-13,15-25","InvoiceNum")
The result from above is this:
(InvoiceNum in (5,6)) and
(InvoiceNum between 12 and 13) and
(InvoiceNum between 15 and 25)
So, in an actual form, you could launch a report or form like this:
Dim strSQLwhere As String
strSQLwhere = MyWhereRanges(Me.txtBoxRanges, "InvoiceNum")
DoCmd.OpenReport "rptInvoices", acViewPreview, , strSQLwhere
