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
Solved

SLQ statement

Posted on 2014-02-18
5
264 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
  • 2
  • 2
5 Comments
 
LVL 35

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 35

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

At the beginning of the year, the IT world was taken hostage by the shareholders of LogMeIn. Their free product, which had been free for ten years, all of the sudden became a "pay" product. Now, I am the first person who will say that software maker…
Like many organizations, your foray into cloud computing may have started with an ancillary or security service, like email spam and virus protection. For some, the first or second step into the cloud was moving email off-premise. For others, a clou…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 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