Removing spaces temporarily

[emozley]

Hi,

I have a database that has peoples names and national insurance numbers in. Unfortunately some of the numbers have spaces in and some don't. Is it possible to do a select where the national insurance number equals a certain value but not to worry about spaces?

Thanks

Ed

 

[JerryKlmns]

Ed

Why don't you get rid of that space, anyway?

 

[emozley]

It's an old database with a lot of records. It needs overhauling to be honest but before I do that I'm hoping there's an easy way. So far I have tried:

"SELECT Firstname, Surname FROM [Staff Database] WHERE Replace([NI Number],' ','')='" & Request.Form("NI") & "'

 

[JerryKlmns]

There is always the Like operator

SELECT Firstname, Surname
FROM [Staff Database]
WHERE [NI Number] Like "*" & Request.Form("NI") & "*"

but also that Replace function along with Trim and many other posts around about white noise space

 

[jebry]

Hi!

It wouldn't be difficult to get rid of the spaces permanently. Just use the replace function in an update query and you will get all numbers and no spaces.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[emozley]

I've removed all the spaces now and it's worked. I will use ASP to put spaces in the right places when displaying them.

Thanks

Ed