blong824

HI,

Im trying to run a swl statement in vb to open up a record set. Im new to record sets and Im hoping this is an easy fix. Im taking three values off a form and using them in the code to return a set of records and then I will print these records(but I haven't made the report yet). Can someone tell me why I get a syntax error in this code?

Dim db As Database, Rs As Recordset
Dim sql As String
Select Case Frame2
Case 1
Set db = CurrentDb
sql = "SELECT tblCustomers.FirstName, tblCustomers.LastName, tblCustomers.BillingAddress,"
sql = sql & "tblCustomers.BillingApt, tblCustomers.BillingCity, tblCustomers.BillingState,"
sql = sql & "tblCustomers.BillingZip, tblCustomers.PhoneNumber, tblCustomers.EmailAddress,"
sql = sql & "tblCustomers.ShippingAddress, tblCustomers.ShippingApt, tblCustomers.ShippingCity,"
sql = sql & "tblCustomers.ShippingState, tblCustomers.ShippingZip, tblCustomers.SourceID,"
sql = sql & "tblCustomers.CustomerID, tblOrders.OrderDate, tblOrders.OrderTypeID"
sql = sql & " FROM ((tblCustomers LEFT JOIN tblLeadResponse ON tblCustomers.CustomerID = tblLeadResponse.CustomerID)"
sql = sql & " LEFT JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID)"
sql = sql & " LEFT JOIN tblShipping ON tblOrders.OrderID = tblShipping.OrderID"
sql = sql & " WHERE(((tblCustomers.SourceID)= " & LeadSource & " ) AND ((tblOrders.OrderDate)"
sql = sql & "Between " & BeginDate & " And " & EndDate & ") AND ((tblOrders.[O/I])=O)"
sql = sql & " AND ((tblLeadResponse.NoCall) Is Null) AND ((tblShipping.ShipDate) Is Null))"


Set Rs = Nothing
Set Rs = db.OpenRecordset(sql)

LeadCount = Rs.RecordCount
Rs.Close
db.Close

End Select

Thanks

Robby

If you're using CurrentDB, you must be in Access forms, right?

Either way, dates should be surrounded by # (pound) signs, strings by single quotes.

".....Where SourceID = " & LeadSource & " .....' If this is numeric, it's fine.

".....OrderDate Between #" & BeginDate & "# AND #" & EndDate & "# AND..." 'This is for dates.

"....SomeTextField = '"& strText & "' AND ...." ' Strings

blong824

Hi,

They are already being passed as dates. Either way I just tried with the #'s and it didn't work. The error says 3705 syntax error and lists the code after there WHERE but in vb the highlighted text is Set Rs = db.openrecordset(sql). Any suggestions?

jayceepoo

Have you tried to see what the actual value in sql is? Put a break point right before set Rs = db.OpenRecordset(sql), open up an immediate window and type this in "? sql" and then press enter. This'll show you the exact value thats in the variable sql.

hope that helps

blong824

Hi,

I typed ?sql in the immediate window and first it just gave me the sequel statement string I had typed. I then typed DoCmd.runsql(sql) and it gave me a syntax error saying missing operator after my WHERE statement. I hope someone can help.
Thanks

blong824

Hi again,

I corrected some syntax and tried to run DoCmd.Runsql(SQL) in the immediate window. It told me a RunSQL action requires an argument consisting of a SQL statement. It is runtime error 2342. My statement is a SELECT statement which I thought is fine. Does anyone have any ideas now?
Thanks

Shurik12

Hi,

my guess is in:

sql = sql & "Between " & BeginDate & " And " & EndDate & ") AND ((tblOrders.[O/I])=O)"

you don't have a space between the double quotes and Between)------> should be...& " Between ....


One more thing:

Set Rs = Nothing
Set Rs = db.OpenRecordset(sql)

should be the other way round:

Set Rs = db.OpenRecordset(sql)
Set Rs = Nothing

Regards,
Shurik12.

blong824

Hi

blong824

HI,

This is what I have now:

Dim db As Database, Rs As Recordset
Dim sql As String
Select Case Frame2
Case 1
Set db = CurrentDb
sql = "SELECT tblCustomers.FirstName, tblCustomers.LastName, tblCustomers.BillingAddress,"
sql = sql & "tblCustomers.BillingApt, tblCustomers.BillingCity, tblCustomers.BillingState,"
sql = sql & "tblCustomers.BillingZip, tblCustomers.PhoneNumber, tblCustomers.EmailAddress,"
sql = sql & "tblCustomers.ShippingAddress, tblCustomers.ShippingApt, tblCustomers.ShippingCity,"
sql = sql & "tblCustomers.ShippingState, tblCustomers.ShippingZip, tblCustomers.SourceID,"
sql = sql & "tblCustomers.CustomerID, tblOrders.OrderDate, tblOrders.OrderTypeID"
sql = sql & " FROM ((tblCustomers LEFT JOIN tblLeadResponse ON tblCustomers.CustomerID = tblLeadResponse.CustomerID)"
sql = sql & " LEFT JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID)"
sql = sql & " LEFT JOIN tblShipping ON tblOrders.OrderID = tblShipping.OrderID"
sql = sql & " WHERE(((tblCustomers.SourceID)= '" & LeadSource & "' ) AND ((tblOrders.OrderDate)"
sql = sql & " Between #" & BeginDate & "# And #" & EndDate & "#) AND ((tblOrders.[O/I])=O)"
sql = sql & " AND ((tblLeadResponse.NoCall) Is Null) AND ((tblShipping.ShipDate) Is Null))"


Set Rs = db.OpenRecordset(sql)

LeadCount = Rs.RecordCount

Rs.Close
db.Close

It now tells me too few parameters. Expected 1. The LeadSource field on the form is a lookup field to a table. It stores numbers but displays text. The date fields are unbound and store a value chosen from a calendar form. Can anyone show me an example of a swl statement that works and uses linked tables and parameters off a form?

Thanks

Thinker

This isn't right either. If you do this, the next statement,
LeadCount = Rs.RecordCount
will fail. Just get rid of this statement, Set Rs = Nothing.

Also, if you are working with Access 2000, you should definitely
declare your recordset variable like...
Dim db As Database, Rs As DAO.Recordset

kcwallace

You typed:

sql = sql & " WHERE(((tblCustomers.SourceID)= " & LeadSource & " ) AND ((tblOrders.OrderDate)"

There needs to be a space after "WHERE"

This SQL Statement works and uses parameters from a form and linked tables:

SELECT badge.[process num], process.name, badge.[time in], badge.[time out], process.[wear date]
FROM badge INNER JOIN process ON (badge.[badge id] = process.[badge id]) AND (badge.[process num] = process.[process num])
WHERE (((badge.[process num])='" & [Forms]![update times form]![pro num] & "'))
ORDER BY process.name;

blong824

HI,
I made all your changes and this is what I have now. It tells me Too few parameters expected and highlights this line
Set Rs = db.OpenRecordset(sql)
Any suggestions now?

Dim db As Database, Rs As DAO.Recordset
Dim sql As String

Select Case Frame2
Case 1
Set db = CurrentDb
sql = "SELECT tblCustomers.FirstName, tblCustomers.LastName, tblCustomers.BillingAddress,"
sql = sql & "tblCustomers.BillingApt, tblCustomers.BillingCity, tblCustomers.BillingState,"
sql = sql & "tblCustomers.BillingZip, tblCustomers.PhoneNumber, tblCustomers.EmailAddress,"
sql = sql & "tblCustomers.ShippingAddress, tblCustomers.ShippingApt, tblCustomers.ShippingCity,"
sql = sql & "tblCustomers.ShippingState, tblCustomers.ShippingZip, tblCustomers.SourceID,"
sql = sql & "tblCustomers.CustomerID, tblOrders.OrderDate, tblOrders.OrderTypeID"
sql = sql & " FROM ((tblCustomers LEFT JOIN tblLeadResponse ON tblCustomers.CustomerID = tblLeadResponse.CustomerID)"
sql = sql & " LEFT JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID)"
sql = sql & " LEFT JOIN tblShipping ON tblOrders.OrderID = tblShipping.OrderID"
sql = sql & " WHERE (((tblCustomers.SourceID)= '" & [LeadSource] & "' ) AND ((tblOrders.OrderDate)"
sql = sql & " Between #" & [BeginDate] & "# And #" & [EndDate] & "#) AND ((tblOrders.[O/I])=O)"
sql = sql & " AND ((tblLeadResponse.NoCall) Is Null) AND ((tblShipping.ShipDate) Is Null))"


Set Rs = db.OpenRecordset(sql)

LeadCount = Rs.RecordCount

Rs.Close
db.Close

Thanks

JimSchor

High,

A way to trouble shoot the SQL is to remove the WHERE and the ORDER portions and start with one field rather than all the fileds you will end up needing. If there is still a problem it is probably in the JOINS.

Also try to build the query in the Access query builder (then view and copy the SQL into your program) then break your SQL statement ONLY where you need to insert data from your form.

I know this is not a direct answer but often the most important help is advice on how to solve the problem yourself. Best of luck ~

blong824

This is what I have:

Dim db As Database, Rs As DAO.Recordset
Dim sql As String

sql = "SELECT tblCustomers.*,* FROM tblCustomers;"

Set Rs = db.OpenRecordset(sql)

LeadCount = Rs.RecordCount

Rs.Close
db.Close

There are 208 records in tblCustomers. LeadCount = 1 after running this. Anyone know why?

Shurik12

Hi,

before doing Rs.RecordCount try to do

rs.MoveLast
rsMoveFirst


Regards,
Shurik12.

pradeep

anita_psari

Hi everybody...
I make a database program using MS Access and VB. To filter the information, I use SQL statement :

SQL = "Select * from TabelQ Where Id_sub = ' " & A & " ' and Row Number between 1 and 696"

It is error, because the field "Row Number" in MS Access consists of 2 word.
I can't change that field name because I got that field name automatic from data server.
How can I use the SQL statement with the field nama that consist of 2 words ("Row Number") ?

Thanks before