Solved

SLQ statement

Posted on 2014-02-18
5
260 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 34

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 34

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Remote Desktop Shadowing often has a lot of benefits. When helping end users determine problems, it is much easier to see what is going on, what is being slecected and what is being clicked on. While the industry has many products to help with this,…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

12 Experts available now in Live!

Get 1:1 Help Now