Running a select query string.

[pdbowling]

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

 

[Mile-O]

Have you tried this?

DoCmd.OpenQuery MyQuery

 

[pdbowling]

hmmm

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

 

[pdbowling]

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

 

[raskew]

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

 

[pdbowling]

Thanks

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

 

[raskew]

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