Solved

SLQ statement

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Launch app from Access custom ribbon 8 33
Top 1 of each supplier 55 55
DSN-LESS connection to MS Access database 6 29
Field naming convntions - MS Access 7 26
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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