Search results

I have a copy on my Google Drive here: https://drive.google.com/open?id=1hU14aG_SdoHt_jEAGck1Xvzfng9Svsf7 It's two files, one is the front-end, other is the back-end. Open, attach back-end via prompts and it's frmRewards. If you have any quesitons, let me know. Thanks in advance for looking...

Same problem, unfortunately :( Private Sub cmdDelete_Click() Dim Response As Integer On Error GoTo cmdDelete_Click_Err DoCmd.SetWarnings False If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then DoCmd.RunCommand acCmdSelectRecord...

Yep, tried that, too. Here's what I tried: Private Sub cmdDelete_Click() Dim Response As Integer On Error GoTo cmdDelete_Click_Err DoCmd.SetWarnings False If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then DoCmd.RunCommand...

I have a form (frmRewards) that has a listbox (lstCustomers) which is used to select customers from a list. When you double-click the customer's name, the rest of the form is populated (no subforms). When I delete the record, I get the infamous #Deleted issue in the listbox. I've tried a...

I see it now, thanks! Is it weird that the old SQL worked just fine?

Hey guys, sorry I haven't been able to get back to this for a few days, been busy around the house. I'll work my way backwards here. @cheekybuddha - The first expression and the second expression are the same, aren't they? Minus the spacing? To answer your question, it works perfectly. I...

Can you think of a way around the issues within tblOptions? I really don't want to hard-set certain aspects of the database (percentage reward, etc.) in case they ever decide to change that in the future. Outside of that and the Calc1 being twice, is it possible? If not, I'll cope with it...

Hey all! @plog has done some amazing work helping me fumble through this SQL mess. Now that he's solved the most pressing issues with my queries, I was hoping I could nab some help with trying to nest them all into one query I can call on demand. Spending roughly ten minutes on this I got...

Yeah, this is something I might need help with. Nesting all these SQL queries into one is a bit more daunting than I thought. I got confused two minutes into it. I'll post another thread. Thanks again, @plog for all the help, and I hope you enjoy your time out of town!

Holy crap @plog, you're on to something here! It's pretty obvious now that the date was being stored with full time included. This might be a wall of SQL text/etc., so bare with me, please. Most of this is for reference purposes and not something you need to delve into. I created a new query...

@plog - What you posted is really close to what I was trying to do myself. The issue here is it's still counting each tbPurchases.PurchaseDate as an individual purchase, and not counting each distinct PurchaseDate as a single item. Here's the output I'm getting, using your three queries, based...

@plog - I forgot I added a new record to the mix when I was playing with the data earlier last week. When I copy/pasted the data over, it messed up the math...again. The record for 2/14/2020 was left out in the math. Sorry. I'll remove it from the example data to clear up the mistake. The...

I'm trying very hard here to provide the requested information. If this is missing, please be specific so I can provide it. Also, I would LOVE this to all be one query (all the queries up to this point) but I can sort this all out after I figure out the Count(*) problem below. I need to count...

Sigh... @plog - First off, you haven't even read what I wrote? Are you kidding me, dude? IF you read what I wrote, you could 100% understand what it is I need. Creeping new aspects into the database? Nope. Had you read ANY of what I wrote, you would understand that this was the requirement...

Ok, let's start over from the beginning. That might make this easier to demonstrate with the data. tbPurchases: ID MemberID PurchaseDate PurchaseAmount 5638 1 2/13/2020 $500.00 5637 1 2/13/2020 $1,000.00 5636 1 2/13/2020 $100.00 5635 1 2/11/2020 $79.99 5634 1 2/6/2020 $85.63...

@plog - I'm curious, why does the data set make this easier on you to create the SQL statement? Giving the data set is a whole lot more information than is actually needed to change Count(*) to Count(*) different dates instead of all the records found. Just trying to get inside your mind and...

Does it produce those proper results in the backend1 I shared? I'm getting a count of ALL purchases and a sum of ALL purchases in the database. I need them filtered. I need the query to return only those results where the PurchaseDate is > Max(Max(IssueDate) OR DateSerial(Year(Now()),1,1)))...

MemberID=1 only has 400(actually 400, not 505 - read below) after Max(tblRewards.IssueDate) and DateSerial(Year(Now()),1,1). I also noticed my math is wrong (again...sigh). Fixing the EXPECTED OUTPUT Pt2 values. MemberID=1 should have a SumOfPurchaseAmount of $400.00. There were only 4...

tblRewards: ID CustomerID IssueDate IssueAmount 1 1 2/2/2020 $100.00 2 2 2/3/2020 $46.63 tbPurchases: ID MemberID PurchaseDate PurchaseAmount 1 1 11/12/2019 $15.00 2 1 11/13/2019 $1500.00 3 1 12/12/2019 $1000.00 4 1 1/22/2020 $5.00 5 1 1/29/2020 $100.00 6 1 2/3/2020...

It's there.