ADO and SQL Statement

[Chris via AccessMonster.com]

Hello all,

I have been using ADO to perform some basic data manipulation in single
tables (Looking for a particular field match and modify another fields value).
I have now moved on to one where I am going to have to match two criteria and
I know that with ADO you can not reference a table in your
rsSomeActiveConnection.Open "tblnamehere",,adOpenKeyset,adLockOptimistic,
AdCmdTable statement, so I will have to use an SQL statement.

I created the SQL statement and then checked it based upon one if I created a
query and everything matches.
I then created a form that will ask for the two criteria, Date and Crew
Number. On this same form I have a button that will run the ADO statement,
fill six(6) variables, open a second form and fill the text boxes on the
second form with the six variable.

Just to make sure I am doing this correctly, after I filled one of the
variable, I have it create a message box and display the variable. When I do
this, instead of it coming up with the correct integer, 17 the value it sends
to the message box is 0. Below you will find my VB, and any help on
something I did wrong or if there is an easier way to do this, please let me
know. If you guys know of a site where I could read up on this that would
also be great to know as well. I appreciate all the help and thank you in
advance!
Thanks, Chris

Here is my VB Form:

Dim rsMyConnection As ADODB.Connection
Dim rsMyLocation As New ADODB.Recordset
Dim mySql As String
Dim SurveyFieldCrewNo As Integer
Dim txtFieldDate As Date
Dim SurveyFieldRequestID01 As Integer

mySql = "SELECT tblSurveyFieldScheduler.*, tblSurveyFieldScheduler.
SurveyFieldCrewNo, tblSurveyFieldScheduler.txtFieldDate " & _
"FROM tblSurveyFieldScheduler " & _
"WHERE tblSurveyFieldScheduler.SurveyFieldCrewNo=" & Me.SurveyFieldCrewNo & "
AND tblSurveyFieldScheduler.txtFieldDate= " & Me.txtFieldDate

Set rsMyConnection = CurrentProject.Connection
rsMyLocation.ActiveConnection = rsMyConnection
rsMyLocation.Open mySql, , adOpenStatic

SurveyFieldRequestID01 = rsMyLocation.Fields(2)

rsMyLocation.Close

MsgBox SurveyFieldRequestID01

End Sub

 

[laudus]

If the value of Me.txtFieldDate is a date (which I expect it is) yo
need to modify your code to say

AND tblSurveyFieldScheduler.txtFieldDate = #" & Me.txtFieldDat
& "#

Any time you are comparing date values you have to enclose the valu
you're comparing to in #'s. Hope this helps

 

[Douglas J. Steele]

Just in case the user's Regional Settings don't have the short date format
set to mm/dd/yyyy, it would be better to use

AND tblSurveyFieldScheduler.txtFieldDate = & Format(Me.txtFieldDate,
"\#mm\/dd\/yyyy\#")

Access will not interpret dd/mm/yyyy dates correctly for the first 12 days
of every month.