If statement to read from table

[ekulrenlig]

I am trying to make an If statement in a macro that will run a query based on a record in a table. The table contains one record with a 2 digit state code that is manually changed by the user, which determines how certain make table queries are run from a set of data. So I'd like the If statement macro to run certain queries based on this state code. The If statement won't let me enter the table and field names though. Please help. Let me know if this doesn't make any sense - I'm not new to access, but am still learning and definitely have a lot of trial and error.

[June7]

If statement cannot directly reference table/query. Must use domain aggregate functions (DLookup, DSum, etc).

Why are you using MAKE TABLE sql action? A process that programmatically modifies db design is usually a bad idea.

[ekulrenlig]

Well, I'm using Access in a non-typical fashion probably. My data is collected in the field, exported to Excel from a separate program, and then imported into Access. I then use Make Table queries to manipulate that data into the proper format that I need based on the state that the data came from. Each state has different data collection methods, so I have a table "Current State" that the user enters the two digit state code, which determines how the data is manipulated. I then run several select queries on the data to essentially check for errors or missing data. These queries are all built into 1 macro, so they all run at once, and then I check through them and correct the errors. So I only want certain select queries to run in the macro based on the state code in the "Current State" table. I thought an If, then statement would work but I can't seem to figure it out. If [Current State].[State Code] = "VA" Then --> Open Query --> if not then continue to the next Action. Does this make any sense at all?

[June7]

Use DLookup to pull the state value from table.

However, users should not work directly with tables and queries, only forms and reports. User can input the state code into a textbox on form. Then the macro can reference the textbox.

I still don't understand why you need MAKE TABLE.

What method do you use to import? Could you just set link to the Excel?

[ekulrenlig]

Excellent! DLookup seems to be working. Thank you very much!
Like I said, I'm probably not using Access properly, but it works for what I'm doing. I'll try to explain it a little better, if you care. The raw data collected in the field is tree inventory plot data, which is export to excel in two separate tables. I use a macro to import the excel data into two existing tables. The tree data has a Product field, which is either downgraded or left as the default in the field. When it is left as the default, the Product is determined by the diameter of the tree. So I have a Product table which assigns that Product based on the tree diameter and species. Each State has different species and Product diameter specifications. So then I use a query to essentially combine the two tables into one "Formatted Data" table, and assign the correct Product depending on which state the tree data is from. I misspoke though, it is an Append query not Make Table, but I do clear the existing data first before importing/appending new raw data that needs to be checked for errors.