Adodc1.CommandType

Hello.

I am new to Visual Basic and I am trying to write a simple program.  I have an Access Database that I have set up as an ODBC connection usign the ADODC form.  When I draw the text boxes I see the data and I can scroll through it using the ADODC buttons.

However, I wanted to set something up so it could ask a user what date they needed and only display that data.  Below is my code:Private Sub cmdgetdata_Click()
Dim dtDate As Date
Dim sSQL As String


dtDate = InputBox("Enter Date Needed")
sSQL = "Select * from MainData"

sSQL = sSQL & " WHERE Date = '" & dtDate & "'"

Adodc1.CommandType = adCmdText
Adodc1.RecordSource = sSQL
Adodc1.Refresh

End Sub

But when it runs it gets to the Adodc1.CommandType = adCMdTxt and errors - saying missing an object.

I can't figure this out although I am sure it is pretty easy.  

Thanks.

LVL 1
nnaxorAsked:
Who is Participating?
 
priya_pbkConnect With a Mentor Commented:
aikimark is right, use "#" in access.

Also small change to sSql statement(use >=) like this

sSQL = "Select * from MainData"

sSQL = sSQL & " WHERE Date >= #" & format(dtDate,"dd-mmm-yy") & "#"

-----------------------------------
or use a between clause. coz using equal to retreive a date, does'nt return any records. this way

sSQL = sSQL & " WHERE Date between #" & format(dtDate,"dd-mmm-yy") & "# and #" & format(dtdate,"dd-mmm-yy") & "#"


Also I would suggest you to use dtpicker or a Monthview control so that there would be no confusion as to which part of the date is month part and which is day part.

I tried out yr code, at least did'nt give me error at the line you said!

-priya

0
 
aikimarkCommented:
you've spelled the command type two different ways:
adCmdText
adCMdTxt

That might be the source of your error.
0
 
priya_pbkCommented:
try this:

sSQL = "Select * from MainData"

sSQL = sSQL & " WHERE Date = '" & format(dtDate,"dd-mmm-yy") & "'"

Hope this helps

If still it gives an error check at what line you get the error!!

-priya
0
 
aikimarkCommented:
if the SQL is your problem, I'd recommend:
sSQL = sSQL & " WHERE Date = #" & dtDate & "#"
0
 
priya_pbkCommented:
sorry this way..

sSQL = sSQL & " WHERE Date between #" & format(dtDate,"dd-mmm-yy") & "# and #" & format(dtdate+1,"dd-mmm-yy") & "#"


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.