stripping spaces out of a string field (1 Viewer)

atrium

Silver Supporter
Joined
May 13, 2014
Messages
205
I have a query as the source of a search program.
I want all the different phone numbers to be stripped of their spaces.

I have put in the query ClientPhone: Replace(([Clients]![Phone])," ","")

When I run it I get a "Date Type mismatch in criteria expression" error

The phone number is a short text field.

Any help would be appreciated

Regards
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
Hi. Also, you could try it this way.
Code:
ClientPhone: Replace(Nz([Clients]![Phone]," ")," ","")
Hope that helps...
 

atrium

Silver Supporter
Joined
May 13, 2014
Messages
205
The exclamation Mark changed to a dot made no difference.

Inserting the NZ worked

Thank you very much guys

Especially to the DBGuy
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
The exclamation Mark changed to a dot made no difference.

Inserting the NZ worked

Thank you very much guys

Especially to the DBGuy
Hi. You're welcome. Glad to hear it worked for you. We were all happy to assist. Good luck with your project.
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,802
Do you know why it made a difference for next time?
Replace function on a null value generates an error. Thus the source data type wasn't the issue. It was the source data (or lack of it). When used in a query like this, one null value in a record is enough to break the whole thing.
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,583
Do you know why it made a difference for next time?
I gave thanks because I didn't know what else to do to highlight and upvote your comment. That question should be answered anytime a solution is offered.

The explanation to solution ratio on this site is getting woefully low. Need to get it closer to 1.
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,802
Thanks, plog. That's the teacher in me coming out!:)
I didn't know what else to do to highlight and upvote your comment
There's always 'reputation' points. Not saying that for me and my case as much as pointing that out for everyone else in the future.
I've notice a lot of signatures make a point of suggesting that but it isn't my style to do so. Points are not why I'm here.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom