Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

HELP! Access Database insert

Posted on 2014-02-13
15
Medium Priority
?
286 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

971 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