Removing data in a query.

D

David

I want to have a query that removes all dashes between the
item code, from field named [item name]
But I don't want to delete the dashes from the table.
See 3 different examples.

Panasonic KX-TCA91 Over the Ear Headset with Mute KXTCA91
Panasonic KX-TCA92 Headset with in-line Volume Control
KXTCA92-A
Sharp MRD-M500 250W x 1 Mono Subwoofer Amplifier MRDM500

I tried this code
Field: NewItemName: Replace ([Item Name] , "-","")
But I'm getting an error "Undefined function 'Replace' in
expression"
I have Access 2000
What is the correct code?

Thanks
 
D

Dirk Goldgar

David said:
I want to have a query that removes all dashes between the
item code, from field named [item name]
But I don't want to delete the dashes from the table.
See 3 different examples.

Panasonic KX-TCA91 Over the Ear Headset with Mute KXTCA91
Panasonic KX-TCA92 Headset with in-line Volume Control
KXTCA92-A
Sharp MRD-M500 250W x 1 Mono Subwoofer Amplifier MRDM500

I tried this code
Field: NewItemName: Replace ([Item Name] , "-","")
But I'm getting an error "Undefined function 'Replace' in
expression"
I have Access 2000
What is the correct code?

Thanks
Click to expand...

It works for my version of Access 2000 (SP3), but this may be one of
those functions that got left out of the Access Expression Service
originally. Verify that the Replace() function works in VBA code or
from the Immediate Window. Assuming it does, write your own wrapper
function, along the lines of:

'---- start of wrapper-function code ----
Function fncReplace(Expression As String, _
Find As String, _
Replace As String, _
Optional ByVal Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

fncReplace = Replace(Expression, Find, Replace, Start, Count,
Compare)

Exit Function
'---- end of wrapper-function code ----

Then call fncReplace in your query instead of Replace.

If your version of Access is less than SP3, maybe applying all service
packs would also fix the problem, sparing you the need to use the
wrapper function.
 
D

Dirk Goldgar

David said:
Is there any code for this event that works in Access 97,
2000, and XP ?
-----Original Message-----
David said:
I want to have a query that removes all dashes between the
item code, from field named [item name]
But I don't want to delete the dashes from the table.
See 3 different examples.

Panasonic KX-TCA91 Over the Ear Headset with Mute KXTCA91
Panasonic KX-TCA92 Headset with in-line Volume Control
KXTCA92-A
Sharp MRD-M500 250W x 1 Mono Subwoofer Amplifier MRDM500

I tried this code
Field: NewItemName: Replace ([Item Name] , "-","")
But I'm getting an error "Undefined function 'Replace' in
expression"
I have Access 2000
What is the correct code?

Thanks
Click to expand...
Click to expand...
Click to expand...

You can write your own replace function, and just use that. I wrote
this for such needs:

'----- start of code -----
Function fncReplace(Expression As String, _
Find As String, _
Replace As String, _
Optional ByVal Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
when
' that function is not available.
'
' Written by: Dirk Goldgar, DataGnostics
' You are free to use this function in your application and to
' redistribute it, so long as the attribution remains unchanged.

Dim strResult As String
Dim lngReplaceCount As Long
Dim lngPos As Long

If Len(Find) > 0 Then
Do
lngPos = InStr(Start, Expression, Find, Compare)
If (lngPos) Then
If Count > -1 Then
lngReplaceCount = lngReplaceCount + 1
If lngReplaceCount > Count Then
Exit Do
End If
End If
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) & _
Replace
Start = lngPos + Len(Find)
End If
Loop Until lngPos = 0
End If

fncReplace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----
 
D

David

Can anyone help me?
Is this the way I have to write in the query?

NewItemName: fncReplace ( [Products]![ItemName])


-----Original Message-----
I want to have a query that removes all dashes between the
item code, from field named [item name]
But I don't want to delete the dashes from the table.
See 3 different examples.

Panasonic KX-TCA91 Over the Ear Headset with Mute KXTCA91
Panasonic KX-TCA92 Headset with in-line Volume Control
KXTCA92-A
Sharp MRD-M500 250W x 1 Mono Subwoofer Amplifier
Click to expand...
Click to expand...
MRDM500

You can write your own replace function, and just use that. I wrote
this for such needs:

'----- start of code -----
Function fncReplace(Expression As String, _
Find As String, _
Replace As String, _
Optional ByVal Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
when
' that function is not available.
'
' Written by: Dirk Goldgar, DataGnostics
' You are free to use this function in your application and to
' redistribute it, so long as the attribution remains unchanged.

Dim strResult As String
Dim lngReplaceCount As Long
Dim lngPos As Long

If Len(Find) > 0 Then
Do
lngPos = InStr(Start, Expression, Find, Compare)
If (lngPos) Then
If Count > -1 Then
lngReplaceCount = lngReplaceCount + 1
If lngReplaceCount > Count Then
Exit Do
End If
End If
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) & _
Replace
Start = lngPos + Len(Find)
End If
Loop Until lngPos = 0
End If

fncReplace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
D

Dirk Goldgar

David said:
Can anyone help me?
Is this the way I have to write in the query?

NewItemName: fncReplace ( [Products]![ItemName])


-----Original Message-----
I want to have a query that removes all dashes between the
item code, from field named [item name]
But I don't want to delete the dashes from the table.
See 3 different examples.

Panasonic KX-TCA91 Over the Ear Headset with Mute KXTCA91
Panasonic KX-TCA92 Headset with in-line Volume Control
KXTCA92-A
Sharp MRD-M500 250W x 1 Mono Subwoofer Amplifier MRDM500
Thanks
Click to expand...

You can write your own replace function, and just use that. I wrote
this for such needs:

'----- start of code -----
Function fncReplace(Expression As String, _
Find As String, _
Replace As String, _
Optional ByVal Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
when
' that function is not available.
'
' Written by: Dirk Goldgar, DataGnostics
' You are free to use this function in your application and to
' redistribute it, so long as the attribution remains unchanged.

Dim strResult As String
Dim lngReplaceCount As Long
Dim lngPos As Long

If Len(Find) > 0 Then
Do
lngPos = InStr(Start, Expression, Find, Compare)
If (lngPos) Then
If Count > -1 Then
lngReplaceCount = lngReplaceCount + 1
If lngReplaceCount > Count Then
Exit Do
End If
End If
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) &
_ Replace
Start = lngPos + Len(Find)
End If
Loop Until lngPos = 0
End If

fncReplace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----
Click to expand...
Click to expand...

Assuming you've fixed the one line-wrap problem in the posted function,
and saved it in a standard module (not named the same as the function),
then you'd define the calculated field in your query like this:

NewItemName: fncReplace([Item Name] , "-", "")

If [Item Name] may be Null, though, I think you may have rewrite that
like this:

NewItemName: IIf([Item Name] Is Null, Null, fncReplace([Item Name] ,
"-", ""))

Watch for line wrap in the above. It was entered on one line.
 
Top