Can I Build an Access Statement (in 2007) as a String and Execute

Hi,
I know that I can build a SQL statement within a string and then execute it 
like the following:
    strSQL = "UPDATE tblAuditRpt " _
                & "SET tblAuditRpt.AuditRptYesCount = " & 
lngInitialEvalCount & " " _
                & "WHERE tblAuditRpt.AuditRptID=25;"
    CurrentDb.Execute strSQL, dbFailOnError

Can I do the same with an Access statement itself?  For example, How could I 
execute the following statement if I built it in a string?

lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetailInitialEval", 
"(((AuditDetailInitialEval.Medicare)=On)  AND 
((AuditDetailInitialEval.DateofService) Between tblAuditDateRange.StartDate 
And tblAuditDateRange.EndDate))")
Thanks,
Terryomsn
0
Utf
4/23/2010 2:21:01 AM
access 16762 articles. 3 followers. Follow

3 Replies
1009 Views

Similar Articles

[PageSpeed] 18

On Thu, 22 Apr 2010 19:21:01 -0700, Terry
<Terry@discussions.microsoft.com> wrote:

Ironically: check out the Eval function in the Help file.
Debug.Print Eval("2+3")
5
Not sure you can use it for assignments though. Since you have Option
Explicit on per best practices, and lngInitialEvalCount is potentially
an undeclared variable, this likely will not work. But it may be a
step in the right direction nevertheless.

-Tom.
Microsoft Access MVP


>Hi,
>I know that I can build a SQL statement within a string and then execute it 
>like the following:
>    strSQL = "UPDATE tblAuditRpt " _
>                & "SET tblAuditRpt.AuditRptYesCount = " & 
>lngInitialEvalCount & " " _
>                & "WHERE tblAuditRpt.AuditRptID=25;"
>    CurrentDb.Execute strSQL, dbFailOnError
>
>Can I do the same with an Access statement itself?  For example, How could I 
>execute the following statement if I built it in a string?
>
>lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetailInitialEval", 
>"(((AuditDetailInitialEval.Medicare)=On)  AND 
>((AuditDetailInitialEval.DateofService) Between tblAuditDateRange.StartDate 
>And tblAuditDateRange.EndDate))")
>Thanks,
>Terryomsn
0
Tom
4/23/2010 2:50:48 AM
1234546
"Terry" <Terry@discussions.microsoft.com> д����Ϣ����:B814CD19-5EEF-4109-8C1A-30A6A59347DC@microsoft.com...
> Hi,
> I know that I can build a SQL statement within a string and then execute 
> it
> like the following:
>    strSQL = "UPDATE tblAuditRpt " _
>                & "SET tblAuditRpt.AuditRptYesCount = " &
> lngInitialEvalCount & " " _
>                & "WHERE tblAuditRpt.AuditRptID=25;"
>    CurrentDb.Execute strSQL, dbFailOnError
>
> Can I do the same with an Access statement itself?  For example, How could 
> I
> execute the following statement if I built it in a string?
>
> lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetailInitialEval",
> "(((AuditDetailInitialEval.Medicare)=On)  AND
> ((AuditDetailInitialEval.DateofService) Between 
> tblAuditDateRange.StartDate
> And tblAuditDateRange.EndDate))")
> Thanks,
> Terryomsn 


0
834976220
4/24/2010 1:23:29 AM
> execute the following statement if I built it in a string?

You would write it into a function in code module,
then call eval or run or runcode or (several other
options).

Writing code into a code module is one of those
things that sort of exists for backward compatibility,
but gets flakier and more difficult every version.

So the more generally correct answer is, don't do it that way.

In the small sense, the problem with your code is
that "lngInitialEvalCount" should be an object or
a property, not a VBA variable.

In the large sense, the problem with your code is
that if you need to evaluate that expression, you
have chosen the wrong approach,

(david)


"Terry" <Terry@discussions.microsoft.com> wrote in message 
news:B814CD19-5EEF-4109-8C1A-30A6A59347DC@microsoft.com...
> Hi,
> I know that I can build a SQL statement within a string and then execute 
> it
> like the following:
>    strSQL = "UPDATE tblAuditRpt " _
>                & "SET tblAuditRpt.AuditRptYesCount = " &
> lngInitialEvalCount & " " _
>                & "WHERE tblAuditRpt.AuditRptID=25;"
>    CurrentDb.Execute strSQL, dbFailOnError
>
> Can I do the same with an Access statement itself?  For example, How could 
> I
> execute the following statement if I built it in a string?
>
> lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetailInitialEval",
> "(((AuditDetailInitialEval.Medicare)=On)  AND
> ((AuditDetailInitialEval.DateofService) Between 
> tblAuditDateRange.StartDate
> And tblAuditDateRange.EndDate))")
> Thanks,
> Terryomsn 


