Records Between Dates 1

[waytech2003]

I have an Access 2000 database where a date is stored in a 10 character string "01-01-2006" I need to get the records that are between to ranges. I have tried the codes below and it will not work. I is ok if working with just one year (2005) but not a two year spread (Jan 2005 to Jan 2006)

"SELECT * FROM Table1 WHERE " _
"ReportDate Between '" & textbox1 & "' And '" & textbox2 & "' "

"SELECT * FROM Table1 WHERE " _
"ReportDate Between #" & textbox1 & "# And #" & textbox2 & "# "

"SELECT * FROM Table1 WHERE " _
"CDate(ReportDate) Between #" & textbox1 & "# And #" & textbox2 & "# "

Any ideas?

 

[gmmastros]

Are you making sure that the date in textbox1 is less than the date in textbox2?

For example, in my database, this works:
[tt][blue]Select *
From Calendar
Where CalendarDate Between '02-01-2005' and '03-01-2005'
[/blue][/tt]

But this doesn't (Because the dates are in the wrong order):
[tt][blue]Select *
From Calendar
Where CalendarDate Between '03-01-2005' and '02-01-2005'
[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[waytech2003]

gmmastros

Yes I am.

In your database, is CalendarDate a DATE or TEXT field?

My problem is that the field I am trying to look at is a TEXT String not a DATE

 

[gmmastros]

In my database, it's a date field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HughLerwill]

I have'nt tested the following in an SQL statement but something along these lines may help;

'convert the strings to Dates

Dim date1 As String
Dim date2 As String

Dim date3 As Date
Dim date4 As Date

date1 = "01-01-2006"
date2 = "01-01-2008"

date3 = DateSerial(Right$(date1, 4), Mid$(date1, 4, 2), Left$(date1, 2))
date4 = DateSerial(Right$(date2, 4), Mid$(date2, 4, 2), Left$(date2, 2))

HIH

 

[waytech2003]

Thanks all for your replies.

I have decided to change that database structure, and make the TEXT fields that are holding dates, into DATE/TIME fields.

 

[gmmastros]

Congratulations. I'm sure you'll be glad you did.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[psykoProgrammer]

After you have changed your fields to Date fields you will (as far as i know) still have to make some changes to your SQL statement.

I believe that the Date field will actually hold more detail than needed (in this case), and so you should place the date you are searching for between # characters to ensure you're statement is returning all records.

My sample would be:

"SELECT * FROM [Calendar] WHERE [yourDateField]>#" & textbox1 & "# AND [yourDateField]<#" & textbox2 & "#"

Assuming your textbox has the date displayed in the same format (YYYY-MM-DD) as your Short Date field, this query should work just fine. You won't have to store the textbox values into string variables either - and that should help to keep things simple.

Cheers.

 

[waytech2003]

The code below now works properly. My problem is now gone, since I altered the database fields. Textboxes have dates as "01/31/2007" just as in the database.

"SELECT * FROM Table1 WHERE ReportDate Between #" & textbox1 & "# And #" & textbox2 & "#

 

[Andrzejek]

I would suggest avoiding default properties of controls:

"SELECT * FROM Table1 WHERE ReportDate Between #" & textbox1[b][blue].Text[/blue][/b] & "# And #" & textbox2[b][blue].Text[/blue][/b] & "# "

Do you have the dates enter by user straight into text boxes?
If so, you have to do a lot of validations to make sure it is a date. In the case like that I would suggest using Calendar control or MonthView control.

Just a sugestion...

Have fun.

---- Andy

 

[waytech2003]

Andrzejek

I agree for reading that the textbox1.Text is easier, but I was under the impression that using the Default property of a control made the code run quicker. I am be wrong here...

Also I check for a valid Date when the textbox looses focus. I keep the Date type textboxes in an array so they can use the same code.

Sub txtDate_LostFocus(Index As Integer)
If IsDate(txtDate(Index)) Then
txtDate(Index) = Format(txtDate(Index), "MM/DD/YY")
Else
txtDate1(Index) = ""
End If
End Sub

 

[Andrzejek]

Using/not using default property is not slower/faster, it is just agreed among programmers it is easier to read, and gives code more 'professional' look. And some day your code will be managed by somebody else, so I do not use default properties of any controls, and I put nice comments in my code.

Have fun.

---- Andy