Recent content by HeelNGville

Thanks for the reply DB Guy and plog. -Plog, regarding query 2, once the inventory reaches -0-, there is no record found in the source. Example in reply above to arnelgp. If a product retains inventory on dates that are consecutive from beginning period to current date, then great, the MIN...

The inventory source table refreshes daily, and retains a snapshot of the inventory values for each refresh date. If I use the max() on the inventory_dt the results returned will be the current snapshot date of inventory. Example. Date Product On Hand Qty 4/7/2020 ABC...

Preface this with thanks for any assistance in resolving what I hope is a relatively easy fix. So, I have a table (sellable_prod_inv) with products (column_name: product_id) and inventory date (column_name: inventory_dt (mm/dd/yyyy), which contains a snapshot of inventory values by day, dating...

Thanks, that is not the actual named value, just an alias used in this post. Appreciate the suggestion though.

Thanks DBGuy! Based on your suggestion, I put together the following, which appears to be working. Would you confirm that I have correct syntax here? Private Sub Form_Current() If Day(Date) = 1 Then Me.txtClick1 = DateAdd("m", -1, DateSerial(Year(Date), Month(Date), 1)) 'if 1st of month...

I understand how to retrieve the first day of the current month and previous month. The challenge I am having is, when the calendar date is on the 1st of each month (i.e. 4/1/2020), I need to revert to the first day of the previous month. So, on my form, I have a date picker calendar, with the...

Thanks for the reply. I get that. However, how do I get that value to default as my date picker .txtClick1 (beginning date) when calendar day of month number = 1. Basically below gives me the 1st day of the previous month, however only need that value to appear when the DAY value = 1 So if...

Preface this with thanks for any assistance in resolving or guidance, as I am sure this is simple but my VBA is limited at best. Basically, I have a DB form with a calendar date picker for a user to select a beginning date (txtClick1) and ending date (txtClick2), with the on current properties...

The challenge (for me anyway) is the fact that I am having to use 2 separate date columns in my where clause and grouping by a date, which is driven by those date fields. So, all the records are stored in a single table (sales), and within that table are 2 separate date columns: 1. sale_day...

Thanks Wayne, I will give that a try and see how it works. I am running the queries in MS Access via pass through against an Oracle database. For me, this would be a non issue if the return date and ship date values were stored in a single column, but with both values stored in separate...

If possible, the goal is to attempt to reduce the query count from 2 to 1, in lieu of increasing from 2 to 5 and muddying the waters further. Thanks for the suggestion though.

I do not believe another query utilizing an inner join by day would work, as I could have prd_ID with return activity in the returns query, yet no sales activity (and vice versa). There are 3 possible results: 1. prd_ID with sales activity and no return activity 2. prd_ID with sales and return...

I preface this with many thanks for assistance. High level, I have a table source (sales) that houses both sale and return activity. I have 2 separate queries (a)qry_sales to retrieve sales and (b) qry_ret to retrieve returns by day and product the current year and previous yeard and aggregating...

Many thanks! Had to modify slightly from: strDestFile = "T:\Planning\" & rst(1) & ".xlsx" to strDestFile = "T\Planning\" & rst(0) & "-" & rst(1) & ".xlsx" Worked like a charm. Thanks so much!

Ok, let me backup as I may have done poor job of explaining. Load File.xlsx is nothing more than template for the code to dump data from tbl_test, then renames the file based on the contents of row 1 & column 1 “-“ & column 3 of tbl_test, then saves the file and quits MS Excel application. So...