I'm using Access 2000. I have SQL encoded on a form that I want to run based on certain criteria that the user selects. This criteria contains a date range. For example here is my SQL:

SWITCH = cboSwitch.Value
StartDate = cboFromDate.Value
EndDate = cboToDate.Value
Me.txtSiteToPlot = Me.ListTotals

dbs.QueryDefs.Delete ("FBER_Query")
dbs.CreateQueryDef ("FBER_Query")
Set qryTempQuery = dbs.QueryDefs("FBER_Query")
qryTempQuery.SQL = "SELECT [Totals].[Cell-ID], [Totals].[FBER Drop Attempts/Call Volume], " & _
"[Totals].[Digital Call-Volume], [Totals].[FBER-Drop-Attempts], " & _
"[Totals].[Switch], [Totals].[Date] " & _
"FROM Totals " & _
"WHERE [Totals].[Cell-ID] = '" & Me.ListTotals & "' " & _
"And [Totals].[Switch] = '" & SWITCH & "' " & _
"And (([Totals].[Date] BETWEEN '" & StartDate & "' AND '" & EndDate & "'))" & _
"ORDER BY [Totals].[Date] ASC;"

DoCmd.OpenQuery "FBER_Query", acViewNormal

The SQL apparently runs but on the OpenQuery line I get run-time error 2001 "you canceled the previous operation". Not sure what this was I then went to the Query the the querydef just created and tried to open it manually and I then get an error "Data type mismatch in criteria expression".

What seems to happen is that it doesn't like the date criteria. The table that my query is based on has the date field formated as short date, but if I change it to text format then the query will work. This is all fine except I also want to run a chart that is based on the querydef that was just created and it won't plot a date if it is text format. It's like a catch-22, I can get half of what I want if it's date format but I can't get the other half unless its text format..... I'm confused.

What can I do to get the query to work and so I can plot the chart based on the query?
I really appreciate any help offered. Thanks.

Oh by the way I remember people saying that Switch and Date are reserved words but I never had problems before. Could this be what is causing the problem??? I'd have to change a lot of stuff to get rid of the Date And Switch Field names!!!

[This message has been edited by NJudson (edited 03-28-2002).]

It appears that my problem is with the syntax of the Date Criteria statement. I thought I got it right earlier but I have not. In the SQL Date Criteria I have a syntax error where I entered:

"And (([Totals].[Date] BETWEEN '" & StartDate & "' AND '" & EndDate & "'))" & _

I've tried a multitude of various statement like this:

"And (([Totals].[Date] BETWEEN '# & StartDate & #' AND '# & EndDate & #'))" & _

but I can't seem to figure out the correct syntax.

When doing a between date criteria for a regular query it is entered like this:

BETWEEN #3/2/02# And #3/8/02#

but what would it be for a user-defined criteria in a coded SQL querydef? Thanks.

[This message has been edited by NJudson (edited 03-28-2002).]

"And (([Totals].[Date] BETWEEN " & StartDate & " AND " & EndDate & "))" & _

I just got rid of the quotes.