Need a second pair of eyes here.

[NJudson]

I know this probably seems like a trivial problem but I've been working on it for some time and can't get it right. I have a form with 2 drop down boxes cboFromDate and cboToDate. I'm trying to run a query that will produce results between those 2 dates and the query runs but does not display any results. I can get query to work for a single date or all dates but not the date range. Here is what I have written for my date range query:

Me.ListTotals.RowSource = "SELECT TOP 25 [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].[FBER Drop Attempts/Call Volume])>=0.025) And " & _
"(([Totals].[Digital Call-Volume])>=25)) " & _
"And (([Totals].[Date]= BETWEEN(cboFromDate.Value AND cboToDate.Value))) " & _
"ORDER BY [Totals].[FBER Drop Attempts/Call Volume] DESC;"
Me.Refresh

If where syntax problem then I would get an error. Right? Well, I don't get an error. It runs the query straight through but doesn't produce any data. I hope someone can see what my problem is. Thanks.

 

[Fornatian]

Maybe someone can correct me if I'm wrong but I was under the impression that it is advisable practice in query management to build a recordset using one query and then perform the calculations using a second.

Try building 1 query to extrapolate the data you need then build your totals from that query.

It may be unnecessary but then at least in the first instance you can see where you are falling over.

Ian

 

[NJudson]

So if I understand you then you're saying to take a query and then break it down into 2 seperate queryies? I don't know if what I have is technically a true query then. I don't have a query built for this. What I have is an SQL statement built into my form code. Does this matter? I'm not sure how I would go about breaking this up into 2 SQL statements.

 

[Fornatian]

Build yourself 1 query which gets the raw data you want to do calcs on(put all your data criteria in this one), lets call it qryRawData.

Then build a second query using qryRawData as the source table and do your Select Top etc.. from there

Stored queries run quicker than SQL statements as they are pre-optimised so it is advisable to store it as a query if it's not dynamically created.

Ian

 

[RV]

Lots of time when you run a query which doesn't come up with results as expected, it's due to the condition(s) you use.
If you use several conditions, try to run the query by leaving out (one of) your conditions.

>And (([Totals].[Date]= BETWEEN(cboFromDate.Value AND cboToDate.Value)))<

This should by:

And (([Totals].[Date] BETWEEN cboFromDate.Value AND cboToDate.Value)))

I suspect the reason you get no results is in the BETWEEN condition.
If so, try this:
And ([Totals].[Date] >=cboToDate.Value
AND [Totals].[Date] <=cboFromDate.Value
cboFromDate.Value)

Ian's advice to not use SQL statements in code but using predefined queries is a good one.
There's also another good reason not to use hardcoded statements, it's turning maintenance into a 100 percent assured nightmare!!!

One last remark:
it's better not to define a field named Date as Date is a reserved word in Access....

Suc6,

RV

 

[Rich]

You missed a few RV
putting spaces in field names is generally regarded as bad practice along with hyphens, slashes etc. Switch is also a reserved word in access.
You've also missed the Forms! reference Between Forms!MyFormName![cboToDate] And etc.

 

[NJudson]

Thanks everyone for your help. RV I rewrote me date range statement like you suggested and it works like a charm. I would have used seperated queries for this but I have an option group and combo boxes on the form for the user to select various options to run queries and I would have had to make a moderately large number of individual queries to call based on the options they selected so this is why I'm hardcoding the SQL. I'm still learning though.

Just curious, if the words Date and Switch are reserved words then how does Access allow me to use them? And since it does allow me to use them what harm can come if I continue to use them?

Thanks again everyone!