Solved

SLQ statement

Posted on 2014-02-18
5
266 Views
Last Modified: 2014-02-18
Hi,

I have an error that says ODBC -Call failed.   (Thanks for your help)

Not sure what is that mean.


Dim MessageBox As String
Dim FromDate As Date: FromDate = InputBox("Please enter a date!", "Enter Date", Date - 1)
Dim ToDate As Date
Dim strSQL As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

ToDate = FromDate + 1

 Dim DailyCount As Long: DailyCount = DCount("ID", "Report_3_MTD_MP_Count", _
         "((Report_3_MTD_MP_Count.CreatedDate)=#" & FromDate & "#)")

If [DailyCount] = 0 Then

strSQL = (" SELECT Count(*) AS [Count] " & _
"from AUD_MOS_IVR_LOG " & _
"WHERE ((((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=" & Format(FromDate, "MM/dd/yyyy") & " And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=" & Format(ToDate, "MM/dd/yyyy") & ")) AND ((AUD_MOS_IVR_LOG.unique_key_id) In (select unique_key_id " & _
"    from AUD_MOS_IVR_LOG where " & _
"(((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=" & Format(FromDate, "MM/dd/yyyy") & " And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=" & Format(ToDate, "MM/dd/yyyy") & ")) " & _
 "And AUD_MOS_IVR_LOG.record_type = 'ME' And " & _
"AUD_MOS_IVR_LOG.data_1 = '1241_AskLOB_MPSERS_YesNo_DM' " & _
" )) AND ((AUD_MOS_IVR_LOG.record_type)='TE') AND ((AUD_MOS_IVR_LOG.DATA_3)='HB' Or (AUD_MOS_IVR_LOG.DATA_3)='MP'));")

rs.Open strSQL, CurrentProject.Connection

  CurrentDb.Execute "INSERT INTO [Report_3_MTD_MP_Count] ([Termination_Event], [CreatedDate], [Call_Count])" _
    & "VALUES ('MP', #" & MyDate & "#, '" & rs.Fields("Count").Value & "');"

rs.Close
Set rs = Nothing

End If
0
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 39867925
It is impossible to debug SQL strings in code which is why I rarely use them.  Put a stop in the code on the open statement.  Then print the sql string to the debug window and copy it and paste it into a new query.  You will frequently get better error messages.
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 39867928
lulu50,

I don't see the code for where you opened the connection (CurrentProject.Connection), or it didn't open correctly.  Did you check to see if it connected properly?

Best regards,

AielloJ
0
 

Author Comment

by:lulu50
ID: 39868120
strSQL = "SELECT Count(*) AS [Count]" & _
" from AUD_MOS_IVR_LOG" & _
" WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>='" & FromDate & "' And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<='" & ToDate & "') AND ((AUD_MOS_IVR_LOG.unique_key_id) In (select unique_key_id" & _
" from AUD_MOS_IVR_LOG where " & _
" ((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & FromDate & "# And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & ToDate & "#)" & _
" And AUD_MOS_IVR_LOG.record_type = 'ME' And" & _
" AUD_MOS_IVR_LOG.data_1 = '1241_AskLOB_MPSERS_YesNo_DM'" & _
" )) AND ((AUD_MOS_IVR_LOG.record_type)='TE') AND ((AUD_MOS_IVR_LOG.DATA_3)='HB' Or (AUD_MOS_IVR_LOG.DATA_3)='MP'));"

My error says

Data type mismatch in criteria expression
right after this " from AUD_MOS_IVR_LOG where " & _

the date type FromDate is a mismatch.  
How can it be?
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39868323
You seem to have date fields enclosed in single quotes in some cases.

Some unsolicited advice.

1. Use DAO rather than ADO since ADO is being deprecated.
2. Use querydefs rather then embedded SQL strings.  If you use querydefs AND the date criteria values are coming from form fields that are defined as dates, you don't need to enclose the date in pound signs.  You only have to do that when the date is a string.  Building the SQL in code makes everything a string.

In a querydef you would use
Where SomeDate = Forms!yourform!SomeDate

But when you are building a string you have to worry about the date delimiter:
strSQL =  " Where SomeDate = #" & Me.SomeDate & "#"

I know  you are new to this so it is confusing.  It is confusing to people who are not so new to programming.
0
 

Author Closing Comment

by:lulu50
ID: 39868545
Thank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Let’s list some of the technologies that enable smooth teleworking. 
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

726 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