Extract string from dynamic query fieldname (1 Viewer)

[OTWarrior]

I am building an access database that will create queries based on imported xlsx spreadsheets, so the names of the queries will change.

The system will generate one of the fields names as such

(SQL code with VB)

AS [" & Chr(34) & Left(MultiCboSel, 20) & Chr(34) & " - Mentions] "

Example:
"IssueType - Environmental" - Mentions: Q11 Theme

I need to extract the "Environmental" part (which can be a different word based on the query) to use in a second level query as the criteria.

1) How can I dynamically reference the query field names? In such as way as Db.Query("Queryname").Field(1).name

2) How can I pull out the middle of the string, between two special characters? I can change the special characters for when they are generated, so long as they aren't ones SQL doesn't like.

Thanks all

 

[sneuberg]

If you use querydef objects you have about all the control you need for creating and manipulating queries.

You can pull out the middle of a string by using a combinations of InStr and other string functions like Left, Right and Mid.

 

[OTWarrior]

That has helped, thank you