Newbie to VB with ACCESS. I am trying to build a SQL statement using 2 controls from a form. Keep getting a syntax error. I need another set of eyes to help find the problem. Below is my statement so far:
     strSql = "SELECT EQUIP.BLDG_NO, EQUIP.EQUIP_NO, EQUIP.EQCODE, EQUIP.LOCAT, EQUIP.SERVES" & _
                " FROM EQUIP WHERE ((EQUIP.BLDG_NO) = " & Val([Forms]![frmStartHere]![cmbBldgNo])) & _
                ") And ((EQUIP.EQUIP_NO) = '" & [Forms]![frmStartHere]![cmbEquipNo] & "')" & _
                " ORDER BY EQUIP.BLDG_NO"

equip.bldg_no is a number field. equip.equip_no is a text field. Both controls are combo fields on the form. This statement is being supplied to open a recordset (ADO).

Thanks for your time.

...whats the error you are getting?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

hi there...

really don't know what language are you programming, but the ) in the VAL function since to be useless (1 open, 2 close?). Apart from that the sql looks ok to me... the syntax error is in the line of code or in the sql???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

Error was just the "Compile Error, Syntax error"
I then changed my statement to the following:
"SELECT EQUIP.BLDG_NO, EQUIP.EQUIP_NO, EQUIP.EQCODE, EQUIP.LOCAT, EQUIP.SERVES" & _
                " FROM EQUIP WHERE ((EQUIP.BLDG_NO) = Val([Forms]![frmStartHere]![cmbBldgNo])" & _
                " And EQUIP.EQUIP_NO) = [Forms]![frmStartHere]![cmbEquipNo]" & _
                " ORDER BY EQUIP.BLDG_NO"

Now I am getting: No value given for one or more parameters

i don't know why you change your code, but the last one is definitive wrong...

use this:
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

In the above example and in your original post, you have Syntax errors in your where clause.

Origianl Post:
"SELECT EQUIP.BLDG_NO, EQUIP.EQUIP_NO, EQUIP.EQCODE, EQUIP.LOCAT, EQUIP.SERVES" & _
                " FROM EQUIP WHERE ((EQUIP.BLDG_NO) = " & Val([Forms]![frmStartHere]![cmbBldgNo])) & _
                ") And ((EQUIP.EQUIP_NO) = '" & [Forms]![frmStartHere]![cmbEquipNo] & "')" & _
                " ORDER BY EQUIP.BLDG_NO"

Should be: (removed ) that proceeded the And keword line 3
"SELECT EQUIP.BLDG_NO, EQUIP.EQUIP_NO, EQUIP.EQCODE, EQUIP.LOCAT, EQUIP.SERVES" & _
                " FROM EQUIP WHERE ((EQUIP.BLDG_NO) = " & Val([Forms]![frmStartHere]![cmbBldgNo])) & _
                " And ((EQUIP.EQUIP_NO) = '" & [Forms]![frmStartHere]![cmbEquipNo] & "')" & _
                " ORDER BY EQUIP.BLDG_NO"



And in your second post change:
"SELECT EQUIP.BLDG_NO, EQUIP.EQUIP_NO, EQUIP.EQCODE, EQUIP.LOCAT, EQUIP.SERVES" & _
                " FROM EQUIP WHERE ((EQUIP.BLDG_NO) = Val([Forms]![frmStartHere]![cmbBldgNo])" & _
                " And EQUIP.EQUIP_NO) = [Forms]![frmStartHere]![cmbEquipNo]" & _
                " ORDER BY EQUIP.BLDG_NO"

to this:
"SELECT EQUIP.BLDG_NO, EQUIP.EQUIP_NO, EQUIP.EQCODE, EQUIP.LOCAT, EQUIP.SERVES" & _
                " FROM EQUIP WHERE ((EQUIP.BLDG_NO) = Val([Forms]![frmStartHere]![cmbBldgNo]))" & _
                " And EQUIP.EQUIP_NO = [Forms]![frmStartHere]![cmbEquipNo]" & _
                " ORDER BY EQUIP.BLDG_NO"

Add a ) after cmbBldgNo and remove the ) that follows EQUIP.EQUIP_NO

Gonazalo, it looks like she is working with VBA.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

I turned on debug to step thru the procedure. When the recordset.open strSql executed, forced the error: "no values given for one or more parameters"

Error displayed with and without the val function. I will dink around some more.

Thanks for the tips.

that error meant that one of the field is not typed correctly...

verify every field name... to this quickly..:

turn on debuging..
step into the line that build the sql
get what's inside the sql variable
try that query in access and it will tell you wich field is wrong.

good luck...


HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

I am using VBA within some Access forms. Using procedures defined in form events to find records and display a form. Most data requires 2 column to be used in a query. You would think I could have started with a simpler project.

Pam,

One of the things that you have done (which all beginners do) is to fail to keep clear the distinction between literal text—a string—and items that represent values.

This is especially tricky when you are creating strings which themselves distinguish between literal and representative.

First let me take your code, and reformat it for reading ease: Specifying the table and the field (TBL.FLD) is only necessary when you have more than 1 table in the FROM clause. So this can be simplified Next, Access is absolutely insane about adding parentheses in SQL, many of which are unnecessary. The folowing red parens do nothing for the SQL, and make it harder to read Taking those out, in the folowing the SQL parser tries to find a record where BLDG is compared to an impossible value. See, the value on the form needs to be added to this SQL string you are building. To do that, you need to break out of being literal, and get VBA to provide the value found on the form (add the red parts): If [Forms]![frmStartHere]![cmbBldgNo] has " 002 " as a value, the resultant string will be Gotta go. Hopefully this will get you on your way. I'll check back later.