Removing data in a query.

  • Thread starter Thread starter David
  • Start date Start date
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
 
Hi David,

For some strange reason you can't use the Replace() function in a query
in Access 2000. There seem to be two ways round it.

The official way is to wrap the Replace() function in a custom VBA
function and call that, e.g. something like this untested air code:

Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As Variant
If IsNull(Target) Then
MyReplace = Null
Exit Function
End IF
MyReplace = Replace(CStr(Target), FindWhat, ReplaceWith)
End Function

The other - no guarantees - is to try installing a recent Jet service
pack. Some installations of Access 2000 do seem able to use Replace() in
queries, and the common factor seems to be the Jet service packs. You
can get to a download link from
http://support.microsoft.com/default.aspx?scid=kb;en-us;300216.

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...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Please help me!

What am I doing worg?
I'm getting an error "Wrong nomber of arguments used with
function in query expression 'myReplace([itemname])'"

=====Module1=========
Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As
Variant
If IsNull(Target) Then
MyReplace = Null
Exit Function
End If
MyReplace = Replace(CStr(Target), "-", "")
End Function

==========Query========
NewName: myReplace([itemname])
====.==============

-----Original Message-----
Hi David,

For some strange reason you can't use the Replace() function in a query
in Access 2000. There seem to be two ways round it.

The official way is to wrap the Replace() function in a custom VBA
function and call that, e.g. something like this untested air code:

Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As Variant
If IsNull(Target) Then
MyReplace = Null
Exit Function
End IF
MyReplace = Replace(CStr(Target), FindWhat, ReplaceWith)
End Function
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...
 
Please help me!

What am I doing worg?
I'm getting an error "Wrong nomber of arguments used with
function in query expression 'myReplace([itemname])'"

=====Module1=========
Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As
Variant
If IsNull(Target) Then
MyReplace = Null
Exit Function
End If
MyReplace = Replace(CStr(Target), "-", "")
End Function

==========Query========
NewName: myReplace([itemname])
====.==============

-----Original Message-----
Hi David,

For some strange reason you can't use the Replace() function in a query
in Access 2000. There seem to be two ways round it.

The official way is to wrap the Replace() function in a custom VBA
function and call that, e.g. something like this untested air code:

Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As Variant
If IsNull(Target) Then
MyReplace = Null
Exit Function
End IF
MyReplace = Replace(CStr(Target), FindWhat, ReplaceWith)
End Function

The other - no guarantees - is to try installing a recent Jet service
pack. Some installations of Access 2000 do seem able to use Replace() in
queries, and the common factor seems to be the Jet service packs. You
can get to a download link from
http://support.microsoft.com/default.aspx?scid=kb;en- us;300216.
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...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Comments in line:

What am I doing worg?
I'm getting an error "Wrong nomber of arguments used with
function in query expression 'myReplace([itemname])'"

=====Module1=========
Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As
Variant
Click to expand...
The line above declares the function with three arguments Target,
FindWhat and ReplaceWith.
If IsNull(Target) Then
MyReplace = Null
Exit Function
End If
MyReplace = Replace(CStr(Target), "-", "")
Click to expand...
Here you've substituted the literal values "-" and "" for FindWhat and
ReplaceWith.
End Function

==========Query========
NewName: myReplace([itemname])
Click to expand...
Here you're calling MyReplace with only one argument. This doesn't match
the 3-argument declaration, so Access complains.

Either use the function as I posted it and call it as something like
NewName: MyReplace([itemname], "-", "")
or else change the declaration so it only expects one argument:
Public Function MyReplace(Target As Variant) As Variant
====.==============

-----Original Message-----
Hi David,

For some strange reason you can't use the Replace() function in a query
in Access 2000. There seem to be two ways round it.

The official way is to wrap the Replace() function in a custom VBA
function and call that, e.g. something like this untested air code:

Public Function MyReplace(Target As Variant, _
FindWhat As String, ReplaceWith As String) As Variant
If IsNull(Target) Then
MyReplace = Null
Exit Function
End IF
MyReplace = Replace(CStr(Target), FindWhat, ReplaceWith)
End Function
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...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
Back
Top