IIF-dates between-will not filter with correct results.

[WeebleSue]

(I have searched this forum and found other answers to the error message i'm getting, but none of the other users' issues seem to be similar to mine)

I retrieve data from a text file that has date/time information in text form.

I use a query to grab the revelant data from the text field (right, left, mid, etc.) and then use FinalDate=DateSerial(yearcolumn, monthcolumn, datecolumn) and FinalTime=TimeSerial(hourcolumn, minutecolumn, secondcolumn) to get the Time.

Then I add the two together to get my FinalDateTime value in a proper "time" formatted column.

Now I need to select dates from this field that fall between a certain start date and certain end date. These values are stored in TempVars.

On either the FinalDate or FinalDateTime columns, i have tried Criteria = Between [TempVars]![StartDate] and [tempVars]![EndDate], but get that old nasty error "expression typed incorrectly etc."

I have added a comparison column and say "CheckMe: IIF ([FinalDate] > [tempVars]![StartDate] and [FinalDate] < [TempVars]![EndDate], "Yes", "No")
Then i try to filter THAT column on "Yes" to show me only those that fall in the range.

Without anything in the Critieria field, the data populates perfectly. All of the entries that should fall between the dates show Yes and the ones that do not, show No.

But the query WILL NOT accept anything in the Criteria column. In the expression, I have tried IIF (x, 0, 1) or IIF (x, 1, 0), i have tried IIF (x, true, false), even IIF (x, "Yes", ""). All of the equations work perfectly but the daggum query still will not filter.

I removed anything from the Critieria row but left the expression. Then i created a new query based on this one, trying to select only where CheckMe = "Yes." No luck. same message.

I gave up on the TempVars and tried putting the dates into a table, to be absolutely positive i was comparing date vs date. Even if I used [datetable]![StartDateTable] instead of [tempVars]![StartDate], same doggone thing. results are perfect, cannot filter on it.

"The expression is typed incorrectly, or it is too complex to be evaluated." etc.

How can Access put a result in a field perfectly, but not let me sort on that same field??

I'm going nuts! :banghead:

 

[spikepl]

Criteria = Between [TempVars]![StartDate] and [tempVars]![EndDate]

Have you really added the " =" in the criterion field? Then remove it.

 

[Pat Hartman]

When working with dates that contain time, you need to extract the date part of the value for the comparison.

Where DateValue(YourDate) Between [TempVars]![StartDate] and [tempVars]![EndDate]

The error messages make it sound like one of the three fields is NOT defined as a date so check that first.

 

[WeebleSue]

SpikePL, no, there is no = in the Criteria line. That was just to explain that what followed was what i put in the critieria section.

The FinalDate is assembled using DateSerial(year, month, date) {and no, those are NOT my real column names}. Is DateSerial not an official "date" result?

And if not, why does the query give the required result in the column... just not allow me to filter on it?

 

[WeebleSue]

Okay, the plot thickens!
I have other tables where the date that I'm using to compare, and if the date is a column in a table, I use the same exact critieria using the same exact variables, and the data filters perfectly fine.

The data in this questionable query is defined IN the query. It is not a column in a table.

The whole problem is that the initial file I have to import has dates with a period separator and not a slash. So 1/1/2012 is 01.01.2012, and Access will not recognize that as a text-based date. I have tried replacing the periods with dates, then importing, but Access still will not change the text into a Date. because of all the sorting, I need the information in sortable, filterable date format.

In addition to that, the date is in European format not US format, so I have to do that other swap to move date/month into 'proper' order. I cannot change the system date format on the work computer, so I have to do it via brute force. So the resultant Date cannot be a value in a table. It has to be developed using a query.

 

[WeebleSue]

Okay, well, I kinda solved it myself.

I created a new table from the questionable query, so now my "good" date is indeed a column in a table.

Now all my filtering works just fine.

seems like a bug to me.... one would think that a value in a column of a query would act the same way as a similar value in a column of a table. Hmph. They do not.

So now when I import new data, i just have to remake the table each time. Not a big deal thanks to macros.

 

[Pat Hartman]

In a table, the date is an actual date/time datatype which under the covers is a double precision number with the integer portion being the number of days since Dec 30, 1899 and the decimal is the milliseconds since midnight. What we "see" is simply formated for our convenience. The column in the query is a text string. Strings are processed character by character left to right. That's why the numbers 1-10 sort as 1, 10, 2, 3, etc rather in what a human would consider the "logical" order.

If you need to sort a text string date, it MUST be in year, month, day order rather than the standard order of your country.