Problem with dates

[Tills13]

Ok, so I've written a program that searches a database and I've run into a little issue with dates, it seems. The search function has the ability to search ranges - and a date range is one of those options.

The dates are assigned to variables, which will be used in my regular expression, thusly:

If LCase(Sheets("Database").Cells(a, InCol).Value) <> "asap" Then
TempDate = FormatDateTime(Sheets("Database").Cells(a, InCol).Value, vbShortDate)
DataBaseItem = DateValue(TempDate)
End If
SearchItem = DateValue(SearchField.Value)
SearchItem2 = DateValue(SearchField2.Value)

And my regular expression is this:

If SearchItem <= DataBaseItem And SearchItem2 >= DataBaseItem Then
Sheets("Data Analysis").Cells(Returns, 1).Value = Sheets("Database").Cells(a, 1).Value
Sheets("Data Analysis").Cells(Returns, 2).Value = Sheets("Database").Cells(a, 2).Value
Sheets("Data Analysis").Cells(Returns, 3).Value = Sheets("Database").Cells(a, 20).Value
Sheets("Data Analysis").Cells(Returns, 4).Value = Sheets("Database").Cells(a, 11).Value
Sheets("Data Analysis").Cells(Returns, 5).Value = Sheets("Database").Cells(a, 10).Value
Sheets("Data Analysis").Cells(Returns, 6).Value = Sheets("Database").Cells(a, 8).Value
Sheets("Data Analysis").Cells(Returns, 7).Value = Sheets("Database").Cells(a, 18).Value
Sheets("Data Analysis").Cells(Returns, 8).Value = Sheets("Database").Cells(a, 17).Value
Sheets("Data Analysis").Cells(Returns, 9).Value = Sheets("Database").Cells(a, 77).Value
Returns = Returns + 1
End If

For some reason, it formats the DataBaseItem as, for example, 8/16/2011, where both the other dates have been formatted 16/8/2011. Why?

 

[shg]

I'd have expected them both to appear in the short date format in Control Panel, but why convert a date to a string and then back to a date?

    If LCase(Sheets("Database").Cells(a, InCol).Value) <> "asap" Then
        DataBaseItem = Worksheets("Database").Cells(a, InCol).Value
    End If
    SearchItem = DateValue(SearchField.Value)
    SearchItem2 = DateValue(SearchField2.Value)
 
    ' ...
 
    If SearchItem <= DataBaseItem And SearchItem2 >= DataBaseItem Then
        Worksheets("Data Analysis").Cells(Returns, 1).Resize(, 9).Value = _
        Application.Index(Worksheets("Database").Rows(a).Value, Array(1, 2, 20, 11, 10, 8, 18, 17, 77))
        Returns = Returns + 1
    End If

 

[Tills13]

I'd have expected them both to appear in the short date format in Control Panel, but why convert a date to a string and then back to a date?

    If LCase(Sheets("Database").Cells(a, InCol).Value) <> "asap" Then
        DataBaseItem = Worksheets("Database").Cells(a, InCol).Value
    End If
    SearchItem = DateValue(SearchField.Value)
    SearchItem2 = DateValue(SearchField2.Value)
 
    ' ...
 
    If SearchItem <= DataBaseItem And SearchItem2 >= DataBaseItem Then
        Worksheets("Data Analysis").Cells(Returns, 1).Resize(, 9).Value = _
        Application.Index(Worksheets("Database").Rows(a).Value, Array(1, 2, 20, 11, 10, 8, 18, 17, 77))
        Returns = Returns + 1
    End If

Many of the items in the database are stored as irregular date formats. most of the items are "40273" or something like that - nothing I can use. If I were to just take the value straight from the database sheet and try to convert it into a date:

DataBaseItem = DateValue(Sheets("Database").Cells(a, InCol).Value)

Where Sheets("Database").Cells(a, InCol).Value = 40361, it doesn't assign anything to "DataBaseItem." I have to convert 40361 into a format that DateValue recognizes...

 

[shg]

In Excel, dates are numbers. Enter 40273 into a cell and format as a date to see 4/5/2010. You can assign that to a date variable just fine.

 

[Tills13]

In Excel, dates are numbers. Enter 40273 into a cell and format as a date to see 4/5/2010. You can assign that to a date variable just fine.

DataBaseItem is not a date variable. It handles everything from Strings to Integers. On that note, however, can you cast a variable to a certain type?

 

[Rick Rothstein]

DataBaseItem is not a date variable. It handles everything from Strings to Integers. On that note, however, can you cast a variable to a certain type?

You can use the CDate function for that...

YourDateVariable = CDate(40273)

Or, you can Dim your variable as Date...

Dim YourDateVariable As Date
.....
.....
YourDateVariable = 40273

In either scenario, YourDateVariable would end up storing the date April 5, 2010.

 

[Tills13]

I'm really not sure what I'm doing wrong - I've added watches and ran my code in break mode. The code in question is the elseif containing the commented out line.

Here:

 

[shg]

If your looking for dates, why not declare all the variables that contain them as Date?

Maybe back up a step and provide the 10,000 foot view.

 

[Rick Rothstein]

In the code you posted, what is the value contained in SearchField.Value (it is what you are assigning to SearchItem)? And what exactly is SearchField... a TextBox?