Date Comparisons Again . . .

[dakota81]

I'm not understanding why I can't compare dates with an SQL statement when creating a recordset object.

These querys works fine:

"SELECT * FROM Calls WHERE [CallDate] = Now()"
"SELECT * FROM Calls WHERE [CallDate] = DateSerial(Year(Now()), Month(Now())..."

But then querys like the following result in errors, saying there was an expected equation.

Dim Date1 As Date
Date1 = Now()
"SELECT * FROM Calls WHERE [CallDate] = Date1"
-or-
"SELECT * FROM Calls WHERE [CallDate] = DateSerial(Year(Date1)), Month(..."

I've been trying several syntax possibilites like using single quotes, the date value, the date value in single quotes, but nothing is working. Any Help?

 

[RickSpr]

I never heard of an "expected equation" error. What exact message are you getting?

I don't see how your first example could give you what you want. Now() returns the current date and time, which is very unlikely to match anything in your table. Likewise, if CallDate in your table is being assigned Now() when the record is created, it contains a time. You probably mean to compare just the date parts. To do that, I would use DateValue([CallDate]) = DateValue(Date1). Rick Sprague

 

[dakota81]

The error message that pops up says 'expected equations: 1'. I want to have two comparisons in the query using the AND command, and when I first was trying things out, the error message said "expected equations: 2'. So basically what I have typed is not being recognized as a valid equation. Actually it might have said 'expressions' instead of 'equations', but either way the meaning is the same.

I do understand that if I compare against Now() I will not get the results I want, but I threw it up as an example query because atleast that statement will run without error.

I will try putting both sides of the equation into the DateValue function next and see if I can get anywhere.

The thing that really bugs me is why the first two querys I typed run but the latter two fail, just by storing Now() into a variable. Once I figure this out, I'll have two text boxes, one for a starting date, one for an ending date, then I'll pull up all records between those dates, after clicking a button of course.

 

[RickSpr]

I overlooked the problem on my first reading. The problem is that Date1 isn't known to the Jet engine, which interprets SQL statements. You need to code it this way:
SELECT * FROM Calls WHERE [CallDate] = #" & Format(Date1, "mm/dd/yy&quot & "#"

You can get away with coding VBA function names and some other things in SQL statements, because the Jet engine calls VBA to interpret things it doesn't understand. But the VBA things have to be global, such as function names and forms reached by the Forms variable. If Date1 were defined as a module-level variable, it might work; I'm not sure. But in general, variables you define yourself can't be named in an SQL statement. You have to convert the variable's value to a string and insert the string in the SQL statement. (The "#" characters mark the string as a date constant. Without them, the value would be interpreted as three numbers involved in 2 divisions.) Rick Sprague