Wildcards?

[Wfuphan]

I have a table in my database that lists different documents, and each document has an assigned 8-10 digit document number. Each document number is unique, however the basic structure of the number is about the same. For example, one number might be 546-RES-09-092 and another might be 546-SS-08-023. So, to make it easier on the person entering this data into the database, I set up an input mask that corresponds with the number structure.

The problem I'm having, involves a query that I set up to locate specific documents. Due to the input mask some of the document numbers have spaces where there isn't a character assigned to the space. So, 546-SS-08-023 comes out as 546- SS-08-023. I need a query that will find the document number regardless of the space or not because the average user will not know to input the spaces. Is this possible?
Thanks

 

[PHV]

Something like this ?
WHERE Replace([table name].[document number], " ", "") = Replace([Enter a document number], " ", "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wfuphan]

I set up my query as follows, and I get an undefined function message box for the Replace function. Did I set it up right?

SELECT [Outgoing Doc log_1].[Log Number], [Outgoing Doc log_1].[Document Number], [Outgoing Doc log_1].[Document Title], [Outgoing Doc log_1].Author, [Outgoing Doc log_1].Status
FROM [Outgoing Doc Log] AS [Outgoing Doc log_1]
WHERE REPLACE([Outgoing Doc Log].[Document Number], " ", "") =REPLACE([Document Number?], " ", "")

 

[PHV]

Which version of access ?
Works on mine (ac2003)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wfuphan]

Access '97, I guess this function wasn't defined back then. That answers some questions though, because I've been using a book that hasn't been too much help.

Are there any other ways of solving this problem without that function?

 

[PHV]

Create your own Public function in a standard code module.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

This faq has "replacement" functions for 97 faq705-4342 (Replace, InStrRev, Join, Split...), can save some time;-)

Roy-Vidar

 

[Krash878]

make the query entry something like this but with your form name and textbox name

Like "*" & [forms]![MainClosed]![txtDate] & "*"

It will be a total wildcard system

Hope this helps

Kenny