Member Avatar for omi4u

I am working on my company's ledger and my accounts department need a search ledger by COA id with from date to date.

I am able to find the record from date to date but when I write the code for COA ID, then it ignore the date formula.

Below is my complete code that I tried

 {ledgerdetails.date} in {?datef}to{?dateto}

       and 
       {ledgerdetails.Accountnamedr} = {?tran} or 
      {ledgerdetails.accountnamecr}={?tran}

I also tried 

{ledgerdetails.date} >= {?datef}

         and

        {ledgerdetails.date} <= {?dateto}

For the backend, I'm using vb.net and MS SQL.

  • 1 Contributor
  • 1 Reply
  • 1K Views
  • 45 Minutes Discussion Span
  • Latest Post Latest Post by omi4u

Dani AI

Generated

This thread was later marked solved by . Common causes when adding an account/ID filter makes a date range appear ignored are logical grouping, parameter typing, and the time component of datetime values. Missing parentheses around OR clauses can let rows bypass the date test. Passing dates as strings (or sending DateTime values that include a time portion) can cause unexpected exclusions on the end date. Ensuring parameters are actual Date/DateTime types and grouping the account checks so the date range is ANDed with the whole account condition addresses the majority of cases.

Practical VB.NET + T-SQL pattern that avoids end-of-day issues and uses parameterized queries:

Dim sql As String = "SELECT * FROM ledgerdetails WHERE LedgerDate >= @DateFrom AND LedgerDate < DATEADD(day, 1, @DateTo) AND (@Tran = AccountNameDr OR @Tran = AccountNameCr)"
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.Add("@DateFrom", SqlDbType.Date).Value = dateFrom.Date
cmd.Parameters.Add("@DateTo", SqlDbType.Date).Value = dateTo.Date
cmd.Parameters.Add("@Tran", SqlDbType.VarChar, 50).Value = tran

Quick checklist: add parentheses around OR account tests so the date applies to both sides; use an exclusive upper bound (less than next day) instead of <= the same date; confirm report parameters are typed as Date/DateTime and that the code passes DateTime values; inspect the report's generated SQL or stored procedure to verify the filters are applied as intended.

Member Avatar for omi4u

problem solved

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.