looking for a command similar to macro substitution in foxpro

[newnoviceuser]

I will try to explain this as best I can. I hope someone is familiar with access and foxpro. i want to do this in access. In foxpro you can use this
x="q"
q="test"
if you use ?x you get q if you use ?&x you get test.
its called macro substitution.
how do you do that in access vba?

 

[Zathras]

I've not seen it done in Access VBA. You will probably have to program around it.

Excel workbook functions provide an Indirect( ) function, but unfortunately that won't help you.

 

[newnoviceuser]

to program around it would be much more difficult because we use 1 routine in foxpro to operate a hundred different areas and we use a variable like trans&tt where tt can be 1 to a hundred i would have to write a hundred seperate subroutines? these things are all in this program it is used constantly because there a a bunch of different offices which each have a seperate number but use the same program and to make it work now all you have to do is set tt to the right office number
any suggestions?
im pretty affluent in foxpro but brand new to access

 

[Zathras]

I'm not at all clear on what exactly you need to do. If you don't already have 100 subroutines, then why do you need this sort of indirection?

If it is just a matter of formatting a string for use in a WHERE clause, that is trivial: "Office" & OfficeNumber produces "Office123" when OfficeNumber is 123.

Can you help me understand what it is that you are trying to do?

 

[newnoviceuser]

ok here is one example the database has a transaction database and it has records listed as code desription affects type then it lists 24 fields they are an end of month total for the last 2 years.
they are listed as such. lasto1 last02 last03 last04...
and then this year is listed bal01 bal02 bal03 ...
it uses these codes through out the entire program and access the data with something like this

field = "bal"+str(month(date()))
lastyear = "last" + str(month(date()))
store current->&field to current->&lastyear
store transamount to current->&field

something along those lines
or it might ask what range of months would like like to print
then use a for x = 1 to 6 next loop using the str(x) with it to print the appropriate months
does that give u an idea?

the one i am currently working on and where i found the problem was it asks what month you want to calculate searches the database for any transactions with the month in that\date then stores it in the appropriate field for that month.
did I explain it appropriately?
and if so can you offer an easy solution to it?
it also uses this same principle for the ofices each office has its own field in the database and it accesses the correct field by adding the office number to the word office making the correct office
office1 office2 office3 ...
i hope this explains my situation accurately

 

[newnoviceuser]

the long and short of it all is this
I am trying to access a field by naming the field in a memory variable
i need to access a field bal with vaious numbers
with a
rst!bal(0,1,2,3,4,5,6) where the othru 6 are stored in a memory variable

 

[Zathras]

It sounds like you should be able to create the necessary queries by building up the SQL statement in code. If you know SQL, that shouldn't be a problem.

If you have been using Access to create queries in the "Design" mode, you can switch to the SQL mode with View/SQL View from the menu. (There is also a tool on the tool bar.)

You set up the queries using VBA and forms. If this is not making sense, you need to get a book to teach you the essentials of programming Access. You can get an idea from the help file, look up "query" and select the topic "Query Properties Reference" click on "See Also" click on "Set Properties by Using Visual Basic" and display. Finally, click on "Set Form, Report, and Control Properties in Visual Basic" (There's probably an easier way to get there, but it's late and I'm signing off for the night.)

One final thought, you might consider re-structuring your tables so that each office is on a different row (3rd normal form). There will probably always be 24 months in every two years, but the number of offices could change and that will make your job much harder.

 

[newnoviceuser]

I think I understand what you are suggesting and I think I can do it. it should work if I create a query in vba using a sql statement I can label each field in the query then use variables to enter the field i want for each label something like this
"SELECT tblTransactions.Date, tblTransactions.TransNo, tblTransactions." &variable & " AS oldbalance, tblTransactions.LedgerCode, tblTransactions.Type, tblTransactions.Balance FROM tblTransactions;"

is that the idea u were suggesting?
if so I have 1 more question is there a way to edit a query or do I have to delete it everytime to recrate a new query with my variables?
what I am thinking of at this point is to
use .createquerydef
then do my storing using a recordset then use .querydef.delete
would that be the best way?

 

[Zathras]

I'm afraid you've exhausted my working knowledge of Access. I have used it on a couple of projects and it has been able to do what I needed done.

Your last post sounds like it should work ok, but I can't guarantee it.

I urge you to find a local consultant who can assist you on site. If you don't get the initial design correct, it will cost you a lot in the long run. Generally, I design a database from back to front. That is, I try to see all of the types of reports and on-line displays that may be wanted and design the database to make the reporting/viewing as easy as possible. The general principle is that a datum is usually entered once, but retrieved many times.

Good luck, and I hope I helped at least a little bit.

 

[newnoviceuser]

you helped a great deal. it does work I still have a few bugs to work out but it works quite effeciently. I agree it is much easier to work back to front but this program is already written and is so big that rewriting it from the start would take me forever.Plus the databases are already in place and have all the data in it. so Im trying to convert. thanks for the help