Solved

HELP! Access Database insert

Posted on 2014-02-13
15
278 Views
Last Modified: 2014-02-13
Hi,

Can you please tell me what I'm doing wrong.

I want to store the count from each query (COBCount and COBContained)
into the new table called Report_1_MTD_Daily_Containment

I need the user to be prompted to enter the date.

Dim COBCount = (SELECT Count(*) AS [Count]
FROM AUD_COB_IVR_LOG
WHERE (((AUD_COB_IVR_LOG.RECORD_TYPE)='CB') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/6/2014# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/6/2014#)))

Dim COBContained = SELECT Count(*) AS [Count]
FROM AUD_COB_IVR_LOG
WHERE (((AUD_COB_IVR_LOG.RECORD_TYPE)='TE') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/6/2014# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/6/2014#) AND ((AUD_COB_IVR_LOG.data_3) In ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS')));


INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls)
   values ('COB', ,COBCount,COBContained)
 
Thanks,
Lulu
0
Comment
Question by:lulu50
  • 8
  • 7
15 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39856976
Are you trying to do this in VBA or in MSSql ?

Here is how you should do it in Access VBA
   Dim MyDate As Date: MyDate = InputBox("Please enter a date!", "Enter Date", Date)

   Dim COBCount As Long: COBCount = DCount("ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='CB') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDate & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDate & "#)")

   Dim COBContained As Long: COBContained = DCount("ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='TE') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDate & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDate & "#) AND ((AUD_COB_IVR_LOG.data_3) In ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS'))")

   CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls) " & _
         "SELECT 'COB', #" & MyDate & "#, COBCount, COBContained "

Open in new window

0
 

Author Comment

by:lulu50
ID: 39857009
Gozreh,

Thank you for taking the time to help.

The date for COBCount  and COBContained should be the date user enter + 1

say I entered today's date 2/12/2014
the count should be generated between 2/12/2014 and 2/13/2014

it is in VBA
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39857028
Ok, so after the InputBox add    MyDate = MyDate + 1
0
 

Author Comment

by:lulu50
ID: 39857042
Not sure what I'm doing wrong.

Is this how I do the date?
the MyDateTo hightlighted in red (error)


Dim MyDateFrom As Date: MyDateFrom = InputBox("Please enter a date!", "Enter Date", Date)

Dim MyDateTo As Date = MyDateFrom + 1;

   Dim COBCount As Long: COBCount = DCount("UNIQUE_KEY_ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='CB') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDateFrom & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDateTo & "#)")

   Dim COBContained As Long: COBContained = DCount("UNIQUE_KEY_ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='TE') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDateFrom & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDateTo & "#) AND ((AUD_COB_IVR_LOG.data_3) In ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS'))")

   CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls) " & _
         "SELECT 'COB', #" & MyDate & "#, COBCount, COBContained "
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39857071
You should use dim only when declaring new verbal, so your code should be
   Dim MyDate As Date: MyDate = InputBox("Please enter a date!", "Enter Date", Date)
   MyDate = MyDate + 1
   Dim COBCount As Long: COBCount = DCount("UNIQUE_KEY_ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='CB') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDate & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDate & "#)")

   Dim COBContained As Long: COBContained = DCount("UNIQUE_KEY_ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='TE') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDate & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDate & "#) AND ((AUD_COB_IVR_LOG.data_3) In ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS'))")

   CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls) " & _
         "SELECT 'COB', #" & MyDate & "#, COBCount, COBContained "

Open in new window


also in VB you don't need the last ;
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39857090
I'm sorry, i see now you added new date MyDateTo
so your code should be
Dim MyDateFrom As Date: MyDateFrom = InputBox("Please enter a date!", "Enter Date", Date)

Dim MyDateTo As Date: MyDateTo = MyDateFrom + 1

   Dim COBCount As Long: COBCount = DCount("UNIQUE_KEY_ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='CB') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDateFrom & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDateTo & "#)")

   Dim COBContained As Long: COBContained = DCount("UNIQUE_KEY_ID", "AUD_COB_IVR_LOG", _
         "((AUD_COB_IVR_LOG.RECORD_TYPE)='TE') AND ((AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)>=#" & MyDateFrom & "# And (AUD_COB_IVR_LOG.DATE_AND_TIME_STAMP)<=#" & MyDateTo & "#) AND ((AUD_COB_IVR_LOG.data_3) In ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS'))")

   CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls) " & _
         "SELECT 'COB', #" & MyDate & "#, COBCount, COBContained "

Open in new window

0
 

Author Comment

by:lulu50
ID: 39857104
I just have syntax error in the insert

  CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls) " & _
         "SELECT 'COB', #" & MyDate & "#, COBCount, COBContained "
0
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).

 
LVL 10

Expert Comment

by:Gozreh
ID: 39857126
is the Call_Count and Contained_Calls text type fields or number type ?
0
 

Author Comment

by:lulu50
ID: 39857134
they are both number type
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39857150
change the code to
CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,Date,Call_Count,Contained_Calls) " & _
         "SELECT 'COB' AS LOB, #" & MyDate & "# AS Date, " & COBCount & " AS Call_Count, " & COBContained & " AS Contained_Calls"

Open in new window

0
 

Author Comment

by:lulu50
ID: 39857155
Still saying insert syntax error!

Not sure why.
0
 

Author Comment

by:lulu50
ID: 39857202
I am using this syntax:

the error now says that I am using (Too few parameters expected)

I changed the Date into createdDate just incase it is a reserve name

 CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,CreatedDate,Call_Count,Contained_Calls) " & _
         "SELECT 'COB', #" & MyDate & "#, COBCount, COBContained "
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39857209
1) change the MyDate  to MyDateFrom or to MyDateTo or to Date()

2) change the field named Date to other name like "ReportDate"

so your code should be
CurrentDb.Execute "INSERT INTO Report_1_MTD_Daily_Containment (LOB,ReportDate,Call_Count,Contained_Calls) " & _
         "SELECT 'COB' AS LOB, #" & MyDateFrom & "# AS ReportDate, " & COBCount & " AS Call_Count, " & COBContained & " AS Contained_Calls"

Open in new window

0
 

Author Comment

by:lulu50
ID: 39857223
Gozreh,

IT WORKS!!  

GREAT!!

I want to thank you for all your help.

Thank you!!!!!!!
Thank you!!!!!!!
Thank you!!!!!!!
Thank you!!!!!!!
Thank you!!!!!!! plus one million times. ok that's it!!!!
0
 

Author Closing Comment

by:lulu50
ID: 39857225
THANK YOU FOR ONE MILLION PLUS TIME!!!!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

19 Experts available now in Live!

Get 1:1 Help Now