Vba between Filter (1 Viewer)

[giddyhead]

Hi Everyone,

The follow in an example of my code and wanted to find a way to use a record set to send form dates for example dates between 2017-01 and 2017-04 to outlook using only those form selected fields. Is there another way besides using elseif statements. Thanks

"SELECT [CDB LIST].LOCATION, count([CDB LIST].LOCATION) AS [# of Personnel], Sum(IIf([Reason]=""Arrived"",""1"",""0"")) AS Reporting, Sum(IIf([Reason]=""10 Days"",""1"",""0"")) AS [10 Days], Sum(IIf([Reason]=""20 Days"",""1"",""0"")) AS [20 Days], InformationS.[LEAD-Email_Address(H)], InformationS.[LEADA--Email_Address(H)], InformationS.[LEADP-Email_Address(H)-P], InformationS.[LEADP-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-P], InformationS.[UCC-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-T], InformationS.[PERS-Email_Address(H)-P], InformationS.[PERS-Email_Address(H)-A], InformationS.[FULL Pay-Email_Address(H)-P], " & _
"InformationS.[FULL Pay-Email_Address(H)-A], Sum(IIf([Required Date]=""2016-12"",""1"",""0"")) AS [2016-12], Sum(IIf([Required Date]=""2017-01"",""1"",""0"")) AS [2017-01], Sum(IIf([Required Date]=""2017-02"",""1"",""0"")) AS [2017-02], Sum(IIf([Required Date]=""2017-03"",""1"",""0"")) AS [2017-03], Sum(IIf([Required Date]=""2017-04"",""1"",""0"")) AS [2017-04], Sum(IIf([Required Date]=""2017-05"",""1"",""0"")) AS [2017-05], Sum(IIf([Required Date]=""2017-06"",""1"",""0"")) AS [2017-06], Sum(IIf([Required Date]=""2017-07"",""1"",""0"")) AS [2017-07], " & _
"FROM [CDB LIST] LEFT JOIN InformationS ON [CDB LIST].LOCATION = InformationS.[(R)LOCATION] " & vbCrLf & _
"WHERE ((([CDB LIST].[Required Date]) Between '" & [Forms]!CDB_LIST_FORM![Begin-Date] & "' And '" & [Forms]!CDB_LIST_FORM![End-Date] & "') AND (([CDB LIST].Received) <>-1)) " & vbCrLf & _
"GROUP BY [CDB LIST].LOCATION, InformationS.[LEAD-Email_Address(H)], InformationS.[LEADA--Email_Address(H)], InformationS.[LEADP-Email_Address(H)-P], InformationS.[LEADP-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-P], InformationS.[UCC-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-T], InformationS.[PERS-Email_Address(H)-P], InformationS.[PERS-Email_Address(H)-A], InformationS.[FULL Pay-Email_Address(H)-P], InformationS.[FULL Pay-Email_Address(H)-A];"

 

[Uncle Gizmo]

Remove confidential info from your dB and post it here.

Sent from my SM-G925F using Tapatalk

 

[isladogs]

