Hi all, I have a query that is built from a string variable. (the date is variable by input from the user).

myQuery = "Select whatever from tableName where date_field between #" & myDate1 & "# and #" & myDate2 ";"

DoCmd.RunSQL wants an action query. What command could I use to get this to run since it is only a select query?

If there's no command to do this, does anyone have a suggestion?
Thanks everyone.
PB

Have you tried this?

DoCmd.OpenQuery MyQuery

Well when I do that it tells me

Runtime error 7874
MS Access can't find the object 'Select * from tableName'

This seems ... to be used for saved queries. This query is built on the fly in code. I suppose there's a quick way to tie a prompting query in to all of this but I'm not sure how to do that. It would likely eliminate the requirement of building anything in code.

Is there a Date Picker that you can tie to a prompt for a query based on dates like this? <-- Floundering for suggestions. Ignore or address any parts of the question you see fit.

PB

If I put:

Like "*" & [Enter a Field_Name] & "*"

in the criteria for non dates, I get the prompt I need but I'm not sure how to structure this type of criteria given the
## around the dates And the fact the I need 2, the start and end dates for the query...

Between #3/1/2003# and #3/31/2003# :::is in the criteria block right now.

Maybe this will be useful in clarifying my question.
Thanks
PB

You're right, docmd.OpenQuery strSQL produces errors. Here's a workaround. Copy/paste the following into a new module in Northwind. Follow the instructions to invoke the function.

Public StartDate As Date
Public EndDate As Date

Function OrderGet(pstartdate As Date, penddate As Date)
'*******************************************
'Name: OrderGet (Function)
'Purpose: Open a query created from code.
' A workaround to Access error produced
' with statement docmd.OpenQuery strSQL
'Author: raskew
'Inputs: from debug window type:
' ? orderget(#4/1/95#,#12/31/95#)<enter>
'*******************************************

Dim db As DATABASE
Dim qd As QueryDef
Dim strSQL As String
Dim tName As String
Dim test As String

Set db = CurrentDb
StartDate = pstartdate
EndDate = penddate
strSQL = "SELECT * from Orders WHERE Orders.OrderDate between #" _
& StartDate & "# AND #" & EndDate & "# " _
& "ORDER BY Orders.OrderID;"

On Error Resume Next
tName = "TempFilter"
'Does query "TempFilter" exist? If true, delete it;
test = db.QueryDefs(tName).Name
If Err <> 3265 Then
docmd.DeleteObject acQuery, "TempFilter"
End If

Set qd = db.CreateQueryDef("TempFilter", strSQL)

docmd.OpenQuery "TempFilter", acViewNormal

'Since this is a test, delete Query "Temp Filter"
docmd.DeleteObject acQuery, "TempFilter"

'cleanup
qd.Close
db.Close
Set db = Nothing

End Function

Rather than creating and deleting the querydef which just contributes to database bloat, just create a parameter query and save it. Your parameters can pop up prompts or refer to fields on a form. Your where clause should be either

where date_field Between [Enter Begin Date] And [Enter End Date]

or

where date_field Between Forms!YourFormName!YourBeginDateControl And Forms!YourFormName!YourEndDateControl

Keep the code that Bob posted because it is useful for creating queries totally built with code since these cannot be parameterized.

Thank you both. I've got it working. To think that I had so much trouble because I was trying to write the parameter query with the # signs......

between #[Enter Start Date]# and #[Enter End Date]#

LOL. Just about every problem I run in to in Access is syntax based. Is there a good practical reference book out there to address issues like this?

Thanks again
PB

Pat makes a very good point about database bloat. When I first read the response, I was skeptical that this would be a significant problem. However, to test, I wrapped the function in a loop that ran through 100 iterations of the routine. At the end, the database had increased in size almost 20%--a substantial hit when nothing had changed (data-wise and object-wise) within the application.

Practically speaking, in many situations parameter queries can be a real bear and it's often more pragmatic to create queries 'on-the-fly'. Example: I hadn't 'repaired/compacted' the particular application in about six months and it had expanded about 15% over that time. Fact is that most applications don't repeat the same 'bloating' procedure 100 times.

Bottom-line, think it's a trade-off. A disciplined developer who repairs/compacts the application on a regular basis isn't going to have much of a problem with database 'bloat'. However, if you 'let it rip' and never go back to perform maintenance, 'bloat' is gonna eat you alive.

Best Wishes,

Bob

It adds up faster than you think. 25 users running 4 queries a day = the 100 times that you tested. Of course if everyone has their own front end, the effect is minimized. The other issue is speed of execution. A saved querydef will executed faster than one built in code. The difference being that Access "binds" the querydef when it is saved. The binding process includes parsing the SQL code to figure out what it needs to do and calculating an access plan for how to retrieve the data requested by the query. Should Jet use index A or index B or does it need to do a full table scan, etc. If you code your SQL in modules, Access needs to go through this "binding" process EVERY time the query is executed rather than ONLY once when it is saved. This is also the cause of the bloat. The workspace required for this "binding" cannot be recovered until the db is compacted.

Some posters have reported having to compact shared databases twice a day!! So, my position is, if you can reduce the necessity to compact and decrese the execution time of queries (even if we are talking seconds) by choosing efficient methods, why not get in the habit? You are much less likely to create a dog of a database.