WhereCondition Syntax Problem when using "And".

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mac - 28 Mar 2007 21:28 GMT

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.

Signature

Regards, Michael

Reply to this Message

'69 Camaro - 28 Mar 2007 21:52 GMT

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.
[quoted text clipped - 4 lines]
>
> 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
[quoted text clipped - 3 lines]
> &
> 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
[quoted text clipped - 28 lines]
> Here is a Date/String WhereCondition string that functions properly for
> me.

Reply to this Message

Mac - 29 Mar 2007 16:22 GMT

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.

Signature

Regards, Michael

> Hi, Mac.
>
[quoted text clipped - 73 lines]
> > Here is a Date/String WhereCondition string that functions properly for
> > me.

Reply to this Message

'69 Camaro - 30 Mar 2007 01:22 GMT

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.
>
[quoted text clipped - 98 lines]
>> > Here is a Date/String WhereCondition string that functions properly for
>> > me.

Reply to this Message

'69 Camaro - 30 Mar 2007 01:28 GMT

Hi, Michael.

For lists of keywords and Reserved Words to avoid, please see the following
Web pages:

http://support.microsoft.com/default.aspx?scid=286335

http://support.microsoft.com/default.aspx?id=321266

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.

> Hi, Michael.
>
[quoted text clipped - 156 lines]
>>> > for
>>> > me.

Reply to this Message

Mac - 30 Mar 2007 13:36 GMT

Thanks for the heads-up on the rules and syntax. I'll remember to use those
techniques in the future.

Signature

Regards, Michael

> Hi, Michael.
>
[quoted text clipped - 174 lines]
> >>> > for
> >>> > me.

Reply to this Message

'69 Camaro - 30 Mar 2007 18:00 GMT

You're welcome. Good luck with the rest of your project.

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 heads-up on the rules and syntax. I'll remember to use
> those
[quoted text clipped - 204 lines]
>> >>> > for
>> >>> > me.

Reply to this Message