Button to alter calculation

[mreference]

I have a unbound field in my form footer that calculates the qty of products sold in 30 days.

=Sum(IIf(Left([Ord_Ref_Own],1)>"J" And [Ord_Date]>DateAdd("d",[B][COLOR=Red]-30[/COLOR][/B],Date()),[Qty],0))

Instead of this I want to creat a series of small command buttons for 7 days, 14 days, 30 days etc

How do I assign a number to a command button, so that then when the user clicks one of the buttons it will change the formula above and display the new quantity.

Also, if no button is pressed, I want it to default to 0.
------------------------------------------------------------------------------------------
I did try creating an unbound box, allowing the user to input the number of days, on clicking the button it converted the days to negative figure. I then tried to assign the field to the code above but it came back with #Error in all my other calculated fields.

 

[pr2-eugin]

Try making the control unbound with no data.. Then in the buttons just put the appropriate value..

Private Sub sevenBtn_Click()
    footerTxtBox = Sum(IIf(Left([Ord_Ref_Own],1)>"J" And [Ord_Date]>DateAdd("d",-7,Date()),[Qty],0))
End Sub

Private Sub fourteenBtn_Click()
    footerTxtBox = Sum(IIf(Left([Ord_Ref_Own],1)>"J" And [Ord_Date]>DateAdd("d",-14,Date()),[Qty],0))
End Sub

Private Sub thirtyBtn_Click()
    footerTxtBox = Sum(IIf(Left([Ord_Ref_Own],1)>"J" And [Ord_Date]>DateAdd("d",-30,Date()),[Qty],0))
End Sub

 

[mreference]

Thanks, I created an unbound box called ebayTotal in the footer and a command button called sevenBtn

I then added the following

Private Sub sevenBtn_Click()
    ebayTotal = Sum(IIf(Left([Ord_Ref_Own], 1) = "E" And [Ord_Date] > DateAdd("d", -7,[B][COLOR=Red] Date[/COLOR][/B]), [Qty], 0))
End Sub

It failed when I clicked the button and highlighted over the SUM part of the expression.

What I noticed is that the Date (highlighted in red) would not accept Date() as in my original query, it kept changing back to Date.

 

[boblarson]

Thanks, I created an unbound box called ebayTotal in the footer and a command button called sevenBtn

I then added the following

Private Sub sevenBtn_Click()
    ebayTotal = Sum(IIf(Left([Ord_Ref_Own], 1) = "E" And [Ord_Date] > DateAdd("d", -7,[B][COLOR=red] Date[/COLOR][/B]), [Qty], 0))
End Sub

It failed when I clicked the button and highlighted over the SUM part of the expression.

What I noticed is that the Date (highlighted in red) would not accept Date() as in my original query, it kept changing back to Date.

Don't use a Sum(IIf... in code. Use a DSum instead.

Create this function in your form's module:

Function PlaceTotalForDays(intNumDays) 
   Me.ebayTotal = DSum("Qty", "TableNameHere", "Left([Ord_Ref_Own],1='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))
End Function

Then call it from each button

Private Sub sevenBtn_Click()
     PlaceTotalForDays 7
End Sub
 
Private Sub fourteenBtn_Click()
     PlaceTotalForDays 14
End Sub
 
Private Sub thirtyBtn_Click()
     PlaceTotalForDays 30
End Sub

What I noticed is that the Date (highlighted in red) would not accept Date() as in my original query, it kept changing back to Date.

Yes, the () is not used in VBA.

 

[mreference]

Thanks Bob, I followed your instructions, it did say that a ) or ] was missing, so I filled it in, I think I put the ) in the right place.

