Lookup Date range in Access using vb.net

[trend]

Basically all I need to do is lookup any events that are going to happen this month... so look through DueDate for any date = 10/*/2004.. How would I do this in an access db where I am trying to look it up in vb.net?

 

I am using:

Cmd = New OleDbCommand("SELECT Seller, Service, Total, DueDate FROM Transactions WHERE Name=@givenBuyer AND DueDate BETWEEN "10/1/04" AND "10/30/04", Conn)

but that.. doesn't work... And I want the dates (the 10/1/04 and 10/30/04 to be automatically generated based on teh current month)

 

How could I do this?

 

thanks!-Lee

[kejpa]

Cmd = New OleDbCommand("SELECT Seller, Service, Total, DueDate FROM Transactions WHERE Name=@givenBuyer AND DueDate BETWEEN "10/1/04" AND "10/30/04", Conn)

Dim dtFirst, dtLast As Date
dtFirst = DateSerial(Now.Year, Now.Month + 1, 1)
dtLast = DateSerial(Now.Year, Now.Month + 1, Date.DaysInMonth(Now.Year, Now.Month + 1))

Cmd = New OleDbCommand("SELECT Seller, Service, Total, DueDate FROM Transactions WHERE Name=@givenBuyer AND DueDate BETWEEN [b]#[/b]" & dtFirst & "[b]#[/b] AND [b]#[/b]" & dtLast & "[b]#[/b], Conn)

Is better.

I'm not sure how this snippet handles December, but it's some first steps at least.

 

HTH

Kejpa

[trend]

//edit/// I didn't see your post above until I was about to post this //end of edit//

Ok, I changed it to:

 

Cmd = New OleDbCommand("SELECT Seller, Service, Total, DueDateTime FROM Transactions WHERE Name=@givenBuyer AND Address=@givenAddress AND DueDateTime BETWEEN @Start AND @End ", Conn) 
     Cmd.Parameters.Add("@Buyer", Buyer) 
  Cmd.Parameters.Add("@Address", Address)
  'Cmd.Parameters.Add("@BuyersName", Name) 
  Cmd.Parameters.Add("@Start", "1/1/2001")
  Cmd.Parameters.Add("@End", "12/12/2005")

 

Now it doesn't error out..But it still doesn't give me the fields within the range i specify... It gives me all the fields that have a a date (any date) in them...

Edited October 14, 2004 by trend

[trend]

Ahh.. OK, in the access db.. i had the field set as a text field.. nota date/time field...

 

but now, when i try to retreve the date, I get date and time.. even though date is the only thing in there.. I only need date. I do have a time field.. should I just combine them into one?

[kejpa]

Dates and times should be saved as date/time in the database, that's why there is such a field type...

If you just want one, ignore the time part by trimming it

dtLast= dtlast.Date 

 

HTH

Kejpa

[trend]

I think you are right.. I will recombine the fields to have a single date/time..

[trend]

Great! I updated the fields to date/time and must everything works smooothly now...

 

Now just to get the

 

Dim dtFirst, dtLast As Date

dtFirst = DateSerial(Now.Year, Now.Month + 1, 1)

dtLast = DateSerial(Now.Year, Now.Month + 1, Date.DaysInMonth(Now.Year, Now.Month + 1))

 

working...

 

Well, I will conquire this tomorrow.. as I am tired :)

 

thanks for all the help!!!

Lee