Solved

Adodc1.CommandType

Posted on 2002-07-10
5
338 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now