stripping spaces out of a string field (1 Viewer)

atrium

Registered User.
Local time
Tomorrow, 00:24
Joined
May 13, 2014
Messages
348
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
 

sonic8

AWF VIP
Local time
Today, 14:24
Joined
Oct 27, 2015
Messages
998
I have put in the query ClientPhone: Replace(([Clients]![Phone])," ","")
Replace the exclamation mark with a dot.


ClientPhone: Replace(([Clients].[Phone])," ","")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:24
Joined
Oct 29, 2018
Messages
21,357
Hi. Also, you could try it this way.
Code:
ClientPhone: Replace(Nz([Clients]![Phone]," ")," ","")
Hope that helps...
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:24
Joined
Jan 20, 2009
Messages
12,849
Replace the exclamation mark with a dot.
ClientPhone: Replace(([Clients].[Phone])," ","")

Access has been fine with table!field since at least Access 2010.
 

atrium

Registered User.
Local time
Tomorrow, 00:24
Joined
May 13, 2014
Messages
348
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
Staff member
Local time
Today, 06:24
Joined
Oct 29, 2018
Messages
21,357
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
Local time
Today, 09:24
Joined
Oct 20, 2018
Messages
3,476
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
Local time
Today, 08:24
Joined
May 11, 2011
Messages
11,611
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
Local time
Today, 09:24
Joined
Oct 20, 2018
Messages
3,476
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

Top Bottom