Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Problem with Date Query

Hi Guys,
I've just been on getting this sorted a couple of minutes ago!!
Anyway i thought it worked but the following will return results regardless of the month and year, in other words it will return records whithin a the days only,  even if they have a different month or year. Help!!!

Dave


txtSQL = "SELECT [Date of PaymentIn] AS [Transaction Date], [Account Name] AS [Account], PaymentNumberIn AS [Payment Number], PaymentDescription AS [Payment Description], [Net Amount] AS [Credit], 0 AS [Debit] From PaymentsIn WHERE [Date of PaymentIn] BETWEEN #" & Format(DateFrom, "dd-mm-yyyy") & "# AND #" & _
                      Format(DateTo, "dd-mm-yyyy") & "# Union SELECT [Date of PaymentOut] AS [Transaction Date], [Account Name] AS [Account], PaymentNumberOut AS [Payment Number], PaymentDescription AS [Payment Description], 0 AS [Net Amount], [Net Amount] AS [Debit] From PaymentsOut WHERE [Date of PaymentOut] BETWEEN #" & Format(DateFrom, "dd-mm-yyyy") & "# AND #" & _
                      Format(DateTo, "dd-mm-yyyy") & "# Union SELECT [Invoice Date] AS [Transaction Date], [Invoice To Name] AS [Account], [Invoice Number] AS [Payment Number], 0 AS [Payment Description], [Net Amount] AS [Credit], 0 AS [Debit] From Invoices WHERE [Invoice Date] BETWEEN #" & Format(DateFrom, "dd-mm-yyyy") & "# AND #" & _
                      Format(DateTo, "dd-mm-yyyy") & "# Union SELECT [Purchase Invoice Date] AS [Transaction Date], [CreditorName] AS [Account], [Purchase Invoice Number] AS [Payment Number], 0 AS [Purchase Payment Description], 0 AS [Credit], [Purchase Invoice Net Amount] AS [Debit] From PurchaseInvoice WHERE [Purchase Invoice Date] BETWEEN #" & Format(DateFrom, "dd-mm-yyyy") & "# AND #" & _
                      Format(DateTo, "dd-mm-yyyy") & "#"

0
davemckenna
Asked:
davemckenna
  • 2
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
When sending dates in a SQL query, it often helps to send in this format:

Format(DateTo, "yyyy-mm-dd")

Otherwise, confusion may exists because dates are interpreted as mm/dd/yy first if possible.
0
 
davemckennaAuthor Commented:
I have changed the order in which they are sent but the same result, it will filter between the day (number) but not the month or year.

0
 
Éric MoreauSenior .Net ConsultantCommented:
Your database is Access ?

Can you paste the content of txtSQL here ?

Also remove all unions while debugging. If we get the first query running, we will add the unions after that.
0
 
davemckennaAuthor Commented:
emoreau,
Thanks for your help, silly me i had my date fileds in 2 tables formatted as text!!! I did it as an attempt to debug earlier on, problem seems to be solved now.

Ta
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now