asked on

Problem with Between Dates

Hi Folks,
I am have the following:

Private Sub CmdShowTransactions_Click()
DateFrom = TxtDateFrom.Text
DateTo = TxtDateTo.Text

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 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 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 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 [Transaction Date] BETWEEN #" & Format(DateFrom, "dd-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-yyyy") & "# ORDER by 1"

Set GridDayBook.DataSource = _
ExecuteSQL(txtSQL, MsgText)
Text1.Text = txtSQL
-----------------------------------------------------
The query generated is the follwing:
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 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 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 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 [Transaction Date] BETWEEN #01-Mar-2005# AND #31-Mar-2005# ORDER by 1
--------------------------------------------------------
However it does not work, the problem is with the date part of the query as i have tested it without and it works, my region settings are set to ireland. Also i have records saved in my Access DataBase in date format, and they are whitin my date serach criteria.

Any idea's?

Ta,
Dave

ramyhh

WHERE [Transaction Date] BETWEEN #" & Format(DateFrom, "dd-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-yyyy") & "# ORDER by 1"
change it to
WHERE [Date of PaymentIn] BETWEEN #" & Format(DateFrom, "dd-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-yyyy") & "# ORDER by 1"

davemckenna

ASKER

ramyhh,
That didnt work either.

Shauli

Is this an access database? If so, then access database must have a query set by US dates (mm/dd/yyyy), otherwise it doesn't work. See MSDN:

When you specify the criteria argument, date literals must be in U.S. format, even if you're not using the U.S. version of the Microsoft Jet database engine. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States. Be sure to enclose your date literals with the number sign (#) as shown in the following examples.

To find records dated May 10, 1996 in a United Kingdom database, you must use the following SQL statement:

SELECT *
FROM Orders
WHERE ShippedDate = #5/10/96#;

You can also use the DateValue function which is aware of the international settings established by Microsoft Windows. For example, use this code for the United States:

SELECT *
FROM Orders
WHERE ShippedDate = DateValue('5/10/96');

And use this code for the United Kingdom:

SELECT *
FROM Orders
WHERE ShippedDate = DateValue('10/5/96');

S

ASKER CERTIFIED SOLUTION

ramyhh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

virendra_malushte

try using / instead of - in following statements
Format(DateFrom, "dd-mmm-yyyy")

EDDYKT

Try this

change

WHERE [Transaction Date] BETWEEN #01-Mar-2005# AND #31-Mar-2005# ORDER by 1

to

where datediff("s", DateFrom, [Transaction Date]) > 0 and datediff("s", [Transaction Date], DateTo) > 0 order by 1

davemckenna

ASKER

That worked fine ramyhh.
Thanks to all for your help.
Heres my final query which works.

"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-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-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-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-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-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-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-mmm-yyyy") & "# AND #" & _
Format(DateTo, "dd-mmm-yyyy") & "#"