Re: Search for a record based on one of two fields

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Afraid it still does not work - it would appear to be a syntax error (the
text comes up in red and the compiler reports a syntax error)

it is most perplexing!

thanks for your help so far


"Luke Bedggood" wrote:

> Hi Allen
>
> Thanks for this - unfortunatly I cannot get it to work as it claims a
> bracket is missing.
>
> I have managed to get the compilier to accept the following (it uses my
> field names)
>
> Dim stDocName As String
> Dim stLinkCriteria As String
>
> stDocName = "frmTechnicalPartNumberView"
>
> stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & "'")
> Or _
> ("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")
>
> DoCmd.OpenForm stDocName, , , stLinkCriteria
>
>
> however when run it says 'type mismatch' and nothing else. any further
> insight you can add would be appreciated.
>
> regards
>
> Luke
>
> "Allen Browne" wrote:
>
> > So you want to search to find a matche in either field:
> >
> > Dim strWhere As String
> > strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
> > """) OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"
> >
> > You can then set the form's Filter to this string and FilterOn = True, or
> > you could FindFirst in the form's RecordsetClone and set the Bookmark.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Luke Bedggood" <LukeBedggood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:C2358A7C-D21F-4C3A-98A5-CCF5645CF5AF@xxxxxxxxxxxxxxxx
> > > Basically..
> > >
> > > I have a text box which is the search data (a part number) & a button that
> > > says start the search (using the info in the text field)
> > >
> > > I have a whole bunch of records, and because of my companies semi-adoption
> > > of two systems, we have inadvertantly ended up with two part number
> > > references per actual part - both have a seperate field in the record, but
> > > what I would like to be able to do is that should a search not reveal the
> > > part number in the first field, to search the second and then bring forth
> > > the
> > > relevant detail
> > >
> > > ie a record has a fldPartNumber1 (example data: Part Number) and a
> > > fldPartNumber2 (example data: Number, Part)
> > >
> > > I would like the user to be able to enter and search against either part
> > > reference by entering the search into a single box, hitting 'go' and the
> > > correct record being returned.
> > >
> > > Fortunatly there is no possibility of duplications in either field, both
> > > next to each other, and throughout the entire recordset......
> > >
> > > thanks for any insight
> >
> >
> >
.

Relevant Pages

  • Re: Multi Field SQL Where Clause
    ... The triple quotes cause a syntax error. ... Public sysNtApLkNo as Long ... when using the string as you have defined it the correct values ... Dim sysNtSysCd As String ...
    (microsoft.public.access.forms)
  • Re: Syntax Error WHY?
    ... Dim sql As String ... Dim strWhere As String ... I am getting a Syntax Error in my Update statement. ...
    (microsoft.public.access.formscoding)
  • Re: Error 3075 Syntax Error
    ... Syntax error in query expression ... Dim mydb As Database ... Dim strQryName As String ... Dim strArchiveToDB As String ...
    (microsoft.public.access.formscoding)
  • Re: Filter issue?????
    ... into this syntax error and it driving me a little crazy. ... Dim strDeptName As String ... ' Combine criteria strings into a WHERE clause for the filter ...
    (microsoft.public.access.forms)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)