Solved

Adodc1.CommandType

Posted on 2002-07-10
5
348 Views
Last Modified: 2010-05-02
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.

0
Comment
Question by:nnaxor
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 7144931
you've spelled the command type two different ways:
adCmdText
adCMdTxt

That might be the source of your error.
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7145969
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
 
LVL 45

Expert Comment

by:aikimark
ID: 7146042
if the SQL is your problem, I'd recommend:
sSQL = sSQL & " WHERE Date = #" & dtDate & "#"
0
 
LVL 2

Accepted Solution

by:
priya_pbk earned 50 total points
ID: 7146072
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
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7146074
sorry this way..

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


0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
add text to end of existing text in file 16 70
SSRS expression Issue finding a string 10 79
passing parameter in sql procedure 9 63
Copy a row 12 62
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question