Date() function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date() function

Author

Topic

Nigelsd
Starting Member

14 Posts

Posted - 2006-04-20 : 16:42:48

Hi all, it's me again.

I'm trying to implement a query that will show all records from three months prior to a certain date (that the user will input) and that date.

This is what I wrote:

SELECT Transactions.Date, Transactions.Details,
FROM Transactions
WHERE Transactions.DATE Between [Forms]![FormNAME]![Date] And DateAdd("M",-3,[Forms]![FormNAME]![Date]);

The idea is that the user is presented with a form which has a single text box to input the date. Then he/she clicks a button (which runs the above query) and the list is presented.

However, when I try to run it, I get back ALL records before a certain date...not just for the three months prior to it.

What am I doing wrong?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-20 : 16:47:44

Interchange ur 2 dates in the Between

ie write it as
Between DateAdd("M",-3,[Forms]![FormNAME]![Date] AND [Forms]![FormNAME]![Date]

Srinika

Nigelsd
Starting Member

14 Posts

Posted - 2006-04-20 : 17:29:00

Hmm...now the result is even weirder. Here's my exact query:

SELECT [Transactions].[Date], [Transactions].[Share ID], [Transactions].[Transaction Type], [Transactions].[Quantity], [Transactions].[Unit Price]
FROM Transactions

WHERE [Transactions].[DATE] Between DateAdd("M",-3,[Forms]![Share Movement Over Three Months]![Today's Date]) And [Forms]![Share Movement Over Three Months]![Today's Date]

And [Transactions].[Transaction Type]="Share Purchase"
Or [Transactions].[Transaction Type]="Share Sales";

I tried two dates, one on which a purchase was made and another on which a sale was made.

When I try the purchase date, it returns ALL share transactions (both purchase and sales) even after the specified date.
When I try the sale date, it returns all sales transactions even after the specified date.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 20:45:28

Can you post your table structure and some sample data

Nigelsd
Starting Member

14 Posts

Posted - 2006-04-21 : 03:03:30

Transactions {Transaction ID#, Date, Unit Price, Amount, Transaction Type, Share ID}

Some samles:


001  02/02/04  $30  20  Share Purchase      Microsoft
002  20/03/04  $50  10  Share Sales          Microsoft
003  15/04/04  $20  01  Broker Fee           Non share
004  10/05/04  $20  11  Membership Fee       Non share

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 04:23:31

just noticed your previous post.

SELECT [Transactions].[Date], [Transactions].[Share ID], [Transactions].[Transaction Type], [Transactions].[Quantity], [Transactions].[Unit Price]
FROM Transactions

WHERE [Transactions].[DATE] Between DateAdd("M",-3,[Forms]![Share Movement Over Three Months]![Today's Date]) And [Forms]![Share Movement Over Three Months]![Today's Date]
And 
(
       [Transactions].[Transaction Type]="Share Purchase"
    Or [Transactions].[Transaction Type]="Share Sales"
)

Nigelsd
Starting Member

14 Posts

Posted - 2006-04-21 : 05:52:14

Now it won't return anything...

I'm using MS Access if that makes any difference.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-21 : 08:34:06

Ur sample data is for year --> 04,
so it is not returning data for [Date] between today & 3 months before

Also is ur data in table and the way u type in the text box of Form is same ? ( Means both in dd/mm/yy format ?)

Srinika

Nigelsd
Starting Member

14 Posts

Posted - 2006-04-21 : 13:12:35

Yes, the format is the same.

[Forms]![Share Movement Over Three Months]![Today's Date]
is a text box that the user will enter a date into. I tried entering a 2004 date but it didn't give me any results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources