Urgent Help with Mid

[Graham]

Need help, Thanks in advance

I am trying to get mid as a statement to work but does
not.

I am trying

If Cust01 = -1 Then
Mid([Customers], 1, 1) = "Y"
Else
Mid(Customers, 1, 1) = "N"
End If

The error is Complie Error - Variable Required - Cant
assign to this expression


However, I can get this to work

If Cust01 = -1 Then

Customers = "Y" & Mid(Customers, 2)

Else
Customers = "N" & Mid(Customers, 2)

End If

 

[Ron Weiner]

Graham

Try removing the square brackets around Customers. It looks to me like
customers is suppose to a VBA variable of either a string or varient type in
your implementation.

Ron W

 

[Jeff Boyce]

Graham

From the expression you provided, it seems like you are trying to modify
something (?a field) named [Customer] by replacing the first character with
either a "Y" or a "N".

If you already have a value for "Cust01" (whatever that might be), why do
you need to modify [Customer]?

We aren't there ... can you provide a bit more description of WHAT you are
trying to accomplish? Help us see the big picture. You've described a very
narrow, tightly-defined "HOW".

 

[Graham]

Thanks Jeff,

Customer is a string of 10 char each char is "Y" OR "N"

It is to allow aceess permissions to the customer program

1st char Y = View N = No View
2nd Char Y = Amend N = No Amend
3rd Char Y = Add New Record N = Can't add
4th Char Y = Delete Record N = Can't Delete

and so on

When updating it a check box (Cust01) is used and I was
just trying to put the string back to show an update.

Hope you can understand this

Thanks

 

[SteveS]

Graham,

Left(), Mid(), and Right() are functions that read a substring form a
string. Since they are 'read' functions, this won't work (as you found out):

Mid([Customers], 1, 1) = "Y"


Instead of using the Mid() function, you could use:
1st position:
Customers = "Y" & Right(Customers, 9)

10th position:
Customers = "Y" & Left(Customers, 9)

2nd - 9th positions:
Customers = Left(Customers, Pos - 1) & "Y" & Right(Customers, 10 - Pos)


Another way to change the permissions string would be to use a function like
(UNTESTED AIR CODE!!)

' State = Y or N
' Pos = the char in the string to change (ie 2nd Char or 8th Char)
Public Function SetPermissions(State As String, Pos As Integer) As String
Dim tmp As String

Select Case Pos
Case 1 'change the first char
tmp = State & Right(Customers, 9)
Case 10 'change the last char
tmp = State & Left(Customers, 9)
Case 2 - 9 'all other chars
tmp = Left(Customers, Pos - 1) & State & Right(Customers, 10 -
Pos)
Else 'if Pos out of range
' add better err handling than this
MsgBox "ERROR"
End Select
'return the value
SetPermissions = tmp
End Function


You would call the function like this:

' 1 = 1st char
If Cust01 = True Then
Customers = SetPermissions("Y", 1)
Else
Customers = SetPermissions("N", 1)
End If

'or

' 8 = 8th char
If Cust01 = True Then
Customers = SetPermissions("Y", 8)
Else
Customers = SetPermissions("N", 8)
End If


HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks Jeff,

Customer is a string of 10 char each char is "Y" OR "N"

It is to allow aceess permissions to the customer program

1st char Y = View N = No View
2nd Char Y = Amend N = No Amend
3rd Char Y = Add New Record N = Can't add
4th Char Y = Delete Record N = Can't Delete

and so on

When updating it a check box (Cust01) is used and I was
just trying to put the string back to show an update.

Hope you can understand this

Thanks

-----Original Message-----
Graham

From the expression you provided, it seems like you are trying to modify
something (?a field) named [Customer] by replacing the first character with
either a "Y" or a "N".

If you already have a value for "Cust01" (whatever that might be), why do
you need to modify [Customer]?

We aren't there ... can you provide a bit more description of WHAT you are
trying to accomplish? Help us see the big picture. You've described a very
narrow, tightly-defined "HOW".

--
More info, please ...

Jeff Boyce
<Access MVP>

.

 

[Ron Weiner]

Steve

From the immediate window in Access paste the following fragment

SomeVar = "Ron" : mid(SomeVar,1,1) = "D": ? SomeVar

and hit <enter>

What prints?

On my box in Access 2K I see "Don"

I still think his problem is Square Brackets around Customers though.
Unless he shares more of his code with us we will never find out.

Ron W

Graham,

Left(), Mid(), and Right() are functions that read a substring form a
string. Since they are 'read' functions, this won't work (as you found out):

Mid([Customers], 1, 1) = "Y"


Instead of using the Mid() function, you could use:
1st position:
Customers = "Y" & Right(Customers, 9)

10th position:
Customers = "Y" & Left(Customers, 9)

2nd - 9th positions:
Customers = Left(Customers, Pos - 1) & "Y" & Right(Customers, 10 - Pos)


Another way to change the permissions string would be to use a function like
(UNTESTED AIR CODE!!)

' State = Y or N
' Pos = the char in the string to change (ie 2nd Char or 8th Char)
Public Function SetPermissions(State As String, Pos As Integer) As String
Dim tmp As String

