SQL

[ScottF]

I'm using Combo box to fill in a and b in the Sql statment. Then I click the commannd button and I get this error "To Few Parameters. Expected 1" in the SQL statment. My brain is fried I have seen this error before but I can't figure it Can sombody help.
Private Sub Command1_Click()
Dim a As String
Dim db As Database
Dim dbs As Recordset
Dim Sql As String
Dim b As String
b = cmbMonth.Text
a = cmbDate.Text
Sql = "Select * from " & b & " where DayM= '" & a & "'"
Data1.DatabaseName = "txtPath"
Data1.Refresh
Set db = OpenDatabase(txtPath)
Set dbs = db.OpenRecordset(Sql)
Set Data1.Recordset = dbs
dbs.Close
db.Close
End Sub

[LG]

Hi,ScottF.
If DayM field is a DateTime data type, than you have to use # around you value instead of ":
Sql = "Select * from " & b & " where DayM= " & Chr(35) & a & Chr(35)

Larisa

[ScottF]

No it just a text field with number in it 1-31

[LG]

Did you try this:
Sql = "Select * from " & b & " where DayM= " & Chr(34) & a & Chr(34)

Larisa

[JHausmann]

How is DayM defined in your database?

[ScottF]

Yes I try it, Aaron I place a break point and check it looks fine the b is show the name of the table and the a is showing a number (When I debug and Print I get SQL Select * from Feb where Date = 3). When the program run this statement Set dbs = db.OpenRecordset(Sql) is where I get the error. I've written hundred are so SQL statments and I've never had this many problems. The program has two Combo boxes one for the table name and one for the field I what to SQL. I have a couple of text boxes that are bound to Data1. After choosing the table and field the click event sql off the result as you see above. All the fields in the database in Access are text fields. A real simple program but I must be doing something wrong.

[ScottF]

Thank you for your help I figure out Sorry for the trouble

[ScottF]

It is a text in the database

[LG]

Hi, ScottF.
Did you try my last post?
Larisa

[Aaron Young]

Put in a BreakPoint and Check the Value of a and b, is b the name of the Table? - It should be.



------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

[Paul282]

The best debug method I've used for SQL is to output your sql string with debug.print then copy and paste it into you DB software (or isql for sql server) or whatever. you can then see the results and be sure of whether the problem is a sql statement problem or a software -string- issue. We can't see your server so it's difficult to troubleshoot (notice all the questions :-) ) once you know where the problem is, if you still can't solve it post a new question with the debug info you've learned. (include the DB datatypes so the single quoting will be easier to see)

Have a good one

[FirstKnight]

Here's a tip as far as SQL is concerned. If you don't know how the statement should look, or your having problems getting it to work try this. Open MS Access and design a query. Create som bogus tables if you need to. After you have created the query and it give's you the correct result, click on View and then on SQL Statement. You will now see the SQL Statement for the query that you have just designed and you can use this in VB.

Hope it helps.

------------------
Hennie Pieterse
Senior Programmer
Datcor International
[email protected]
Telephone: +27 15 7816335
ICQ Number : 61636095

Today is yesterday's tomorrow