Solved

DAO.QueryDef

Posted on 2009-07-09
15
597 Views
Last Modified: 2013-11-27
I'm getting a debug error on the line qdf.SQL = strSQL stating:

Microsoft Visual Basic
Run-time error '3129':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Suggestions?

For addtional information refer to Q/A "Inner Join Query based on Input Parameter for Table Names"
Public Sub InnerJoin()
 

    Dim qdf As DAO.QueryDef

    Dim strSQL As String, strQueryName As String

        

strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _

".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _

".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _

".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _

".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _

".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _

".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _

".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _

".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _

".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _

".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _

".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _

".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _

".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _

".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _

".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _

strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _

strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _

".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr," & _

"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN," & "WHERE " & strTblBefore & ".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"

strQueryName = "qryInnerJoinBeforeAfter"

Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")

qdf.SQL = strSQL

Set qdf = Nothing
 

 Debug.Print strSQL

    

End Sub

Open in new window

0
Comment
Question by:dlmaz
  • 4
  • 3
  • 3
  • +3
15 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
There is something very strange about your SQL string. When I dimension the strTblBefore and strTblAfter as strings and give them values, then put Debug.Print strSQL after strSQL = "SELECT " & ..., I get True instead of a string.
Public Sub InnerJoin()

 

    Dim qdf As DAO.QueryDef

    Dim strSQL As String, strQueryName As String

    Dim strTblBefore As String, strTblAfter As String

    strTblBefore = "strTblBefore"

    strTblAfter = "strTblAfter"

strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _

".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _

".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _

".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _

".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _

".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _

".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _

".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _

".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _

".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _

".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _

".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _

".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _

".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _

".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _

".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _

strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _

strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _

".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr," & _

"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN," & "WHERE " & strTblBefore & ".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"
 

'======== ADDED ========

Debug.Print strSQL

'=======================
 

strQueryName = "qryInnerJoinBeforeAfter"

Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")

qdf.Sql = strSQL

Set qdf = Nothing

 

 Debug.Print strSQL

    

End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this

strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _
".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _
".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _
".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _
".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _
".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _
".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _
".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _
".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _
".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _
".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _
".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _
".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _
".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _
".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _
".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _
strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _
strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _
".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr " & _
"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN" & " WHERE " & strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""

strQueryName = "qryInnerJoinBeforeAfter"
Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.SQL = strSQL
Set qdf = Nothing

 Debug.Print strSQL
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
For starters, your syntax with the line continuation characters (Underscores) is incorrect.

I've fixed a few lines for you to show you the correct syntax (Underscore at the end, ampersand at the beginning of the next line):
 
strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore _
 & ".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore _
 & ".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore _
 & ".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore _
 & ".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore

Also, you need to define strTblBefore and strTblAfter (as shown in the previous post)
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Sorry cap,

I didn't refresh the screen.

cap's syntax works as well.
 
(Ignore my previous post)
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
dlmaz,

For the record, it's nothing to do with the line continuation characters. I'm set in my ways of doing things, so at first glance your syntax looked wrong to me (that part of the syntax in the original post is definitely correct - my remark to the contrary was wrong).

This was the original end of your sql string:
".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"

Cap made a great catch on this one. The change he made was very subtle:


strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""

He rearranged the quotes a bit dropping the quotes before the <> and adding quotes around the X's. This embedded the X in the SQL string.

Before that, the SQL string was effectively a boolean expression that checked two conditions:
1. The bulk of your SQL string <> 'X'
2. "someTableName.PPS AS AfterPPS," <> "X"

Both of these conditions are true since neither of these strings was literally 'X'. That's why the SQL string evaluated to TRUE, per the first comment.

No points for this comment please - it was an excellent find on cap's part, but worthy of an explanation.
0
 

Author Comment

by:dlmaz
Comment Utility
Thank you for the quick responses, however after using the code as provided by capricorn1, I now get this error:

Run-time error '3075':
Syntax error (missing operator) in query expression '.PPN'

What I am trying to accomplish is to get an inner join query to run based upon input on the table names that are created by the user (see note below).

Please, note the SQL is based upon the following:

Public Function CreateTable()

    Dim sSql As String
    Dim sTable
   
    sTable = InputBox("Enter Table Name", "Table Creation")
    If Trim$(Nz(sTable, "")) = "" Then
        MsgBox "Must specify table name"
    Else
        sSql = "SELECT PPN, PPS, PPNStatusEffDate, JON, JobTitle, BillingCriteria, ManHours, Budget, APSUnitN, APSWBSN, APSDir, APSMgr, POwner, CompCharge, ContractLbr, AuthorizedName, PropN, PLN, TCS, CP, PWPReq, SLNucQA, SLProjMgr, SLProgMgr INTO [" & sTable & "] FROM qryBeforeAfter"

        Debug.Print sSql

        CurrentDb.Execute sSql, dbFailOnError
    End If
End Function

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

dlmaz,

in your query that you build, you have specified
" & strTblBefore & ".PPN AS BeforePPN

and
" & strTblAfter & ".PPN AS AfterPPN
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
Just to mention - readability is a wonderful thing in development.
It helps you support your own application, which helps you make your application's quality better.
While I don't believe in it at the expense of performance or functionality, when there's nothing to lose then readability every time! ;-)
A couple of table aliases and it's much simpler.
strSQL = "SELECT B.PPN AS BeforePPN, B.PPS AS BeforePPS," & _
"B.PPNStatusEffDate AS BeforePPNStatusEffDate, B.JON AS BeforeJON," & _
"B.JobTitle AS BeforeJobTitle, B.BillingCriteria AS BeforeBillCriteria," & _
"B.ManHours AS BeforeManHrs, B.Budget AS BeforeBudget," & _
"B.APSUnitN AS BeforeAPSUnitN, B.APSWBSN AS BeforeAPSWBSN," & _
"B.APSDir AS BeforeAPSDir, B.APSMgr AS BeforeAPSMgr," & _
"B.POwner AS BeforePOwner, B.CompCharge AS BeforeCompCharge," & _
"B.ContractLbr AS BeforeContractLbr, B.AuthorizedName AS BeforeAuthName," & _
"B.PropN AS BeforePropN, B.PLN AS BeforePLN, B.TCS AS BeforeTCS," & _
"B.CP AS BeforeCP, B.PWPReq AS BeforePWPReq, B.SLNucQA AS BeforeSLNucQA," & _
"B.SLProjMgr AS BeforeSLProjMgr, B.SLProgMgr AS BeforeSLProgMgr," & _
"A.PPN AS AfterPPN, A.PPS AS AfterPPS, A.PPNStatusEffDate AS AfterPPNStatusEffDate," & _
"A.JON AS AfterJON, A.JobTitle AS AfterJobTitle, A.BillingCriteria AS AfterBillCriteria," & _
"A.ManHours AS AfterManHrs, A.Budget AS AfterBudget, A.APSUnitN AS AfterAPSUnitN," & _
"A.APSWBSN AS AfterAPSWBSN, A.APSDir AS AfterAPSDir, A.APSMgr AS AfterAPSMgr," & _
"A.POwner AS AfterPOwner, A.CompCharge AS AfterCompCharge, A.ContractLbr AS AfterContractLbr," & _
"A.AuthorizedName AS AfterAuthName, A.PropN AS AfterPropN, A.PLN AS AfterPLN," & _
"A.TCS AS AfterTCS, A.CP AS AfterCP, A.PWPReq AS AfterPWPReq," & _
"A.SLNucQA AS AfterSLNucQA, A.SLProjMgr AS AfterSLProjMgr, A.SLProgMgr AS AfterSLProgMgr " & _
"FROM " & strTblBefore & " B INNER JOIN " & strTblAfter & " A ON B.PPN = A.PPN WHERE B.PPS <> 'X' And A.PPS <> 'X'"
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Miriam:  I've had & at the end of a line on countless occasions.
0
 

Author Comment

by:dlmaz
Comment Utility
Please, excuse the delayed response but I've had other issues at work that took priority over this until now. Reviewing the comments since my last post, I still haven't been able to resolve this issue.

Now, there was one module created to allow the user to name the table being created from essentially a Make Table query.

The second module that I'm working on will run the Inner Join query based upon the 2 table names supplied by the user.

For example, the user specifies a table name of Smith and another table name of Jones. When the Inner Join query is run, it should ask the user which table name will be considered tblBefore? and which table name will be considered tblAfter?

Hopefully, this will help clarify what it is I am trying to accomplish. If it's still not clear or if the code used so far will not accomplish this, please, feel free to advise me. Thank you!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
I'm not entirely clear. Which part are you still struggling with? Asking the user? Using their entries? The SQL itself should be OK - no?
0
 

Author Comment

by:dlmaz
Comment Utility
Using the attached, I'm still getting a Run-time error 'syntax error (missing operator) in query expression '.PPN'.'

Also, I discovered yesterday afternoon that when the SQL did work with input on the table names, the query is not using the data from those tables anyway.

I just don't seem to get it!

I don't know what I'm doing wrong or if I'm making it more complicated than it should be.

ublic Sub InnerJoin()
 

    Dim qdf As DAO.QueryDef

    Dim strSQL As String, strQueryName As String

        

strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _

".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _

".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _

".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _

".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _

".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _

".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _

".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _

".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _

".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _

".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _

".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _

".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _

".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _

".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _

".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _

strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _

strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _

".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr " & _

"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN" & " WHERE " & strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""
 

strQueryName = "qryInnerJoinBeforeAfter"

Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")

qdf.SQL = strSQL

Set qdf = Nothing
 

 Debug.Print strSQL
 

    

End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Replace lines 27 through 29 with the code in the snippet.
-Chuck
' turn off error handling

On Error Resume Next

    With CurrentDb

        ' delete any the query if it exists

        .QueryDefs.Delete strQueryName

        DoEvents

' turn error handling back on

On Error GoTo 0

        ' create a new query

        Set qdf = .CreateQueryDef(strQueryName)

        DoEvents

        ' set the sql of the new query

        qdf.Sql = strSQL

        DoEvents

        ' run the query,

        qdf.Execute

        DoEvents

        ' delete the query

        .QueryDefs.Delete strQueryName

        DoEvents

    End With

Open in new window

0
 

Author Comment

by:dlmaz
Comment Utility
Thank you for the quick response but now I get Run-time error '3065'; Cannot execute a select query. The query itself "qryInnerJoinBeforeAfter" is an inner join query....?
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
Comment Utility
Sorry, I used some code that runs an action query. Remove these four lines from the code I previously posted and that should eliminate the error.
        ' run the query,

        qdf.Execute

        DoEvents

        ' delete the query

        .QueryDefs.Delete strQueryName

        DoEvents

Open in new window

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now