HELP! Access Database insert

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
lulu50Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GozrehConnect With a Mentor Commented:
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
 
GozrehCommented:
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
 
lulu50Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
GozrehCommented:
Ok, so after the InputBox add    MyDate = MyDate + 1
0
 
lulu50Author Commented:
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
 
GozrehCommented:
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
 
GozrehCommented:
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
 
lulu50Author Commented:
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
 
GozrehCommented:
is the Call_Count and Contained_Calls text type fields or number type ?
0
 
lulu50Author Commented:
they are both number type
0
 
GozrehCommented:
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
 
lulu50Author Commented:
Still saying insert syntax error!

Not sure why.
0
 
lulu50Author Commented:
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
 
lulu50Author Commented:
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
 
lulu50Author Commented:
THANK YOU FOR ONE MILLION PLUS TIME!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.