4155

Inserting Weekends into Table with only Weekdays MS Access

Question:

I'm needing to insert the weekends into a table that only has weekdays and then assign the last known value to the weekend values. I know I'm going to need an Insert Query, although I'm still pretty new when it comes to VBA and SQL.

Code so far:

<pre class="lang-vb prettyprint-override">Private Sub btnWeekends_Click() DoCmd.SetWarnings False Dim db as DataBase Dim rs as RecordSet Dim fieldCount as Integer Dim i as Integer set db = CurrentDb set rs = db.OpenRecordSet("Archive", dbOpenDynaset) fieldCount = db.TableDefs("Archive").Fields.Count Dim DateVal As Date DateVal = rs![ValDate] Do While Not rs.EOF i = 0 Do While IsNull(DLookup("ValDate", "Archive", "ValDate=#" & DateAdd("d", 1, ValDate) & "#")) = True rs.AddNew ' removed other fields and edited line below rs![ValDate] = DateVal rs.Update i = i + 1 Loop rs.MoveNext Loop '//-----Clean Up set db = Nothing set rs = Nothing DoCmd.SetWarnings True End Sub

Data that I have(starting on Friday):

<pre class="lang-none prettyprint-override">+------------------------------------------------------------------------+ |ID |Customer Name| Nbr | City |Value of Day|ExtendedNbr| ValDate | +------------------------------------------------------------------------+ |001| Cust1 | 91 | New York | 529 | 91928592 | 1/5/2018 | |002| Cust2 | 87 | Las Vegas| 654 | 85642187 | 1/5/2018 | |003| Cust3 | 45 | Denver | 258 | 78943245 | 1/5/2018 | |004| Cust1 | 91 | New York | 611 | 91928592 | 1/8/2018 | |005| Cust2 | 87 | Las Vegas| 753 | 85642187 | 1/8/2018 | |006| Cust3 | 45 | Denver | 357 | 78943245 | 1/8/2018 | +------------------------------------------------------------------------+ 'ValDate then skips past 1/6/2018 and 1/7/2018 to 1/8/2018

Data that I'm needing:

<pre class="lang-none prettyprint-override">+------------------------------------------------------------------------+ |ID |Customer Name| Nbr | City |Value of Day|ExtendedNbr| ValDate | +------------------------------------------------------------------------+ |001| Cust1 | 91 | New York | 529 | 91928592 | 1/5/2018 | |002| Cust2 | 87 | Las Vegas| 654 | 85642187 | 1/5/2018 | |003| Cust3 | 45 | Denver | 258 | 78943245 | 1/5/2018 | |004| Cust1 | 91 | New York | 529 | 91928592 | 1/6/2018 | |005| Cust2 | 87 | Las Vegas| 654 | 85642187 | 1/6/2018 | |006| Cust3 | 45 | Denver | 258 | 78943245 | 1/6/2018 | |007| Cust1 | 91 | New York | 529 | 91928592 | 1/7/2018 | |008| Cust2 | 87 | Las Vegas| 654 | 85642187 | 1/7/2018 | |009| Cust3 | 45 | Denver | 258 | 78943245 | 1/7/2018 | |010| Cust1 | 91 | New York | 611 | 91928592 | 1/8/2018 | |011| Cust2 | 87 | Las Vegas| 753 | 85642187 | 1/8/2018 | |012| Cust3 | 45 | Denver | 357 | 78943245 | 1/8/2018 | +------------------------------------------------------------------------+ 'I'm needing it to add the Saturday(1/6/2018) and Sunday(1/7/2018) before continuing on to 1/8/2018

Because there is no Value of Day for 1/6/2018 or 1/7/2018, I'm needing to grab the previous day's values and input them into the newly added weekend dates (ex. 1/6/2018 and 1/7/2018) with the matching information as well (Customer Name, Nbr, City, ExtendedNbr).

Thank you in advance for any help/advice you can provide. I'm still new and any chance to learn more is greatly appreciated!

<strong>EDIT1:</strong>

Line rs.Update errors with "Run-Time error '3022': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship..."

<strong>EDIT2:</strong>

The results are input at the end of all the records. Loop is infinite for some reason. The new dates aren't inserted into the table properly (they're all the same dates and not in the correct order).

Answer1:

This is a SQL based solution.

This gets all records where for customer x exists records for Friday + the following Monday but not for Saturday.

<pre class="lang-sql prettyprint-override">SELECT a1.* FROM Archive a1 INNER JOIN Archive a2 ON ( (a1.Nbr = a2.Nbr) AND (a1.ExtendedNbr = a2.ExtendedNbr) AND (a1.ValDate + 3 = a2.ValDate) AND (DatePart("w", a1.ValDate) = 6) ) WHERE NOT EXISTS (SELECT * FROM Archive a3 WHERE a3.Nbr = a1.Nbr AND a3.ValDate = a1.ValDate + 1)

