Inherited an Access DB that has queries labelled as MMM-YYYY. How to create a macro just to run queries for 'JAN' or prompted month?

[MikeL]

Queries are similar to:
TEST JAN-2022
TEST FEB-22
FILE JAN-23
All contain a text string with the month ie JAN or FEB

Need syntax and process to create a macro to run only queries with month entered at Prompt

Thanks in advance

 

[welshgasman]

You would need to run through the query collection, inspect each name and see if the entered value is in there.
As all 3 char months are unique, you could just use Instr(),
If found, then run that query, or store the name in a table ready to run.

So google for walking through the query collection.

This should give you a start. AllQueries object (Access)

 

[Micron]

If they all return the same fields but just for different time periods then having a query for each period is quite inefficient. In that case you could create a prompt in the query itself and forget the macro. That's known as a parameter prompt and isn't the best method, but it is the simplest if you don't mind typing values in each time you run the query. How to do that would depend on what your field data looks like.

 

[Joe4]

If they all return the same fields but just for different time periods then having a query for each period is quite inefficient. In that case you could create a prompt in the query itself and forget the macro. That's known as a parameter prompt and isn't the best method, but it is the simplest if you don't mind typing values in each time you run the query. How to do that would depend on what your field data looks like.

Yeah, I agree with Micron. It sounds like a parameter query may be your best bet here. As he said, how to test it up depends on how your data is formatted.
Here is a little video to show you how to return all the records between two dates if your field is an actual Date field:

If your field is actually a string, then you should be able to get by with just one parameter, and use a wildcard, like this:

Like "*" & [Enter 3 letter month abbreviation] & "*"

If your field actually is a date field, and you want to return all JAN entries, regardless of year, I think I would add a calculated field to your query that returns just the 3 letter abbreviation for the month, like this:

MonthAbbr: Format([FieldName], "mmm")

and then use a parameter on this calculated field like this:

[Enter 3 letter month abbreviation]

 

[MikeL]

Thanks for the responses. I am trying to apply. Would a parameter query work when I want to append tables?
My requirements have slightly changes. I have a DB with multiple tables. Would like to run an append of any tables meeting parameter condition?

the tables are not named MMM YY. rather a format like '1-2023' and '1-2022'. Is it possible to prompt for the 1- or 2- , does a parameter query append tables when this query is run?

 

[Joe4]

You should be able to apply a parameter to any sort of query you want.

However, I am bit confused. Are you trying to apply this parameter to data within the table, or to the table names?

 

[MikeL]

To table names, not data within the table

 

[Micron]

No, you cannot pass the table name as a parameter. You would have to write code for that and construct the query sql in that code. I don't see how that question applies to the original ask. Those are awful names for objects - should never start with a number.

 

[MikeL]

Thanks, my original ask was incorrect. Still wondering if can create an append query that prompts for the month number and hyphen and appends only those tables, if this is a more clear ask

 

[Micron]

As I mentioned you cannot use a query parameter prompt to choose a table name. If I'm wrong, I will be happy to learn something.
You will have to do something else: code, or build a form with a combo list of tables but you will still need code. Something has to tell your code which table to run it against. You're trying to put the cart before the horse, so to speak. Why not just create the queries you need and bind them to the proper tables?