Select Case Pos
Case 1 'change the first char
tmp = State & Right(Customers, 9)
Case 10 'change the last char
tmp = State & Left(Customers, 9)
Case 2 - 9 'all other chars
tmp = Left(Customers, Pos - 1) & State & Right(Customers, 10 -
Pos)
Else 'if Pos out of range
' add better err handling than this
MsgBox "ERROR"
End Select
'return the value
SetPermissions = tmp
End Function


You would call the function like this:

' 1 = 1st char
If Cust01 = True Then
Customers = SetPermissions("Y", 1)
Else
Customers = SetPermissions("N", 1)
End If

'or

' 8 = 8th char
If Cust01 = True Then
Customers = SetPermissions("Y", 8)
Else
Customers = SetPermissions("N", 8)
End If


HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks Jeff,

Customer is a string of 10 char each char is "Y" OR "N"

It is to allow aceess permissions to the customer program

1st char Y = View N = No View
2nd Char Y = Amend N = No Amend
3rd Char Y = Add New Record N = Can't add
4th Char Y = Delete Record N = Can't Delete

and so on

When updating it a check box (Cust01) is used and I was
just trying to put the string back to show an update.

Hope you can understand this

Thanks

-----Original Message-----
Graham

From the expression you provided, it seems like you are trying to modify
something (?a field) named [Customer] by replacing the first character with
either a "Y" or a "N".

If you already have a value for "Cust01" (whatever that might be), why do
you need to modify [Customer]?

We aren't there ... can you provide a bit more description of WHAT you are
trying to accomplish? Help us see the big picture. You've described a very
narrow, tightly-defined "HOW".

--
More info, please ...

Jeff Boyce
<Access MVP>

.

 

[John Vinson]

If Cust01 = -1 Then
Mid([Customers], 1, 1) = "Y"
Else
Mid(Customers, 1, 1) = "N"
End If

The error is Complie Error - Variable Required - Cant
assign to this expression

The Mid() function is designed to *extract* a substring from a text
string - not to *update* a string. It's read-only.

Reading your later reply, I'd suggest a) not storing this composite,
redundant field in your table AT ALL; and b) generating it on the fly
with an expression like

Me![Customer] = IIF(Me![Cust01], "Y", "N") & IIF(Me![Cust02], "Y",
"N") & ... <etc>


John W. Vinson[MVP]
(no longer chatting for now)

 

[Ron Weiner]

John can you explain the following behavior in the Immediate window of
Access 2K.

SomeVar = "Ron" : ? SomeVar, : mid(SomeVar,1,1) = "D" : ? SomeVar<enter>
Ron Don

This is a trick I had used for years to speed up concatenating big strings
in VB3,4,5,6. It is even possible that I used the mid statement this way on
a Commodore 64 way back in the dark ages (when this was actually FUN!!).

Ron W

If Cust01 = -1 Then
Mid([Customers], 1, 1) = "Y"
Else
Mid(Customers, 1, 1) = "N"
End If

The error is Complie Error - Variable Required - Cant
assign to this expression

The Mid() function is designed to *extract* a substring from a text
string - not to *update* a string. It's read-only.

Reading your later reply, I'd suggest a) not storing this composite,
redundant field in your table AT ALL; and b) generating it on the fly
with an expression like

Me![Customer] = IIF(Me![Cust01], "Y", "N") & IIF(Me![Cust02], "Y",
"N") & ... <etc>


John W. Vinson[MVP]
(no longer chatting for now)

 

[Dirk Goldgar]

If Cust01 = -1 Then
Mid([Customers], 1, 1) = "Y"
Else
Mid(Customers, 1, 1) = "N"
End If

The error is Complie Error - Variable Required - Cant
assign to this expression

The Mid() function is designed to *extract* a substring from a text
string - not to *update* a string. It's read-only.

Reading your later reply, I'd suggest a) not storing this composite,
redundant field in your table AT ALL; and b) generating it on the fly
with an expression like

Me![Customer] = IIF(Me![Cust01], "Y", "N") & IIF(Me![Cust02], "Y",
"N") & ... <etc>

Actually, John, there are both a Mid function and a Mid *statement*.
The VBA help file has this to say about the Mid statement:

<quote>
Mid Statement

Replaces a specified number of characters in a Variant (String) variable
with characters from another string.

Syntax
Mid(stringvar, start[, length]) = string

The Mid statement syntax has these parts:

Part Description
----------- ------------------------------------------

stringvar Required. Name of string variable to modify.

start Required; Variant (Long). Character position in
stringvar where the replacement of text begins.

length Optional; Variant (Long). Number of characters to
replace. If omitted, all of string is used.

string Required. String expression that replaces part of
stringvar.

Remarks
---------
The number of characters replaced is always less than or equal to the
number of characters in stringvar.

Note: Use the MidB statement with byte data contained in a string. In
the MidB statement, start specifies the byte position within stringvar
where replacement begins and length specifies the numbers of bytes to
replace.
</quote>

I think Ron Weiner is on the right track. I suspect that [Customers] is
a control on a form, and the Mid statement only works on String or
Variant(String) variables,

 

[John Vinson]

John can you explain the following behavior in the Immediate window of
Access 2K.

Learn something new every day...!

Thanks, Ron and Dirk. I was mistaken.

John W. Vinson[MVP]
(no longer chatting for now)