and you can use that to insert the Saturdays:

<pre class="lang-sql prettyprint-override">INSERT INTO Archive ([Customer Name], Nbr, City, [Value of Day], ExtendedNbr, ValDate) SELECT a1.[Customer Name], a1.Nbr, a1.City, a1.[Value of Day], a1.ExtendedNbr, a1.ValDate + 1 FROM Archive a1 INNER JOIN Archive a2 ON ( (a1.Nbr = a2.Nbr) AND (a1.ExtendedNbr = a2.ExtendedNbr) AND (a1.ValDate + 3 = a2.ValDate) AND (DatePart("w", a1.ValDate) = 6) ) WHERE NOT EXISTS (SELECT * FROM Archive a3 WHERE a3.Nbr = a1.Nbr AND a3.ValDate = a1.ValDate + 1)

To insert the Sundays, use the same, but replace + 1 by + 2 in both places.

To insert random single missing days (bank holidays), change a1.ValDate + 3 to a1.ValDate + 2, and remove AND (DatePart("w", a1.ValDate) = 6)

<strong>Edit</strong>

An alternate version if DatePart() inside JOIN gives trouble:

<pre class="lang-sql prettyprint-override">INSERT INTO Archive ([Customer Name], Nbr, City, [Value of Day], ExtendedNbr, ValDate) SELECT a1.[Customer Name], a1.Nbr, a1.City, a1.[Value of Day], a1.ExtendedNbr, a1.ValDate + 1 FROM Archive a1 INNER JOIN Archive a2 ON ( (a1.Nbr = a2.Nbr) AND (a1.ExtendedNbr = a2.ExtendedNbr) AND (a1.ValDate + 3 = a2.ValDate) ) WHERE NOT EXISTS (SELECT * FROM Archive a3 WHERE a3.Nbr = a1.Nbr AND a3.ValDate = a1.ValDate + 1) AND (DatePart("w", a1.ValDate) = 6)

Answer2:

<h2> When entering dates as query criteria, enclose them in pound signs (#) </h2> DoCmd.RunSQL "INSERT INTO Archive SELECT * FROM Archive WHERE ValDate = #" & DateVal & "#"

<strong>Edited 1</strong>

Do While Not rs.EOF If IsNull(DLookup("ValDate", "Archive", "ValDate=#" & DateAdd("d", 1, ValDate) & "#")) = True Then rs.AddNew rs![ValDate] = DateVal rs.Update End If rs.MoveNext Loop

Recommend

  • Changing path to parent directory of active working folder
  • VBA automates Outlook Mail Bug: put 2 strings in the HTMLBody
  • How do I embed the console that my tkinter (.py) file runs with into the GUI of my program [duplicat
  • Flatten json documents in Java
  • Iterating quickly through Outlook appointment items
  • VBA Last Change Method
  • Inserting Weekends into Table with only Weekdays MS Access
  • Access VBA SubForm Not Filtering Other Subform When Embbedded in Navigation Form
  • OLEFormat (unknown member): Invalid Request in PowerPoint
  • Excel VBA: Cannot perform auto search on website
  • Why JQuery AJAX call is not working?
  • Custom Subject line for Outlook
  • How to send a notification to multiple subscribers using web-push package of Node.js?
  • Excel VBA getElementsByTagName() only returning the last input
  • Could Not Find Path Specified: CreateDirectoryW
  • Run a Macro every time sheet is changed
  • How to assign uninstallation password, When we are uninstalling the application
  • internetexplorer.application object raises an “Automation error - unknown interface” error on a give
  • Sending Email from Lotus Notes using Excel and having Attachment & HTML body
  • Cannot vlookup range after name change in VBA
  • Inputting page manually by comma and range negative sign using Regex
  • How to specify current user desktop for DoCmd.TransferText
  • Disabling checkbox selections in VB .NET 2008 Winform Listview
  • <input type=“hidden” functionality issue
  • Form Control Checkbox to Copy and Paste Text from a Different Sheet
  • Cannot copy to the last cell in the range
  • Dynamic programming: Find largest diamond (rhombus)
  • Full calendar business hour constrain on day click event
  • show/hide another combobox, with the help of combobox items
  • Parse RRULE to readable text?
  • Where can in find the locale objects for d3.js for different countries
  • Django Migrations fail during django initialization
  • twisted.internet.error.ConnectError when run scrapy spider
  • Is there a equivalent to JSON.Net in Java? [duplicate]
  • end daemon processes with multiprocessing module
  • Is there any purpose for h2-h6 headings in HTML5?
  • print() is showing quotation marks in results
  • iOS: Detect app start via notification press
  • Can I check if a recipient has an automatic reply before I send an email?
  • formatting the colorbar ticklabels with SymLogNorm normalization in matplotlib