"SELECT [CDB LIST].LOCATION, count([CDB LIST].LOCATION) AS [# of Personnel], Sum(IIf([Reason]=""Arrived"",""1"",""0"")) AS Reporting, Sum(IIf([Reason]=""10 Days"",""1"",""0"")) AS [10 Days], Sum(IIf([Reason]=""20 Days"",""1"",""0"")) AS [20 Days], InformationS.[LEAD-Email_Address(H)], InformationS.[LEADA--Email_Address(H)], InformationS.[LEADP-Email_Address(H)-P], InformationS.[LEADP-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-P], InformationS.[UCC-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-T], InformationS.[PERS-Email_Address(H)-P], InformationS.[PERS-Email_Address(H)-A], InformationS.[FULL Pay-Email_Address(H)-P], " & _
"InformationS.[FULL Pay-Email_Address(H)-A], Sum(IIf([Required Date]=""2016-12"",""1"",""0"")) AS [2016-12], Sum(IIf([Required Date]=""2017-01"",""1"",""0"")) AS [2017-01], Sum(IIf([Required Date]=""2017-02"",""1"",""0"")) AS [2017-02], Sum(IIf([Required Date]=""2017-03"",""1"",""0"")) AS [2017-03], Sum(IIf([Required Date]=""2017-04"",""1"",""0"")) AS [2017-04], Sum(IIf([Required Date]=""2017-05"",""1"",""0"")) AS [2017-05], Sum(IIf([Required Date]=""2017-06"",""1"",""0"")) AS [2017-06], Sum(IIf([Required Date]=""2017-07"",""1"",""0"")) AS [2017-07][COLOR=Red],[/COLOR] " & _
"FROM [CDB LIST] LEFT JOIN InformationS ON [CDB LIST].LOCATION = InformationS.[(R)LOCATION] " & vbCrLf & _
"WHERE ((([CDB LIST].[Required Date]) Between '" & [Forms]!CDB_LIST_FORM![Begin-Date] & "' And '" & [Forms]!CDB_LIST_FORM![End-Date] & "') AND (([CDB LIST].Received) <>-1)) " & vbCrLf & _
"GROUP BY [CDB LIST].LOCATION, InformationS.[LEAD-Email_Address(H)], InformationS.[LEADA--Email_Address(H)], InformationS.[LEADP-Email_Address(H)-P], InformationS.[LEADP-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-P], InformationS.[UCC-Email_Address(H)-A], InformationS.[UCC-Email_Address(H)-T], InformationS.[PERS-Email_Address(H)-P], InformationS.[PERS-Email_Address(H)-A], InformationS.[FULL Pay-Email_Address(H)-P], InformationS.[FULL Pay-Email_Address(H)-A];"

Its difficult to follow a long string of code like this without the context - hence the comment by Uncle Gizmo. However there is at least one error in the code
- remove the comma (marked in RED above ) before the FROM section

 

[giddyhead]

Thanks for the update. The reason I am asking is wanted to use the form between dates for example between 2016-12 and 2017-04 and add them to outlook. To provide more details is adding the fields information from the dates selected within the form to the recordset between rst![Forms]!CDB_LIST_FORM![Begin-Date] and rst![Forms]!CDB_LIST_FORM![End-Date] so the information for those fields will show. Opening and sending emails with static dates work however wanted to see if they was a way to use dynamic data for this. Hope I make it a little clearer. Thanks

 

[Cronk]

2016-12 is not a valid date. If your form controls are date formatted, then you need to put
[CDB LIST].[Required Date]) Between #" & format([Forms]!CDB_LIST_FORM![Begin-Date], "mm/dd/yyyy") & "# And #" & ....

Check the string generated by adding debug.print (your sql string) to the next line.

 

[giddyhead]

Thanks for the update. I get a syntax error Expected: End of Statement. Request your assistance as I am not sure why of the error. Thanks

.Body = Rst![CDB LIST]![Required Date] Between #" & format([Forms]!CDB_LIST_FORM![Begin-Date], "mm/dd/yyyy") & "# And #" & format([Forms]!CDB_LIST_FORM![End-Date] &"#"

 

[isladogs]

Thanks for the update. I get a syntax error Expected: End of Statement. Request your assistance as I am not sure why of the error. Thanks

.Body = [COLOR="Red"]Rst![/COLOR][CDB LIST]![Required Date] Between #" & format([Forms]!CDB_LIST_FORM![Begin-Date], "mm/dd/yyyy") & "# And #" & format([Forms]!CDB_LIST_FORM![End-Date] &"#"

Is this part of an email?

I can't see why you need the 'Rst!' at the start
Try removing it

You don't want [] around Forms!
You could try enclosing the form name in [] brackets.
The rest of the date range code looks OK
So, try

.Body =[CDB LIST]![Required Date] Between #" & format(Forms![CDB_LIST_FORM]![Begin-Date], "mm/dd/yyyy") & "# And #" & format(][Forms![CDB_LIST_FORM]![End-Date] &"#"

In future, I would also recommend you avoid all spaces & underscores in table/form names

 

[giddyhead]

I took out the rst! and the same error came up. It maybe because of the Between function as not sure where I should put it in the .body= area. Thanks

 

[MrHans]

The last format function is opened, but never closed and you don't specify the desired format...

 

[giddyhead]

Thanks for the update. I have it in an email so I can send information to the customers to let them know the number pending per month. In addition thanks for the information will not use spaces or underscores for names. Upon loading code

