form search

[danny]

Hey,

I am searching through a huge table which was imported
but the problem is that one of the fields has space
before the text and so the search call is not working
well.

It basically is trying to match the space.

Not sure what to do here.

Please help.

Thanks,

danny

 

[Rick B]

You might search for a string in the field. For example, let's say you are
looking for the field containing "Texas", but your inport inported it as
"_Texas" simply modify your search as...

Like "*Texas*"

Rick B


Hey,

I am searching through a huge table which was imported
but the problem is that one of the fields has space
before the text and so the search call is not working
well.

It basically is trying to match the space.

Not sure what to do here.

Please help.

Thanks,

danny

 

[Alicia]

You can clean the spaces out by using an update query.
UPDATE [tblMyTable] SET [tblMyTable].[fldMyField] = Trim
([fldMyField]);

 

[danny]

Hey Rick,

this is what i am doing, i am not sure how i would
incorporate what you said below.

This is my query search which basicall calls a query
called Final Query which is below the code.

Private Sub QuerySearch_Click()
On Error GoTo Err_QuerySearch_Click

Dim stDocName As String

stDocName = "FinalQuery"

If DCount("*", "FinalQuery") = 0 Then
MsgBox "No records found for Gab/SSN# " & Me.GabId
Else
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If

Exit_QuerySearch_Click:
Exit Sub

Err_QuerySearch_Click:
MsgBox Err.Description
Resume Exit_QuerySearch_Click
End Sub



Query:
SELECT [Test Cases].[GAB/SS#], [Test Cases].[Doc-Date], *
FROM [Test Cases]
WHERE ((([Test Cases].[GAB/SS#])=([Forms]![Final Data
Retrieval]![GabId])))
ORDER BY [Test Cases].[Doc-Date] DESC;

Thanks for your help.

Danny

 

[John Spencer (MVP)]

If it is always ONE space then you could just use criteria like the following
which would be able to take advantage of any index.

WHERE [Gab/SS#] = " " & Forms![Final Data Retrieval]![Gabid] OR
[Gab/SS#] = Forms![Final Data Retrieval]![Gabid]

If it is multiple spaces, then you either have to strip out the leading spaces
or use a like clause


WHERE Trim([Gab/SS#])=Forms![Final Data Retrieval]![Gabid]

Or

Hey Rick,

this is what i am doing, i am not sure how i would
incorporate what you said below.

This is my query search which basicall calls a query
called Final Query which is below the code.

Private Sub QuerySearch_Click()
On Error GoTo Err_QuerySearch_Click

Dim stDocName As String

stDocName = "FinalQuery"

If DCount("*", "FinalQuery") = 0 Then
MsgBox "No records found for Gab/SSN# " & Me.GabId
Else
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If

Exit_QuerySearch_Click:
Exit Sub

Err_QuerySearch_Click:
MsgBox Err.Description
Resume Exit_QuerySearch_Click
End Sub

Query:
SELECT [Test Cases].[GAB/SS#], [Test Cases].[Doc-Date], *
FROM [Test Cases]
WHERE ((([Test Cases].[GAB/SS#])=([Forms]![Final Data
Retrieval]![GabId])))
ORDER BY [Test Cases].[Doc-Date] DESC;

Thanks for your help.

Danny

-----Original Message-----
You might search for a string in the field. For example, let's say you are
looking for the field containing "Texas", but your inport inported it as
"_Texas" simply modify your search as...

Like "*Texas*"

Rick B


Hey,

I am searching through a huge table which was imported
but the problem is that one of the fields has space
before the text and so the search call is not working
well.

It basically is trying to match the space.

Not sure what to do here.

Please help.

Thanks,

danny


.