SQL Server 7.0 Error Message

[Kostas]

When I run the following query in Design Mode I get results, but I can not save the query. I get an error message that reads:

"ODBC Error [Microsoft][ODBC SQL Server Driver]COUNT Field Incorrect"

When I try use the Query Analyzer, it will not even compile the SQL. The same error comes up. How come it will run it once, but then it says that its not a valid statement?

If I take the WHERE clause with the prompts at the end out it runs, but the prompting is the critical function of this query so I need it in there.

SELECT dbo.tbl_lucent_center.lcnt_center_name,
dbo.tbl_lucent_skill.lcnt_skill_status,
dbo.tbl_lucent_interval.Date, dbo.tbl_lcnt_timeconv.real_time,
dbo.tbl_lucent_interval.TOD,

AS ASA

FROM.... dbo.tbl_lucent_interval LEFT OUTER JOIN
dbo.tbl_lcnt_timeconv ON
dbo.tbl_lucent_interval.TOD = dbo.tbl_lcnt_timeconv.lucnt_TOD LEFT
OUTER JOIN
dbo.tbl_lucent_skill ON
dbo.tbl_lucent_interval.Skill = dbo.tbl_lucent_skill.lcnt_skill_id LEFT
OUTER JOIN
dbo.tbl_lucent_center ON
dbo.tbl_lucent_interval.ACD = dbo.tbl_lucent_center.lcnt_center_id

WHERE (dbo.tbl_lucent_interval.Date BETWEEN ? AND ?)

[forty7]

You may have encountered one of two problems.

1) Your WHERE clause
I may have some help for you. Take a look at the T-SQL help from query analyzer for BETWEEN. I noticed this:
"... If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN."
Since you are doing OUTER JOINS some of your data may be NULL and cause the function to screw up. You may want to use lt and gt in your where clause instead. If you find that I'm wrong on this, please post it. I'd like to know how it turns out.

2) VB query builder
The error is propably preventing the designer from saving the query.
OR
The SQL is acceptable to SQL-Server but not VB. I've encountered SQL that is accepted in query analyzer but not in the VB Designer. In this case, I go to the properties of the query instead of the designer and Paste the query directly from query analyzer.


thanx/good luck,
adam

[Kostas]

To your first solution:

1) I used lt and gt and it still came up with the same error message, but the query did run and return a record set just like the BETWEEN.

2) I am using the Design View in the SQL Enterprise Manager, not the VB Designer. This is really baffling me...I think I'm going to have to use an ADO to get the information from VB to SQL if I cant get SQL Server to prompt me.

Thnx for the 411 though.