Solved

HELP! Access Database insert

Posted on 2014-02-13
15
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

738 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