Solved

HELP! Access Database insert

Posted on 2014-02-13
15
279 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

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,…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

18 Experts available now in Live!

Get 1:1 Help Now