0
david
4/24/2010 10:36:32 AM
Reply:

Similar Artilces:

Access 97 can't resize database window
My database window with the listings of forms tables etc was adjusted to a smaller width, but resizing it is completely disabled and renders Access 2007 utterly useless for me. Is there anyway to 'reset' the window? ...

How to link with an Access DB
Hi, I have an Acces DB with many tables. I need to choose the name of a customer in a cell of Excel. For example, in acces I have this tables: Table1 Id Name Last Name City I need to choose the last name from a drop down menu in a spreadsheet and then in other cell I need to put all the data regarding the last name that I choose. I hope to be exaustive, and sorry for my english. :-) Many Thanks Stefano ...

Accessing Windows Office Templates
There are some very nice templates at the Windows Office Template Gallery. I would like to download (mostly Word, but a few Excel) some of these templates, but the format comes out with a ".cab" extension that Office v.X doesn't understand. Is there any way to get these templates? Unfortunately, they make the Mac template archive look very weak. TIA Len Hi Len, Microsoft has a Mac template download page: http://www.microsoft.com/mac/resources/templates.aspx?pid=templates There's a link to the on-line gallery on that page. It looks like a newly re-designed gallery to m...

Access 2003 resets form variables during debugging while the form is open
Hello, I am using MS Access 2003. (FWIW, this is about an ADP project, not MDB.) A form has a variable that is initialized (by some function call) in Form_Load() and then stays unchanged for the form's entire lifetime. (In a language more advanced than VBA, it would be a constant, not a variable; unfortunately, in VBA I cannot initialize a constant by non- constant expression). Specifically, in my case it looks like this: Private sTempFileName as String ... Private Sub Form_Load() ... sTempFileName = GenerateTempFileName(...) ... End Sub Priv...

can't send or receive emails
this just happened this week. I get this message....The connection to the server has failed. Subject 'help', Account: 'mail.comcast.net', Server: 'smtp.comcast.net', Protocol: SMTP, Port: 80, Secure(SSL): No, Socket Error: 10060, Error Number: 0x800CCC0E........your advice to to change smtp port to 25 or 587 or 80 does not work???? Port 80 is definitely wrong for that server; whose advice was that? Comcast wants you to use these email settings: http://www.comcast.com/Customers/FAQ/FaqDetails.ashx?Id=3D2288 Note that the outgoing server now requires port 5...

How can I get 32-bit Integers?
I am using Access MS Office 2007 with VBA 6.5, on Win XP. My Integer type is just 16 bits, i.e. its max value is 32,767. How can I configure it so as to have 32-bit Integers, along with 64-bit Longs? "Renny Bosch" <noname@nospam.com> wrote in message news:OqqNyqmuKHA.4492@TK2MSFTNGP05.phx.gbl... > I am using Access MS Office 2007 with VBA 6.5, on Win XP. My Integer type > is just 16 bits, i.e. its max value is 32,767. How can I configure it so > as to have 32-bit Integers, along with 64-bit Longs? An Access Integer is 16 bits. A Long Integer is...

Can I copy radio buttons so that the second set is independent of.
I have a group of four radio buttons that all point to one cell. My goal is to create a second set that looks identical to the first but which points to a second cell. This is for a questionaire that will have many questions with the same four answer values. I want to be able to quickly create 100 button groups that will each update a separate cell for scoring of the questionaire. There are optionbuttons on the Control toolbox toolbar and there are optionbuttons on the Forms toolbar. Each has different behaviors. If I had to use lots, I'd use the Forms version. Here's a pos...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

OWA. CA's. and Exchange 2007
The other day, I had to renew the Certificate on my Exchange 2007 server. Everything seemed to go alright, but eventually I received complaints that outbound email wasn't being sent. Looking at EMC showed all of the email queued up and waiting for something. After I relaxed the settings on my router (Allowed all outbound SMTP instead of restricting it to the Exchange server sending to MxLogic) all of the outbound email went though. I also was receiving reports of Webmail being broken (which was halfway fixed with the help of http://www.microsoft.com/technet/support/ee/transform.aspx?Pr...

How do I get Powerpoint 2003 fade in 1 by 1 in 2007
I frequently used the 'fade in one by one' feature in Powerpoint 2003, which allowed me to fade in individual bullet points by way of a click of a mouse. Not only is this feature now not standard in 2007, but I can't work out how to create it: anything that I find, tends, instead, to fade the bullet points in on some kind of automatic time schedule, rather than - as I want - on a mouse click. I want only to use text, no sounds, no pictures, and nothing fancy. Currently, I am reduced to copying my material into an old Powerpoint 2003 presentation, but this is silly....

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