.Body =[CDB LIST]![Required Date] Between #" & format(Forms![CDB_LIST_FORM]![Begin-Date], "mm/dd/yyyy") & "# And #" & format([Forms![CDB_LIST_FORM]![End-Date]) &"#"

I get a compile error: Expected:end of statement with Between being highlighted. Looking forward for your assistance. Thanks.

 

[isladogs]

Oops.
Mr Hans is right - there were a couple of mistakes in my last post.
1. missing "mm/dd/yyyy" section
2. an extra ] crept in somehow

This now looks correct for the SQL:

[COLOR="Red"].Body[/COLOR]=[CDB LIST]![Required Date] Between #" & Format(Forms![CDB_LIST_FORM]![Begin-Date], "mm/dd/yyyy") & "# And #" & Format([Forms![CDB_LIST_FORM]![End-Date],"mm/dd/yyyy") & "#"

However, the purpose of the .Body part is to assign the text for the email body.
So I'm not sure why you would want to put this info there.

 

[giddyhead]

No worries. I am going to add the appropriate verbiage and other items however just cant quite get over this hill with the syntax. I added the code and when I click off it I get a compile error Expected: end of statement with the Between function being highlighted. Thanks

 

[isladogs]

Try creating a query to include table [CDB LIST] and field [Required Date]
Add the Between dates SQL as criteria

Does it run successfully?

If no, modify the SQL until it does work

If yes, then go ahead with:

adding the appropriate verbiage and other items

 

[giddyhead]

The query was created and works great as it groups, sum numbers and also filters between dates to let me know between which dates how many items were used. Lets say between the first and fifth 4 records were retrieved and sum of records for example date1 7 records, date2 4 records etc for admin supplies and when added to the module it also runs great. It opens up outlook and add the appropriate information. However the concern comes up as I want to find a way to add the records fields based upon the dates selected. For example Field1, Field2, etc and between dates 1-5. In Outlook .Body="Admin used " & field1 or field2, etc &" of brushes "

Basically based upon the dates that are selected it looks for the field names due to being the same name and add the appropriate information from the VBA SQL. Thanks

 

[isladogs]

OK - in order for someone to help you solve this, do what was suggested way back in post #2

Remove confidential info from your dB and post it here.

When you do so, please also give an example of what you want to see in your .body text

 

[Minty]

Create the body as a string outside of the outlook method. Then add that string as you body. This will allow you to debug the string and if you want to later format it with HTML, or maybe even put it in a HTML Table.

 

[giddyhead]

Thank you all for your assistance. What I wanted to see in the body of the email is if if the between strings selected from the form were 2017-01 and 2017-05 it will put the selected values of those fields in the body for example.

In the Body
.Body = It will show the all values between what was selected in the form.

For example if between values 2017-01 and 2017-05 were selected it will show 5 records and the body will show

.Body=rs!Department &" You have " &rs![# Per Department] & " you check out # on " & rs![2017-01] & " and # " rs![2017-02], etc based upon the values selected in the form. For got to add if the .send option does not work what code can be used to all of the send. This is the code been trying to make it work

Do
DoEvents
rctrlrenwnd = FindWindow("rctrl_renwnd32", vbNullString)
afxwndw = FindWindowEx(rctrlrenwnd, 0&, "afxwndw", vbNullString)
afxwndw = FindWindowEx(afxwndw, 0&, "afxwndw", vbNullString)
X = FindWindowEx(afxwndw, 0&, "#32770", vbNullString)
Button = FindWindowEx(X, 0&, "button", vbNullString)
Call SendMessageLong(button, WM_LBUTTONDOWN, 0&, 0&)
Call SendMessageLong(button, WM_LBUTTONUP, 0&, 0&)
Loop Until Button < 0

and when I do msgbox button it always has a number in it even if there is no New Mail window open. Thanks

 

[Minty]

What on earth is that code doing? What's that got to do with sending an email!

Sent from my Nexus 7 using Tapatalk

 

[giddyhead]

It is to click the send button after the Outlook message have been prep.

 

[Minty]

Just use .send with the open outlook object.

I think you should post up the whole sending code. You shouldn't really do the body structure work in the outlook object. create all the elements outside of the mail sending. Once they are correct then plonk them into the final sending routine.

Sent from my Nexus 7 using Tapatalk