Creating an Expression that includes a table name and a Yes/No switch setting in a Access 2013.

Question

I am using a very sophisticated parsing application that parses a variety of text data objects into formats that can be used to Import, Append, and Update a large number of Access Tables. The vast majority of the logic used in the target Access 2013 application is composed of tables, macros, and queries. I know that many professional Access developers do not like Access Macros, but for my particular application, these macros are perfect. I have very little VBA or Expression Builder skills, and would like to use an Access IF Macro if possible.

Virtually all of the tables, Macros, and queries contained in the Access portion of my application is reusable by dozens of different imported text tables. Candidly, although this application works great, and I am down to the last stumbling-block before it can be placed on line, It is my long-term objective to move the database portion of this application to SQL.

The Access portion of my application consist includes a series of macros that import the text data and place it into tables, sends a copy of these newly created table to Excel for analysis, and stores a copy of these tables into a long-term data store that will be used for fault-analysis and performance trending. Some of these tables require much less processing than others, and I would like to place two or more conditional IF Macros that would check a code that is passed via the parsing utility, to determine the processing required.

In a worse case scenario, as many as ten different Access tables can be imported, processed, and archived, during a single pass in the applications. However, only about thirty percent of the tables have meaningful information. The remaining seven percent only have information in the first four table and I would like to have a conditional IF statement that would interrogate a switch, and if it was set it would by the processing for the last six tables in the database. I have organized the processing for all ten tables in sequential order, and I would like to place an IF macro after the four table is processed to determine if further processing is required on the remaining six tables. If not, I will exit ACCESS at that point.

I have created a single field table called FullRunSwitch, and have a text field called RunSwitch that can be set to either Yes or No. The parsing utilities will set this switch appropriately, based upon the processing requirement of the amount of text data placed in each table. If possible, I would like a simple IF expression that would interrogate this switch, and determine if further processing is required by the application.

I would assume that it would start with an IF Statement and include both the table name and the field name containing the Yes/No. As I mentioned, I have very limited VBA skills but if this is the only options, I would like to know before I get started.

Any help will be greatly appreciated.

James H. Barnes

******@jhbarnes.net or ******@jhbarnes.com

Answer 1

Wayne,

Your Yes/No suggestion was exactly what I was looking for, and here is a description of how I will use it:

1. Each customer will have from two to seven logical Internet connections running over a single physical link.
2. One end-point connection will be defined as the primary, and all other end-points on the connections will be defined as secondary.
3. A total of nine probe commands will be sent on the primary connection, but only three will be sent on secondary connections.
4. In my parsing application, I have a way of determining what responses are from primary end-points, and what responses are from secondary end points; and it can set the Primary/Secondary (Yes/NO) switch that can be passed to the MS Access application.
5. I have created an Access Autoexec Macro that has a series of nine processing steps.

The only thing that I want to added to this Autoexec Macro is an IF Macro after step three, to check the Yes/No switch. The setting of this switch will determine if all nine processing steps are to be executed, or if processing can stop after the first three steps have been performed.

I am currently using a single-field table for this switch but if this will not work, I can used a Access form or another object. (I am using a single-field table because it was a very simple process to set a Yes/No value in a text field, and then importing this field into an Access table

The Access portion of the application is designed to:

• Run the first three steps, and then;
• Interrogate the setting of the Yes/No switch;
  • If switch is No, Access will Exit and no additional processing is required.
  • If Switch is Yes, Access will perform the remaining six steps of the application and then Exit.

I don't know if this clarifies or further confuses how I intend to use the Yes/No switch, but hopefully the former is the case.

My "so called" Access application is very simple, and is designed to execute as fast as possible. It is currently composed of queries, Macros, and a couple of VBA functions. However, if this project is successful, I will get rid of the macros, and convert the logic to VBA code. 

Again thanks for your help and hope to hear from you soon.

Answer 2

Wayne,

I believe that your suggested approach will work as soon as I correct one error in the new conditional macro.

The Macro appeared to formatted correctly, but I received the following error when I attempted to run this macro:

    "Microsoft Access cannot find the name 'RunPrimary_Table' you entered in the expression."

As you suggested, I changed the format of the RunSwitch field to a Yes/No.

As you recall, the text area (table) that contains the Yes/No field named RunSwitch, is called

RunPrimary_Table. My Macro is formatted as follows:

If       IIf([RunPrimary_Table]![RunSwitch]=-1,"The answer is Yes","The answer is No")

If       [RunSwitch]=0

         QuitAccess

end If

End If

I checked the contents of the RunSwitch field in the RunPrimay_Table, and it was a -1.

I certainly appreciate the help you have provided thus far, and am convinced that everything will work fine as soon as I correct this final error.

BTW: You asked for a brief example of my data, and what I am trying to do with it. I am in the final stage of developing what I believe will be a unique, possibly "game changing" management console, called the Technology Infrastructure Management Console (TIMC). It is cloud based and is designed to manage technology infrastructures of small businesses located anywhere in North America. It does not require any new hardware or software at the small business customer site, and does not require any on-site technical expertise.

It sends a variety of specially-configured ICMP-based probes (packets) to ISP routers, cloud computing applications, DNS servers, etc., and measure network performance and availability. The probe responses are standard ICMP text messages, that are parsed, analyzed, and stored in an Access database.

If you would like to get more information regarding the TIMC,  please visit my website at http://www.jhbarnes.net.

Again, thanks for your the help, and I certainly hope that you have a solution to this last problem that I have encountered.

James H. Barnes

******@jhbarnes.com

Answer 3

Change the format of your field "RunSwitch" from Text to Yes/No

In the macro use

If RunSwitch = -1  (for yes value)

or

If RunSwitch = 0 (for no value)

it would look something like

IIf ( [FullRunSwitch]![RunSwitch] =-1, "The answer is Yes", "The answer is No")

If this is not what you need, could you give a (brief) example of your data and what you are trying to do with it