Outlook 2007 addresses
How can I find the list of addresses I have sent emails to? When you start a new email you can put a letter in the To line and list will come up. Where is that list? How can I print it out? Thanks Matt that is the autocomplete cache. You would need a utility to print it out. http://www.outlook-tips.net/archives/2004/20040621.htm http://www.slipstick.com/problems/nicknamecache.asp -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Out...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Outlook 2007 Install gone wrong
We recently switched e-mail hosts and I successfully (mostly) switched over my company's accounts from a POP server to our new IMAP server. But I'm trying to set up one more account and am running into issues. I put in all the same settings as for our other accounts, but it won't allow me to create or copy any sub-folders into the InBox folder for this account (it tells me it cannot and this is "probably due to a limitation of your imap server")...but I can do so for the other accounts on the same server. Also, when I try to view the Imap Folders, it says "The...

users with restricted access
We have some users that we have directed to only get their information from a report that has been set up. Because of that, I set up a parameter query to make the information more easy to see. The parameter query prompts for last name or broker #, is there a way, when the last name is entered to include Jr's & Sr's? Or should this be another field in the table to make the last name field more accurate? ...

Access 97/2000/2003 comparisons
I have a rather large application that uses an access 97 database (DAO). We also have a version that works with access 2000, Oracle, msde, and SQLServer (ADO). We would like to retire the DAO version of the product, but there are places where the ADO version is much slower compared to where we run DAO seeks (very noticeable when looping). So, I have a few questions. 1. Are there any tips/tricks to speed up ADO queries to compare with DAO seeks? 2. Would there be any benifit in using access 2003 over previous versions of the software besides the added features (xml support, etc). 3...

Can you delete Business Alerts?
I cannot see any way to delete Business Alerts, can someone tell me how? I am using GP 8.0 -- Sheri Salomone THANKS! Try going to Cards --> System --> Business Alerts. -- Charles Allen, MVP "Sheri Salomone" wrote: > I cannot see any way to delete Business Alerts, can someone tell me how? > I am using GP 8.0 > -- > Sheri Salomone > THANKS! woo hoo! Thank you! -- Sheri Salomone THANKS! "Charles Allen" wrote: > Try going to Cards --> System --> Business Alerts. > -- > Charles Allen, MVP > > > > "Sheri Salo...

no access to send internet email
Hi, Exchange 2003. Can a mail enabled user be restricted to send internal email only and not internet email? Nich Hello, > Exchange 2003. Can a mail enabled user be restricted to send internal email > only and not internet email? yes, that is possible. There are multiple ways to archive this goal. Depents on your infrastructure,too. If you have mailing to external over an SMTP Connector, try this: First look into your SMTP Connector, you will find Delivery Restrictions there. It is self explaining. If you have trouble with that or you won't find it, then i will need additi...

Why ClassWiard can't see my Class?
I created a class(Class-A), derived a class(Class-B) which derived from CWnd class. When I used mouse right-click on the new-created class, it shows a popup-command-menu which missing "add virtual function" and "add windows message handler". From ClassWizard I couldn't see the new-created class either. I tried to delete myprogram.clw & re-issue ClassWizard, but it doesn't help. Anyone can help ? Thanks, Eagle "EagleChen" <xx@xx> wrote in message news:uRGde941DHA.3496@TK2MSFTNGP11.phx.gbl... > I created a class(Class-A), derived a class(C...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

OE6 can't start due to message store prob
"Outlook Express could not be started. The application was unable to open the Outlook Express message store. Your computer may be out of memory or your disk is full (0x8007000E,5)" This happens after I try to copy in backed up .dbx files. ...

Publisher can not save file
I recently started having troubles with my Publisher 2003. Whenever I go to save my files now, using save as or just the save button, it gives me a dialog box that says "Can not save file." It does that twice, then it disappears. I also noticed that when it does this, it leaves the .tmp files in the directory where I tried to save. I can change the name of the files, and sometimes it will save it. Most of the time not though. I have NAV, and I noticed that it was said there was an issue with Publisher and NAV. Is this the same with the 2005 version, and is this anything anyone...

how can i edit the positioning of the balloon comment in a word fi
how can i edit the positioning of the balloon comment in a microsoft word file ? please reply on my email What you can do is adjust the space reserved for the balloons in the margin. In Word 2007, on the Review tab, click Track Changes, and then click Change Tracking Options. Change the "Preferred width" setting. -- Stefan Blom Microsoft Word MVP "melikelmalik" <melikelmalik@discussions.microsoft.com> wrote in message news:80E5F3D3-04A0-4E81-B154-FA8459B25F00@microsoft.com... > how can i edit the positioning of the balloon comment in a mi...