Allen Browne Search form HELP!!!!!!!!!!!!!!

[Erin Freeman]

I am using the search form and I love it, however, i am trying to search 24
fields now and as soon as i word wrap in the visual basic editor window it
doesnt work....

If Not IsNull(Me.txtsub) Then
strWhere = strWhere & "([SubDept A1] Like ""*" & Me.txtsub & "*""
Or [SubDept B1] Like ""*" & Me.txtsub & "*"" Or [SubDept C1] Like ""*" &
Me.txtsub & "*"" Or [SubDept D1] Like ""*" & Me.txtsub & "*"" Or [SubDept E1]
Like ""*" & Me.txtsub & "*"" Or [SubDept F1] Like ""*" & Me.txtsub & "*"" Or
[SubDept G1] Like ""*" & Me.txtsub & "*"" Or [SubDept H1] Like ""*" &
Me.txtsub & "*"" Or [SubDept I1] Like ""*" & Me.txtsub & "*"" ) AND "

End If

Here is my code (with less seach fields) you get the idea, So what i was
trying to do is just before the ) AND " i put a _ to word wrap, and then
continue on, however when i am all done, it tells me that the ) at the end is
an expected end of statment...any ideas?

 

[Dennis]

You have an extra double-quote after the last *.

 

[Erin Freeman]

all of the code is formatted like that, note the first line below

strWhere = strWhere & "([SubDept A1] Like ""*" & Me.txtsub & "*""

You have an extra double-quote after the last *.

I am using the search form and I love it, however, i am trying to search 24
fields now and as soon as i word wrap in the visual basic editor window it
doesnt work....

If Not IsNull(Me.txtsub) Then
strWhere = strWhere & "([SubDept A1] Like ""*" & Me.txtsub & "*""
Or [SubDept B1] Like ""*" & Me.txtsub & "*"" Or [SubDept C1] Like ""*" &
Me.txtsub & "*"" Or [SubDept D1] Like ""*" & Me.txtsub & "*"" Or [SubDept E1]
Like ""*" & Me.txtsub & "*"" Or [SubDept F1] Like ""*" & Me.txtsub & "*"" Or
[SubDept G1] Like ""*" & Me.txtsub & "*"" Or [SubDept H1] Like ""*" &
Me.txtsub & "*"" Or [SubDept I1] Like ""*" & Me.txtsub & "*"" ) AND "

End If

Here is my code (with less seach fields) you get the idea, So what i was
trying to do is just before the ) AND " i put a _ to word wrap, and then
continue on, however when i am all done, it tells me that the ) at the end is
an expected end of statment...any ideas?

 

[Allen Browne]

The underscore is the answer for line-wraps in VBA.
They must not be inside the quotes.
Keep trying - perhaps with simpler expressions first: you'll get it.

(BTW, your example *really* looks like a spreadsheet in Access instead of a
correct relational design.)

 

[Erin Freeman]

Thanks for the answer, do you have any suggestions as to how i would go
about getting a correct relational design instead of lookin like a
spreadsheet? the background is i have a about 20 values that i need to filter
on using your search form, i am still learning so i have just used your
example for the "contains" search field for this to be able to search all of
those values....

The underscore is the answer for line-wraps in VBA.
They must not be inside the quotes.
Keep trying - perhaps with simpler expressions first: you'll get it.

(BTW, your example *really* looks like a spreadsheet in Access instead of a
correct relational design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using the search form and I love it, however, i am trying to search 24
fields now and as soon as i word wrap in the visual basic editor window it
doesnt work....

If Not IsNull(Me.txtsub) Then
strWhere = strWhere & "([SubDept A1] Like ""*" & Me.txtsub & "*""
Or [SubDept B1] Like ""*" & Me.txtsub & "*"" Or [SubDept C1] Like ""*" &
Me.txtsub & "*"" Or [SubDept D1] Like ""*" & Me.txtsub & "*"" Or [SubDept
E1]
Like ""*" & Me.txtsub & "*"" Or [SubDept F1] Like ""*" & Me.txtsub & "*""
Or
[SubDept G1] Like ""*" & Me.txtsub & "*"" Or [SubDept H1] Like ""*" &
Me.txtsub & "*"" Or [SubDept I1] Like ""*" & Me.txtsub & "*"" ) AND "

End If

Here is my code (with less seach fields) you get the idea, So what i was
trying to do is just before the ) AND " i put a _ to word wrap, and then
continue on, however when i am all done, it tells me that the ) at the end
is
an expected end of statment...any ideas?

 

[Allen Browne]

It looks like one entity (a department?) can have lots of sub-entities (sub
departments?), and you have each of thee as a field in the table. Instead,
you need a related table where there is a *record* for each subdepartment.

This kind of thing:

Department table:
DepartmentID AutoNumber
DepartmentName Text

DepartmentSub table:
DepartmentSubID AutoNumber
DepartmentID Number (which department this entry is
for)
DepartmentSubName Text

This is the basic one-to-many relation. If you want to read up on how to
design tables correctly (called normalization), Jeff Conrad has a number of
links here:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the answer, do you have any suggestions as to how i would go
about getting a correct relational design instead of lookin like a
spreadsheet? the background is i have a about 20 values that i need to
filter
on using your search form, i am still learning so i have just used your
example for the "contains" search field for this to be able to search all
of
those values....

The underscore is the answer for line-wraps in VBA.
They must not be inside the quotes.
Keep trying - perhaps with simpler expressions first: you'll get it.

(BTW, your example *really* looks like a spreadsheet in Access instead of
a
correct relational design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using the search form and I love it, however, i am trying to search
24
fields now and as soon as i word wrap in the visual basic editor window
it
doesnt work....

If Not IsNull(Me.txtsub) Then
strWhere = strWhere & "([SubDept A1] Like ""*" & Me.txtsub &
"*""
Or [SubDept B1] Like ""*" & Me.txtsub & "*"" Or [SubDept C1] Like ""*"
&
Me.txtsub & "*"" Or [SubDept D1] Like ""*" & Me.txtsub & "*"" Or
[SubDept
E1]
Like ""*" & Me.txtsub & "*"" Or [SubDept F1] Like ""*" & Me.txtsub &
"*""
Or
[SubDept G1] Like ""*" & Me.txtsub & "*"" Or [SubDept H1] Like ""*" &
Me.txtsub & "*"" Or [SubDept I1] Like ""*" & Me.txtsub & "*"" ) AND "

End If

Here is my code (with less seach fields) you get the idea, So what i
was
trying to do is just before the ) AND " i put a _ to word wrap, and
then
continue on, however when i am all done, it tells me that the ) at the
end
is
an expected end of statment...any ideas?