extract substring from a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using an ADP access database. I am trying to create a query. Just to
make this simple lets say there are currently two varibles in the query,
AccountNum and Date. I am trying to create a third variable called BranchNum,
but its value will vary depending on what AccountNum contains. Lets say
AccountNum = e25f
the second value in AccountNum is the branch number so in this case the
account number is from branch 2. Now BranchNum needs to be set to this value.
I have tried putting something like this into the column:
Mid([AccountNum,2,1]) and the alias would be BranchNum. But when I go to save
the query it gives me this error: ADO error: 'Mid' is not a recognized
function name. I added Microsoft DAO 3.51 object library and I still get the
same error in my query.
Anyone know how I can grab a variable from the middle of a string in a query?
 
I've tried that too and get this error message: ADO error: User does not have
permission to perform this operation on view 'dbo.QryTotals'.

Duane Hookom said:
Use Substring(AccountNum, 2, 1) in place of Mid(....) in ADPs.

--
Duane Hookom
MS Access MVP
--

Benjamin said:
I am using an ADP access database. I am trying to create a query. Just to
make this simple lets say there are currently two varibles in the query,
AccountNum and Date. I am trying to create a third variable called
BranchNum,
but its value will vary depending on what AccountNum contains. Lets say
AccountNum = e25f
the second value in AccountNum is the branch number so in this case the
account number is from branch 2. Now BranchNum needs to be set to this
value.
I have tried putting something like this into the column:
Mid([AccountNum,2,1]) and the alias would be BranchNum. But when I go to
save
the query it gives me this error: ADO error: 'Mid' is not a recognized
function name. I added Microsoft DAO 3.51 object library and I still get
the
same error in my query.
Anyone know how I can grab a variable from the middle of a string in a
query?
Click to expand...
Click to expand...
 
This seems to be a permissions issue, not a syntax issue.

--
Duane Hookom
MS Access MVP
--

Benjamin said:
I've tried that too and get this error message: ADO error: User does not
have
permission to perform this operation on view 'dbo.QryTotals'.

Duane Hookom said:
Use Substring(AccountNum, 2, 1) in place of Mid(....) in ADPs.

--
Duane Hookom
MS Access MVP
--

Benjamin said:
I am using an ADP access database. I am trying to create a query. Just
to
make this simple lets say there are currently two varibles in the
query,
AccountNum and Date. I am trying to create a third variable called
BranchNum,
but its value will vary depending on what AccountNum contains. Lets say
AccountNum = e25f
the second value in AccountNum is the branch number so in this case the
account number is from branch 2. Now BranchNum needs to be set to this
value.
I have tried putting something like this into the column:
Mid([AccountNum,2,1]) and the alias would be BranchNum. But when I go
to
save
the query it gives me this error: ADO error: 'Mid' is not a recognized
function name. I added Microsoft DAO 3.51 object library and I still
get
the
same error in my query.
Anyone know how I can grab a variable from the middle of a string in a
query?
Click to expand...
Click to expand...
Click to expand...
 
any idea how to fix this. I am the creator and administrator of both the sql
database/server and the adp database so I can change anything that may be set
up wrong.

Duane Hookom said:
Use Substring(AccountNum, 2, 1) in place of Mid(....) in ADPs.

--
Duane Hookom
MS Access MVP
--

Benjamin said:
I am using an ADP access database. I am trying to create a query. Just to
make this simple lets say there are currently two varibles in the query,
AccountNum and Date. I am trying to create a third variable called
BranchNum,
but its value will vary depending on what AccountNum contains. Lets say
AccountNum = e25f
the second value in AccountNum is the branch number so in this case the
account number is from branch 2. Now BranchNum needs to be set to this
value.
I have tried putting something like this into the column:
Mid([AccountNum,2,1]) and the alias would be BranchNum. But when I go to
save
the query it gives me this error: ADO error: 'Mid' is not a recognized
function name. I added Microsoft DAO 3.51 object library and I still get
the
same error in my query.
Anyone know how I can grab a variable from the middle of a string in a
query?
Click to expand...
Click to expand...
 
Back
Top