0

When i use my code shown below it seems to select all data within the same year rather than within the current month. Is this a potential format issue as my data base is dd/mm/yyyy and if this is the case how would i format my date variables to match. My code is shown below and i have researched this issue and cannot work out its cause so any help would be great.

private void LabelProfit()
{
            //Set date

            DateTime now = DateTime.Now;
            var startDate = new DateTime(now.Year, now.Month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);

            //ERROR WITH BETWEEN STATEMENT

            //Select Income 
            con.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT SUM(Income) FROM Finance WHERE TransactionDate BETWEEN #" + startDate.ToShortDateString() + "# AND #" + endDate.ToShortDateString() + "#");
            cmd.Connection = con;
            decimal Income = decimal.Parse(cmd.ExecuteScalar().ToString());
            
            //Select Expenditure
            OleDbCommand cmd2 = new OleDbCommand("SELECT SUM(Expenditure) FROM Finance WHERE TransactionDate BETWEEN #" + startDate.ToShortDateString() + "# AND #" + endDate.ToShortDateString() + "#");           
            cmd2.Connection = con;
            decimal Expenditure = decimal.Parse(cmd2.ExecuteScalar().ToString());
            con.Close();


            // Creates Output for label
            decimal sum = Income - Expenditure;
            string output = $"{sum:C2}";
            lblProfitLoss.Text = output.ToString();
         
}
godot
  • 3,422
  • 6
  • 25
  • 42
Ben Maclean
  • 1
  • 1
  • This is a classical problem in string concatenation when building SQL Queries. You are not writing the date as access expects them. You should never use this approach (string concatenation) but you should use parameterized queries – Steve Dec 10 '20 at 13:22
  • Access uses `mm/dd/yyyy` format when between `#`. And I am guessing you are in a country where the date format is _not_ mm/dd/yyyy. – mjwills Dec 10 '20 at 13:28
  • Also, `BETWEEN` likely doesn't handle _times_ on the last day of the month very well either. What your query _should_ do is >= 1st day of the month and < 1st day of _next_ month. – mjwills Dec 10 '20 at 13:29
  • @mjwills my access db shows the date in dd/mm/yyyy im so confused – Ben Maclean Dec 10 '20 at 14:15
  • Are you storing dates in the database as dates or as strings? – Lasse V. Karlsen Dec 10 '20 at 21:06

0 Answers0