Me.ebayTotal = DSum("Qty", "[B][COLOR=Red]TableNameHere[/COLOR][/B]", "Left([Ord_Ref_Own],1[B][COLOR=Red])[/COLOR][/B]='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "[B][COLOR=Red]\#dd\/mm\/yyyy\[/COLOR][/B]#"))

The function I created was placed in the VBA for the subform in which my data is stored, and the TableNameHere was the query used in the Record Source for the subform.

I also changed the date format around for the UK.

Having done all that, no errors occur on pressing one of the buttons, but the text box remains Blank.

Any ideas, have I missed something?

 

[pr2-eugin]

No do not change the Format to UK.. Leave it as Bob has give you.. This is not to do anything with the Format you give, it is how the dates are stored in a JET Database.. This is a known International Date Formatting problem.. So just try the following..

Me.ebayTotal = DSum("Qty", "TableNameHere", "Left([Ord_Ref_Own],1)='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))

 

[mreference]

Oops, I have reverted back to the date. Thanks!

The form still displays a blank box, did the closing ) go in the right place.

 

[pr2-eugin]

Yes the bracket you have placed is right.. although the number you are passing might be wrong.. you need to pass -7, -14 and -30.. So.. just adding a negative would solve it.. Expanding on Bob's code..

Private Sub sevenBtn_Click()
     PlaceTotalForDays -7
End Sub
 
Private Sub fourteenBtn_Click()
     PlaceTotalForDays -14
End Sub
 
Private Sub thirtyBtn_Click()
     PlaceTotalForDays -30
End Sub

Function PlaceTotalForDays(intNumDays) 
    Me.ebayTotal = DSum("Qty", "TableNameHere", "Left([Ord_Ref_Own],1)='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))
End Function

 

[gemma-the-husky]

rather than command buttons, I would either

1. have a textbox where the user can enter whatever number of days he wanted
2. have an option group with radio buttons to permit him to choose the active setting.

I would probably also give him a way of saving his preferred setting, probably in the registry (savesetting) - then each user can have an individual setting.

in thre "process" button, you need a bit of code to read the text box, or active option group value.

 

[mreference]

Hi Paul, that worked, we now have totals.

However, they seem to way off the mark. I think what has happened is that the subform is filtered on one product that the user selects in the parent form.

The code supplied by Bob now totals the Qty for all sales rather than just the filtered item.

This may also be causing the query to take an age to calculate rather than being instant.

Can I filter the code somewhere to only display the correct item?

 

[pr2-eugin]

Add that condition to the Criteria of the DSum..

DSum("Qty", "TableNameHere", "[COLOR=Red][[COLOR=Blue]theFieldName[/COLOR]] = Me.Parent![COLOR=Blue]ControlName[/COLOR] AND[/COLOR] Left([Ord_Ref_Own],1)='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))

Changing the blue bits..

 

[mreference]

I have tried numerous variations of what to include but each time is keep saying that this part of the code "Me.Parent!ControlName" creates a run time error 2471

The FieldName I'm guessing is the control on the subform
The Me.Parent!ControlName is the name of the field on the main form?
The sub form is one of four on the page and I have used

Me.Parent!ControlName
Me.Parent.Parent!ControlName
Me.Parent.Parent.Parent!ControlName
Me.Parent.Parent.Parent.Parent!ControlName

Confused dot com

 

[pr2-eugin]

theFieldName - Field in the TableNameHere not the Control in any of the Forms..

Me.Parent - This calls for the Parent Form, where the control for filtering sits.. You do not have to change the syntax.. Unless the Form Footer is on the Parent Form itself.. Then you would just use Me.ControlName

Also make sure proper concatenation is passed
* If it is String..

DSum("Qty", "TableNameHere", "[COLOR=Red][[/COLOR][COLOR=Blue]theFieldName[/COLOR][COLOR=Red]] =[/COLOR] '" & [COLOR=Red]Me.Parent![/COLOR][COLOR=Blue]ControlName[/COLOR] & "' AND Left([Ord_Ref_Own],1)='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))

* If it is Number..

DSum("Qty", "TableNameHere", "[COLOR=Red][[/COLOR][COLOR=Blue]theFieldName[/COLOR][COLOR=Red]] =[/COLOR] " & [COLOR=Red]Me.Parent![/COLOR][COLOR=Blue]ControlName[/COLOR] & " AND Left([Ord_Ref_Own],1)='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))

* If it is Date..

DSum("Qty", "TableNameHere", "[COLOR=Red][[/COLOR][COLOR=Blue]theFieldName[/COLOR][COLOR=Red]] =[/COLOR] #" & [COLOR=Red]Me.Parent![/COLOR][COLOR=Blue]ControlName[/COLOR] & "# AND Left([Ord_Ref_Own],1)='E' And [Ord_Date] >" & Format(DateAdd("d", intNumDays, Date), "\#mm\/dd\/yyyy\#"))

 

[mreference]

Genuis!

Works a charm and boss very happy... Thank you Paul & Bob

 

[pr2-eugin]

Glad to have helped..