Strange

[Guest]

Hi. I have a Form that looks up Customer information via a number of Combo
Boxes. The Text Boxes are Bound to the Customers Table and the "find" combo
Boxes arte unbound. I use the following External Function to locate the
record and set the bookmark to the record so the Text boxes show the
information held in the record. I then place the apropriate info into the
various "Find" Combo Boxes. I do this with the following code

Public Function util_ShowInfo(frm As Form, strField As String, varInfo As
Variant) As Boolean

If IsNumeric(varInfo) Then
strCriteria = BuildCriteria(strField, dbInteger, varInfo)
Else
strCriteria = BuildCriteria(strField, dbText, varInfo)
End If

With frm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
frm.Bookmark = .Bookmark
frm!cmbFindStoreNo = .StoreNo ' cmbFindStoreNo
frm!cmbFindStoreName = .StoreName ' cmbFindStoreName
frm!cmbFindAccNo = .AccountNo ' cmbFindAccountNo
frm!cmbFindCustomerName = .CustomerName ' cmbFindCustomerName
frm!cmbContractNo = .ContractNo
util_ShowInfo = True
Else
util_ShowInfo = False
End If

End With

End Function

This all works fine EXCEPT when I look up a customer called "All Sorts" in
this instance I get the following error :- Run -time Error '7956' The syntax
of the sub queryin this expression is incorrect.
This is generated by the following line in the above code :-
strCriteria = BuildCriteria(strField, dbText, varInfo)
I can search for a customer called "Allied Dunbar! and find it wth no
problem as I can with othe customers in the table but when I search for "All
Sorts" I always get this Error.
Even stranger, if I change the information in the Field to "A", it finds the
record with no problem. Change the information in the Field to "Al" it finds
the record with no problem. Change the information in the Field to "All" and
I get an Error but it is slightly different from the one above :- Run-time
Error '2431' The Expression you entered contains invalid syntax. Change the
information in the Field back to "All Sorts" and I am back to the Message -
Run -time Error '7956' The syntax of the sub query in this expression is
incorrect .

Does anyone know what could be happening?

Thanks RayC

 

[J. Goddard]

It sounds like "All" is causing a problem - it is a reserved word. The
problem may be in the function buildcritera, where it may not be be
putting something in quotes.

Can you post the code for BuildCriteria please?

John

 

[Guest]

Hi. The function "BuildCriteria" is something that is built into Access and
I don't know how to get to all that stuff.
Sounds as though you are right, if I Change to Alli Sorts, Alla Sorts, Allx
Sorts, it runs without a problem. change to All', All-, All. and I get the
same errors.
If we can not det into the Access internal code, I will have to write my own
version and I am not realy good enough for that, couls you help there?

Thanks RayC

 

[J. Goddard]

I wasn't aware of BuildCriteria.

Here's the solution:

When you are building your varinfo string, enclose the data in single
quotes. Works like a charm:


?buildcriteria("Company",dbtext,"'All Sorts'")
Company='All Sorts'

I did this in the immediate window. Note: if your field contains
blanks, you will have to enclose it in square brackets, as in "[Company
Name]"

John

 

[Guest]

As you said, works like a Charm the line now looks like this,
strCriteria = BuildCriteria(strField, dbText, "'" & varInfo & "'")

Thamk You RayC

I wasn't aware of BuildCriteria.

Here's the solution:

When you are building your varinfo string, enclose the data in single
quotes. Works like a charm:


?buildcriteria("Company",dbtext,"'All Sorts'")
Company='All Sorts'

I did this in the immediate window. Note: if your field contains
blanks, you will have to enclose it in square brackets, as in "[Company
Name]"

John

Hi. The function "BuildCriteria" is something that is built into Access and
I don't know how to get to all that stuff.
Sounds as though you are right, if I Change to Alli Sorts, Alla Sorts, Allx
Sorts, it runs without a problem. change to All', All-, All. and I get the
same errors.
If we can not det into the Access internal code, I will have to write my own
version and I am not realy good enough for that, couls you help there?

Thanks RayC

: