Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CHALLENGE....Access and variables ?

Status
Not open for further replies.

mikefortune

IS-IT--Management
Feb 9, 2001
20
GN
I am use to Crystal Reports and assigning variables in the expression (formula) builder, i.e.: numberVAR = BLAH

I have a very difficult expression in Access and it tells me that it is either to complex or something and try using variables. How do I assign a variable in Access? I will show below what I did in Crystal that worked and what I tried in access that didn't:

Crystal Formula here:
-------------------------------------
Local numberVar Firstpos;
Local numberVar secondpos;

Firstpos:= InStr({OELIN.Description},"-");
Secondpos:= instr(Firstpos+1, {OELIN.Description}, "-");

If Firstpos > 0 and
Secondpos > 0
then
Trim(mid({OELIN.Description}, Firstpos+1, Secondpos - Firstpos -1))

Else
""


Access Expression Here:
---------------------------------------
Expr1: trim(Mid([INMAST]![Desc1],InStr([INMAST]![Desc1],'-'),(InStr(InStr([INMAST]![Desc1],'-')+1,[INMAST]![Desc1],'-'))-(InStr([INMAST]![Desc1],'-'))))
 
Unless there are two fields called Desc1 (in separate tables that have been added to your query) you don't need the "full path" to the field. Anyway, you can basically create the exact same expression you have by setting up the positional fields in the QBE grid. Then use the name you assign them for your final calculation.

Firstpos: InStr(INMAST!Desc1, "-")
Secondpos: InStr(Mid(INMAST!Desc1, Firstpos+1, len(INMAST!Desc1) - Firstpos +1))
Expr1: IIF(Firstpos > 0 AND Secondpos > 0, Trim(INMAST!Desc1, Firstpos + 1, Secondpos - Firstpos - 1))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top