Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SLQ statement

Posted on 2014-02-18
5
Medium Priority
?
271 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 40

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 40

Accepted Solution

by:
PatHartman earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

564 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