Solved

# Problem with Between Dates

Posted on 2005-04-14
Medium Priority
221 Views
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
0
Question by:davemckenna

LVL 2

Expert Comment

ID: 13786346
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"

0

Author Comment

ID: 13786447
ramyhh,
That didnt work either.
0

LVL 19

Expert Comment

ID: 13786613
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
0

LVL 2

Accepted Solution

ramyhh earned 2000 total points
ID: 13786650
put the where statement after each "From table" statement and use the field name instead of the alias
0

LVL 1

Expert Comment

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

LVL 26

Expert Comment

ID: 13789760
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
0

Author Comment

ID: 13792481
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") & "#"
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
###### Suggested Courses
Course of the Month14 days, 22 hours left to enroll