Syntax of Where condition

[kraasty]

I have a form (myForm) with a query (myQuery) with many fields as
datasource.

When I set the variable:
stCriteria = " FIELD1 Like '*abc*' OR Field2 Like '*abc*' "

and run the command: DoCmd.OpenForm "myForm", , , stCriteria
the form displays only the records matching the criteria in the [Field1] and
[Field2] fields.

Is there a way to form the WHERE statement so that the "abc" text is
searched in all
the fields of the query without having to repeat the "FIELDx LIKE sometext
OR"
for every field of the query?

Thank you,
Andreas

 

[Lynn Trapp]

Not that I know of, but if you are searching for similar data in separate
fields, I wonder about the structure of your database. It doesn't sound very
normalized to me.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Nick Coe \(UK\)]

I don't think you can, though I could be wrong.

Why not just filter the data in your source query? It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the SQL
view if you wanted to...

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In kraasty typed:

 

[kraasty]

Why not just filter the data in your source query?
I have a unbound textbox in the main form to enter the search srtring
and a button that uses vba to open the form with the entered text as
criteria.

It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the SQL
view if you wanted to...

Thanks. I haven?t thought of it but I think it will need some work for the
conversion of the statement

I thought maybe there was another way to approach this.

Is there a way to get information the number of fields and the field names
in a query/table in VBA?
Or the number, name and type of the controls in a form,
so I could generate the where statement automatically?

I don't think you can, though I could be wrong.

Why not just filter the data in your source query? It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the SQL
view if you wanted to...

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In kraasty typed:

I have a form (myForm) with a query (myQuery) with many
fields as
datasource.

When I set the variable:
stCriteria = " FIELD1 Like '*abc*' OR Field2 Like '*abc*'
"

and run the command: DoCmd.OpenForm "myForm", , ,
stCriteria
the form displays only the records matching the criteria
in the
[Field1] and [Field2] fields.

Is there a way to form the WHERE statement so that the
"abc" text is
searched in all
the fields of the query without having to repeat the
"FIELDx LIKE
sometext OR"
for every field of the query?

Thank you,
Andreas

 

[Martin Walke]

Nick,

You can use the following code

<aircode>

<open recordset>
for i = 0 to Myset.Fields.count-1
Print Myset.Field(i).name
next

</aircode>

HTH
Martin

Why not just filter the data in your source query?

I have a unbound textbox in the main form to enter the search srtring
and a button that uses vba to open the form with the entered text as
criteria.

It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the SQL
view if you wanted to...

Thanks. I haven?t thought of it but I think it will need some work for the
conversion of the statement

I thought maybe there was another way to approach this.

Is there a way to get information the number of fields and the field names
in a query/table in VBA?
Or the number, name and type of the controls in a form,
so I could generate the where statement automatically?

I don't think you can, though I could be wrong.

Why not just filter the data in your source query? It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the SQL
view if you wanted to...

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In kraasty typed:

I have a form (myForm) with a query (myQuery) with many
fields as
datasource.

When I set the variable:
stCriteria = " FIELD1 Like '*abc*' OR Field2 Like '*abc*'
"

and run the command: DoCmd.OpenForm "myForm", , ,
stCriteria
the form displays only the records matching the criteria
in the
[Field1] and [Field2] fields.

Is there a way to form the WHERE statement so that the
"abc" text is
searched in all
the fields of the query without having to repeat the
"FIELDx LIKE
sometext OR"
for every field of the query?

Thank you,
Andreas

 

[Nick Coe \(UK\)]

Not me mate... kraasty

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In Martin Walke typed:

Nick,

You can use the following code

<aircode>

<open recordset>
for i = 0 to Myset.Fields.count-1
Print Myset.Field(i).name
next

</aircode>

HTH
Martin

Why not just filter the data in your source query?

I have a unbound textbox in the main form to enter the
search srtring
and a button that uses vba to open the form with the
entered text as
criteria.

It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the
SQL
view if you wanted to...

Thanks. I haven?t thought of it but I think it will need
some work
for the conversion of the statement

I thought maybe there was another way to approach this.

Is there a way to get information the number of fields
and the field
names in a query/table in VBA?
Or the number, name and type of the controls in a form,
so I could generate the where statement automatically?



"Nick Coe (UK)" <[email protected]>
wrote in
message news:%23JsSN3%[email protected]...

I don't think you can, though I could be wrong.

Why not just filter the data in your source query? It's
much easier to build a where like that with the query
designer grid and you could then cut and paste from the
SQL
view if you wanted to...

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In kraasty typed:
I have a form (myForm) with a query (myQuery) with many
fields as
datasource.

When I set the variable:
stCriteria = " FIELD1 Like '*abc*' OR Field2 Like
'*abc*'
"

and run the command: DoCmd.OpenForm "myForm", , ,
stCriteria
the form displays only the records matching the
criteria
in the
[Field1] and [Field2] fields.

Is there a way to form the WHERE statement so that the
"abc" text is
searched in all
the fields of the query without having to repeat the
"FIELDx LIKE
sometext OR"
for every field of the query?

Thank you,
Andreas

 

[Nick Coe \(UK\)]

I can think of a few things to try but I'm extremely pushed
for time just now so I'll have to be brief I'm afraid.

If you enumerate all the controls on a given form you will
end up with labels and all sorts in the list not just the
bound data controls, so you have to filter them out.

You could use the DoCmd.FindRecord method in code, that will
search whole rows.

You could try using Access own Filter By Form button.... Or
create your own QBE form.

Suggest you think about getting Access Developers Handbook,
pub Sybex, www.developershandbook.com

The basic way to enumerate from a form is (this is
untested):

Public Function EnumCtls(strFName as String)
' Get form controls

' Nick Coe

Dim obj As AccessObject
Dim frm As Form

Dim strCtlName As String
Dim ctl As Control, lngCtl As Long


On Error GoTo HandleErr


Set dbs2 = Application.CurrentProject

For Each obj In dbs2.AllForms

If obj.Name = strFName Then

DoCmd.OpenForm strFName, acDesign, , , , acHidden

For Each ctl In Forms(strFName).Controls
strCtlName = ctl.Properties("Name")
Next ctl ' Next Control on Form

DoCmd.Close acForm, strFName, acSaveNo
End If ' If name of form is OK
Next obj


ExitHere:
Exit Function

' Error handling block
HandleErr:
Select Case Err.Number
Case 2455
' 2455 occurs if a control has no Help Context ID
property
' e.g. A sub form

Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.description,
vbCritical, "Form " & strFName & " control enumeration"
End Select
Resume ExitHere

' End Error handling block.
End Function

Good luck

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In kraasty typed: