Solved

Syntax to change Marcro query to VBA query

Posted on 2013-11-12
7
261 Views
Last Modified: 2013-11-12
I have a macro that calls an existing query to run.  I am updating my code to move the macro and use VBA.

I can't determine the syntax for the following SQL command:

 DoCmd.RunSQL "UPDATE Tpatient SET Tpatient.RecallNotice = Date(), Tpatient.RecallNumber = [RecallNumber]+1
WHERE (((Tpatient.RecallNumber)=0) AND ((Tpatient.RecallActive)=-1) AND ((Date()-[TPatient]![LastApmnt])>([TPatient]![RecallFreq]*30))) OR (((Tpatient.RecallNumber)=1) AND ((Tpatient.RecallActive)=-1) AND ((Date()-[TPatient]![RecallNotice])>30) AND ((Tpatient.RecallFreq)>=12)) OR (((Tpatient.RecallNumber)<4) AND ((Tpatient.RecallActive)=-1) AND ((Date()-[TPatient]![LastApmnt])>360) AND ((Date()-[TPatient]![RecallNotice])>30) AND ((Tpatient.RecallFreq)<12))"


Any assistance is create appciated.
0
Comment
Question by:thandel
  • 4
  • 3
7 Comments
 

Author Comment

by:thandel
Comment Utility
I've attached a screen shot as it may help clafiry.
Query.jpg
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
You're using .'s and !'s interchangeably.   Unless there is a form involved named Tpatient as well as a table, use all .'s:
Dim strSQL as string
strSQL =  "UPDATE Tpatient SET Tpatient.RecallNotice = Date(), Tpatient.RecallNumber = [RecallNumber]+1
WHERE (((Tpatient.RecallNumber)=0) AND ((Tpatient.RecallActive)=-1) AND ((Date()-[TPatient].[LastApmnt])>([TPatient]![RecallFreq]*30))) OR (((Tpatient.RecallNumber)=1) AND ((Tpatient.RecallActive)=-1) AND ((Date()-[TPatient].[RecallNotice])>30) AND ((Tpatient.RecallFreq)>=12)) OR (((Tpatient.RecallNumber)<4) AND ((Tpatient.RecallActive)=-1) AND ((Date()-[TPatient].[LastApmnt])>360) AND ((Date()-[TPatient].[RecallNotice])>30) AND ((Tpatient.RecallFreq)<12))"
 DoCmd.RunSQL strSQL

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Simplifying and formatting the code for readability.  You actually don't need the prefixes since there is only one table involved:

Dim strSQL as string
strSQL =  "UPDATE Tpatient " _ 
                & "SET RecallNotice = Date(), " _
                &  "RecallNumber = [RecallNumber]+1 " _
                &  "WHERE (((RecallNumber)=0)  " _
                &   "AND ((RecallActive)=-1)  " _
                &   "AND ((Date()- [LastApmnt])>([RecallFreq]*30))) " _
                &   "OR (((RecallNumber)=1) AND ((RecallActive)=-1)  " _
                &    "AND ((Date()-[RecallNotice])>30) " _
                &    "AND ((RecallFreq)>=12)) OR (((RecallNumber)<4) " _
                &    "AND ((RecallActive)=-1) " _
                &    "AND ((Date()-[LastApmnt])>360) " _
                &     "AND ((Date()-[RecallNotice])>30) "
                &    "AND ((RecallFreq)<12))"
 DoCmd.RunSQL strSQL

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:thandel
Comment Utility
Thanks was all that was needed was to place the original SQL into a string and then use that string?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Pretty much - When there are only tables involved (no form criteria, etc) it is generally just that simple.

Access automatically throws in a lot of garbage - extra parentheses, prefixes, etc which I usually try to clean up for readability.

Form references/criteria however can complicate things  -- you need to separate the references out from the SQL strings so that they get evaluated separately (You have to treat them like variables), so those are never quite this straight forward.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
All that said, I believe the main problem with your original post was the !'s where there should have been .'s
0
 

Author Comment

by:thandel
Comment Utility
Thanks again, SQL is my weak spot but your eplanations certainly helped.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

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