-
Jun 22nd, 2017, 01:31 PM
#1
Thread Starter
Lively Member
How to setup an ADO Filter when data has embedded hyphen?
Hi,
I'm trying to setup an ADO Filter for multiple
parameters but one of my data parameters
has an embedded hyphen - e.g. May'17.
How can I set up a filter for this condition?
Is SQL my best alternative?
Thanks for any help provided.
-
Jun 23rd, 2017, 07:44 AM
#2
Re: How to setup an ADO Filter when data has embedded hyphen?
Ee
Do you mean the apostrophe in May'17?
If so, what is the nature of your problem?
If so, how would you set the filter if there was no apostrophe?
Spoo
Last edited by Spooman; Jun 23rd, 2017 at 07:48 AM.
-
Jun 23rd, 2017, 03:41 PM
#3
Thread Starter
Lively Member
Re: How to setup an ADO Filter when data has embedded hyphen?
for a single parameter filter, one could use a 'Find':
Code:
obfRecords.MoveFirst
obfRecords.Find "[Supplier_ID]= '" & supplier.Value & "'"
If obfRecords.BOF And obfRecords.EOF Then
(not in list)
Else
(in list)
End If
using a filter:
Code:
obfRecords.MoveFirst
obfRecords.Filter "[Supplier_ID]= " & supplier.Value
If obfRecords.BOF And obfRecords.EOF Then
(not in list)
Else
(in list)
End If
'Find' doesn't provide allowance for multiple parameters while 'Filter' (apparently) does
however, the 'Filter' doesn't seem to work when I try something like:
Code:
obfRecords.MoveFirst
obfRecords.Filter "[Supplier_ID]= " & supplier.Value & " AND Product_ID= " & nextSkuRow.Cells(, 3).Value
If obfRecords.BOF And obfRecords.EOF Then
(not in list)
Else
(in list)
End If
-
Jun 24th, 2017, 08:34 AM
#4
Re: How to setup an ADO Filter when data has embedded hyphen?
Ee
Let me see if I understand your 3 cases
Code:
obfRecords.Find "[Supplier_ID]= '" & supplier.Value & "'"
obfRecords.Filter "[Supplier_ID]= " & supplier.Value
obfRecords.Filter "[Supplier_ID]= " & supplier.Value & " AND Product_ID= " & nextSkuRow.Cells(, 3).Value
Let's assume ..
supplier.Value is a string such as H2125
nextSkuRow.Cells(, 3).Value is an integer such as 22
Then, the string being created for Find or Filter would be as follows:
case 1. obfRecords.Find .. [Supplier_ID]= 'H2125'
case 2. obfRecords.Filter .. [Supplier_ID]= H2125
case 3. obfRecords.Filter .. [Supplier_ID]= H2125 AND Product_ID= 22
Does that match your expectations?
Two other thoughts:
1. Looks like a parameter is missing here, before the comma: nextSkuRow.Cells(, 3).Value
2. Looks like you are missing the brackets here: AND Product_ID= 22
Spoo
Last edited by Spooman; Jun 24th, 2017 at 08:40 AM.
-
Jun 24th, 2017, 11:31 AM
#5
Re: How to setup an ADO Filter when data has embedded hyphen?
@spoo
to handle quotes I made this Function and this take care of them...
Code:
Public Function strStr(ByVal sString As String) As String
Dim s As String
If Len(sString) = 0 Then
strStr = "NULL"
Exit Function
End If
'entfernt doppelte Hochkommata ''
s = sString
Do While InStr(1, s, Chr(39) & Chr(39)) > 0
s = Replace(s, Chr(39) & Chr(39), Chr(39))
Loop
'Hochkommata doppeln
s = Replace(s, Chr(39), Chr(39) & Chr(39))
'entfernt doppelte DoppelHochkommata "
Do While InStr(1, s, Chr(34) & Chr(34)) > 0
s = Replace(s, Chr(34) & Chr(34), Chr(34))
Loop
'Hochkommata doppeln
s = Replace(s, Chr(34), Chr(34) & Chr(34))
'Zeichenkette in Hochkommata einschliessen
strStr = Chr(39) & s & Chr(39)
End Function
in the SQL you would use it like this...
Code:
sSQL = "INSERT INTO "
sSQL = sSQL & "tbl_Adressen ("
sSQL = sSQL & "AN_Vorname, "
sSQL = sSQL & "AN_Nachname, "
sSQL = sSQL & "AN_Angelegt) "
sSQL = sSQL & " VALUES ("
sSQL = sSQL & "" & strStr(.AN_Vorname) & ", "
sSQL = sSQL & "" & strStr(.AN_Nachname) & ", "
sSQL = sSQL & "" & strStr(.An_Angelegt) & ")"
regards
Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|