Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Syntax to change Marcro query to VBA query

Posted on 2013-11-12
7
Medium Priority
?
270 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
ID: 39642006
I've attached a screen shot as it may help clafiry.
Query.jpg
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39642095
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 2000 total points
ID: 39642145
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:thandel
ID: 39642174
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
ID: 39642196
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
ID: 39642199
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
ID: 39642237
Thanks again, SQL is my weak spot but your eplanations certainly helped.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

824 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