Total text between two dates.

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Total text between two dates.

Post by adam »

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.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Total text between two dates.

Post by HansV »

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

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Total text between two dates.

Post by adam »

The text SL in in column "Status". should I not add the column name to the query?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Total text between two dates.

Post by HansV »

Apparently I haven't interpreted your question correctly. Can you explain more clearly what you want?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Total text between two dates.

Post by adam »

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.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Total text between two dates.

Post by HansV »

Does TextBox2 always contain today's date?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Total text between two dates.

Post by adam »

Yeah.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Total text between two dates.

Post by HansV »

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

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Total text between two dates.

Post by adam »

Thankyou so much for the help Hans. It worked really well.
Best Regards,
Adam