Re: Strange

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


Ray C wrote:
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" wrote:


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


Ray C wrote:


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







.

Relevant Pages

  • Re: Strange
    ... Sounds as though you are right, if I Change to Alli Sorts, Alla Sorts, Allx ... Can you post the code for BuildCriteria please? ... The Text Boxes are Bound to the Customers Table and the "find" combo ...
    (microsoft.public.access.gettingstarted)
  • Re: Strange
    ... Sounds as though you are right, if I Change to Alli Sorts, Alla Sorts, Allx ... The Text Boxes are Bound to the Customers Table and the "find" combo ... Public Function util_ShowInfo(frm As Form, strField As String, varInfo As ...
    (microsoft.public.access.gettingstarted)
  • Re: Strange
    ... The Text Boxes are Bound to the Customers Table and the "find" combo Boxes arte unbound. ... Public Function util_ShowInfo(frm As Form, strField As String, varInfo As Variant) As Boolean ... strCriteria = BuildCriteria ...
    (microsoft.public.access.gettingstarted)
  • Re: AES AWARDS
    ... What are people hooking up to ... People are using our boxes and cards with all sorts of equipment. ...
    (rec.audio.pro)
  • Re: One Tool, One Type of Turning, One Wood OR Keep Exploring?
    ... I too like to dabble and try my hand at all sorts. ... me is the guy who turns only boxes say, but is not really much good at ... Tom. ...
    (rec.crafts.woodturning)
Loading