UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating A Query From SQL Where Condition, Access 2007    
 
   
josephbupe
post Jun 13 2018, 10:12 AM
Post#1



Posts: 814
Joined: 23-January 08
From: Lusaka,Zambia



I want to create a a query based on the Where condition from the previous statement in the same form, but the query is not being created when i run the button.

Something like this:

CODE
Dim qdf As QueryDef

strSql = "SELECT * FROM qryEvents WHERE strWhere"

'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "qryEvents_Search"

Set qdf = CurrentDb.CreateQueryDef("qryEvents_Search", strWhere)
'DoCmd.OpenQuery qdf.Name
DoCmd.OpenQuery "qryEvents_Search"

'release memory
qdf.Close
Set qdf = Nothing


The Where condition part is:

CODE
If Len(strWhere) > 0 Then
    If Nz(DCount("*", "qryEvents", Left(strWhere, Len(strWhere) - 0)), 0) > 0 Then
       strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 0)
       strSQL = "SELECT * FROM qryEvents " & strWhere & ";"


Go to the top of the page
 
Daniel_Stokley
post Jun 13 2018, 10:26 AM
Post#2



Posts: 273
Joined: 22-December 14
From: Grand Junction, CO, USA


This part definitely will not work:
CODE
strSql = "SELECT * FROM qryEvents WHERE strWhere"


The following combination of your code might work:
CODE
Dim qdf As QueryDef

If Len(strWhere) > 0 Then
    If Nz(DCount("*", "qryEvents", Left(strWhere, Len(strWhere) - 0)), 0) > 0 Then
       strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 0)
       strSQL = "SELECT * FROM qryEvents " & strWhere & ";"

'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "qryEvents_Search"

Set qdf = CurrentDb.CreateQueryDef("qryEvents_Search", strWhere)
'DoCmd.OpenQuery qdf.Name
DoCmd.OpenQuery "qryEvents_Search"

'release memory
qdf.Close
Set qdf = Nothing
Go to the top of the page
 
josephbupe
post Jun 13 2018, 11:16 AM
Post#3



Posts: 814
Joined: 23-January 08
From: Lusaka,Zambia


Thanks Daniel,

i have tried it but the query qryEvents_Search is not being created and I have getting this error:

QUOTE
Syntax error (missing operator) in query expression


as seen in the attached screenshort
Attached File(s)
Attached File  error.jpg ( 161.74K )Number of downloads: 2
 
Go to the top of the page
 
josephbupe
post Jun 13 2018, 11:53 AM
Post#4



Posts: 814
Joined: 23-January 08
From: Lusaka,Zambia


Hi Daniel,

Okay, now its working, although i had to create the query qryEvents_Search manually, but the search results are being passed to the new query. Thank you for helping out.

CODE
Dim strNewSql As String
Dim qdf As QueryDef

strSql = "SELECT * FROM qryEvents " & strWhere & ";"

'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "qryEvents_Search"

strNewSql = "SELECT * FROM qryEvents " & strWhere
Set qdf = CurrentDb.CreateQueryDef("qryEvents_Search", strNewSql)

qdf.Close
Set qdf = Nothing


Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th October 2018 - 05:57 AM