Hi, Michael.
The fields in the data table that
the combo box named cboField is bound to all have unique descriptive
names
like "Serial Number" and "Status".
Any character that isn't alphanumeric or the underscore is illegal.
The
space between Serial and Number is illegal because the space indicates
"the next identifier or keyword is coming" to the parser. Since Number
isn't another identifier or appropriate keyword, this will result in a
syntax error.
I'm also
not sure what you mean by using brakets to try to fix the problem.
To avoid this syntax error, brackets are placed at the beginning and at
the end of the identifier to tell Jet or the VB syntax checker that
[Serial Number] is all one string for the identifier. This usually
fixes
it, but not always, because Jet optimizes SQL code and sometimes breaks
code that used to work.
If you don't want to chase unnecessary bugs (you will occasionally
forget
a bracket or two when they're needed or Jet will rearrange your code
for
you when you least expect it), then only use alphanumeric characters
and
the underscore in names and use the Caption Property to show names for
humans who can't read camel case or underscores as spaces between
words.
For example:
Column Name: SerialNum
Caption: Serial Number
When viewing the column in a table, query, or form built with the form
wizard, the column header will read Serial Number, not SerialNum, the
name
of the column.
Is it illegal to name a control "field"?
Yes. Reserved words are illegal.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blogs:
www.DataDevilDog.BlogSpot.com,
www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Thanks for the correct syntax on the strings.
I am, however, confused about how the combo box named cboField and the
text
box named tboSearchInformation is illegal. The fields in the data
table
that
the combo box named cboField is bound to all have unique descriptive
names
like "Serial Number" and "Status". I don't have any of the
fileds(columns) in
the table named "Field". Is it illegal to name a control "field"? I'm
also
not sure what you mean by using brakets to try to fix the problem.
I am new to VBA and I am trying to use common techniques but any help
is
appreciated.
Anyway thanks for your help.
--
Regards, Michael
:
Hi, Mac.
Here is an example of the string that errors with 'Type Mismatch'.
This
string is data type Number for Me!cboField and Date for
Me!cboField2.
strWhere = "[" & Me!cboField & "] = #" & Me![tboSearchInformation]
&
"#
And
[" & Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"
What am I doing wrong?
Your syntax indicates that someone used illegal characters to name
the
column, and brackets are used to try to fix the problem. This
doesn't
always work. Only use alphanumerics and the underscore character to
name
columns, tables, and any other identifier, and you'll avoid a lot of
unnecessary bug chasing.
Your syntax also indicates that tboSearchInformation is a date data
type
and
tboSearchInformation2 is a string data type. Try:
strWhere = "[" & Me!cboField & "] = " & Me![tboSearchInformation] & "
And ["
& Me!cboField2 & "] =#" & Me![tboSearchInformation2] & "#"
Here is another string that errors with 'Type Mismatch. This string
is
data
type Number for Me!cboField and String for Me!cboField2.
strWhere = "[" & Me!cboField & "] = " & Me![tboSearchInformation]
And
"["
&
Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"
Your concatenation is wrong for the "And" part. Try:
strWhere = "[" & Me!cboField & "] = " & Me![tboSearchInformation] & "
And ["
& Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blogs:
www.DataDevilDog.BlogSpot.com,
www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.
I have a report that gets run to show records that meet criteria
determined
by either one or two inputs. When one input is selected I only need
a
single
WhereCondition string. Those I have are working properly.
When two inputs are selected I need to build a WhereCondition
string
with
an
"And".
Here is an example of the string that errors with 'Type Mismatch'.
This
string is data type Number for Me!cboField and Date for
Me!cboField2.
strWhere = "[" & Me!cboField & "] = #" & Me![tboSearchInformation]
&
"#
And
[" & Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"
What am I doing wrong?
strWhere = "[" & Me!cboField & "] = " & Me![tboSearchInformation]
And
"["
&
Me!cboField2 & "] = #" & Me![tboSearchInformation2] & "#"
Here is another string that errors with 'Type Mismatch. This string
is
data
type Number for Me!cboField and String for Me!cboField2.
strWhere = "[" & Me!cboField & "] = " & Me![tboSearchInformation]
And
"["
&
Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"
Here is a Date/String WhereCondition string that functions properly
for
me.
