Query Field Name based upon Text Box value

[Malcolm17]

Hello,

I have created a grid using a table Fields are DinnerTable, 18:00, 18:15, 18:30, 18:45 etc

On a form I have a textbox named txtGrid1 and a button

I'm looking to run a query where if the textbox value was 18:30 then my query fields would be 'DinnerTable' and '18:30' - however I cannot work out how to make the field names variable (if txtGrid1 = 18:30 then field name = 18:30, OR if txtGrid1 = 18:00 then field name = 18:00 etc)

Is this possible to make query field names variable please?

Thank you,

Malcolm

 

[Gasman]

Your DB is incorrect.
You would have one field that holds time and day. Then just query that data.

Your way, you will need to build the sql string in VBA with concatenation and set that form's record source to that string.
Always debug.print your sql before even trying to use it.

 

[GPGeorge]

Hello,

I have created a grid using a table Fields are DinnerTable, 18:00, 18:15, 18:30, 18:45 etc

On a form I have a textbox named txtGrid1 and a button

I'm looking to run a query where if the textbox value was 18:30 then my query fields would be 'DinnerTable' and '18:30' - however I cannot work out how to make the field names variable (if txtGrid1 = 18:30 then field name = 18:30, OR if txtGrid1 = 18:00 then field name = 18:00 etc)

Is this possible to make query field names variable please?

Thank you,

Malcolm

What you describe as a table with multiple fields that each contain values of the same thing, "dinnertimes" (18:00, 18:15, 18:30, 18:45, etc.), is commonly referred to as "repeating columns" or "spreadsheet design". It's actually a fairly common mistake among newcomers to Access who have previous experience with Excel.

Here is a series of blog posts describing the problem and demonstrating how to correct it. http://www.rogersaccessblog.com/category/problems-with-repeated-columns/

 

[mike60smart]

Hello,

I have created a grid using a table Fields are DinnerTable, 18:00, 18:15, 18:30, 18:45 etc

On a form I have a textbox named txtGrid1 and a button

I'm looking to run a query where if the textbox value was 18:30 then my query fields would be 'DinnerTable' and '18:30' - however I cannot work out how to make the field names variable (if txtGrid1 = 18:30 then field name = 18:30, OR if txtGrid1 = 18:00 then field name = 18:00 etc)

Is this possible to make query field names variable please?

Thank you,

Malcolm

Can you upload your database so we can see your table structures/ relationships?

 

[The_Doc_Man]

I'm going to presume that this part of your question, "DinnerTable, 18:00, 18:15, 18:30, 18:45", represents some type of restaurant reservation system? Is that a correct assumption?

Typically, this kind of "scheduling" app uses a "reservation" table that has three primary elements (and could have more). You have "table number" as a field, you have "start time" as a field, and you have EITHER "estimated end time" or "estimated duration" as a field. This lets you decide what table resources are available or occupied at any given time. Before we go down this path, can you verify your intention or did I read something that wasn't really there?

 

[Ken Sheridan]

The attached little demo file illustrates the basics of the sort of reservations database which The_Doc_Man describes. Its model is:

The central functionality of the database is that it detects intersecting date ranges (date/time ranges in your case) to determine when a room ( table in your case) is available for the whole of a specified range. In addition to the auxiliary Calendar table (Calendar_1 in the above is a second instance of the table, not a separate table per se), you'd need a table of all 15 minute time slots.