I want to create a sql query for my excel vba that would total the text "SL" between the last 2nd of June and today.
For example;
Textbox1 has date as 06/02
Textbox2 has 07/31/2021
When I run the query the total of SL between these two dates will be displayed in Textbox3.
Any help on this would be kindly appreciated.
Total text between two dates.
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Total text between two dates.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total text between two dates.
You'd don;t provide sufficient relevant information, so I can only provide vague help. You'd need to open a recordset based on a SQL string like this:
Code: Select all
strSQL = "SELECT Sum([SL]) AS Total FROM [MyTable] WHERE [DateField] Between #" & _
Format(Me.TextBox1, "mm/dd/yyyy") & "# And #" & Format(Me.TextBox2, "mm/dd/yyyy") & "#"
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total text between two dates.
The text SL in in column "Status". should I not add the column name to the query?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total text between two dates.
Apparently I haven't interpreted your question correctly. Can you explain more clearly what you want?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total text between two dates.
I want to count the total number of records that has "SL" in column "status" between dates in textbox1 and textbox2.
The date in textbox1 should be formatted as "mm/dd" only where the date in textbox2 is formatted as "mm/dd/yyyy".
Let's say for an example;
The user writes 06/02 in textbox1 and if the access table date column has 06/02/2020 and 06/02/2021 I want the code to count from 06/02/2021 and todays date in textbox2 where then the code would count/sumup or total the text "SL" based on that date and todays date.
I hope I've made the question clear.
The date in textbox1 should be formatted as "mm/dd" only where the date in textbox2 is formatted as "mm/dd/yyyy".
Let's say for an example;
The user writes 06/02 in textbox1 and if the access table date column has 06/02/2020 and 06/02/2021 I want the code to count from 06/02/2021 and todays date in textbox2 where then the code would count/sumup or total the text "SL" based on that date and todays date.
I hope I've made the question clear.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Total text between two dates.
Try
Code: Select all
strSQL = "SELECT Count(*) AS Total FROM [MyTable] WHERE [Status]='SL' AND ([DateField] Between #" & _
Me.TextBox1 & "/" & Year(Date) & "# And Date())"
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Total text between two dates.
Thankyou so much for the help Hans. It worked really well.
Best Regards,
Adam
Adam