Mid vs. Mid$ and proper use if these functions!

[Nawab]

Hey can anyone explain to me the difference between Mid and Mid$ ....i
notice sometimes when i a running the update query with Mid$ it tells
me that for example 3 records will be updated ( which in theory is the
right amount based on the example) however sometimes only two records
are updated! Also in the Mid function i am not specifying the last
argument because I need the remainder of the string

example Mid([field1], 4) i am doing this becasue i want everything
from the 4th character till the end of the string.....is this approach
ok...

please advise
nawab

[Wayne Morgan]

The difference between the functions with the $ and those without is how
they handle Nulls. The ones with the $ are text only and will give you an
error if you pass them a Null.

As for your second question, if it does what you want it's ok.

--
Wayne Morgan
Microsoft Access MVP
"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

Hey can anyone explain to me the difference between Mid and Mid$ ....i
notice sometimes when i a running the update query with Mid$ it tells
me that for example 3 records will be updated ( which in theory is the
right amount based on the example) however sometimes only two records
are updated! Also in the Mid function i am not specifying the last
argument because I need the remainder of the string

example Mid([field1], 4) i am doing this becasue i want everything
from the 4th character till the end of the string.....is this approach
ok...

please advise
nawab

[Allen Browne]

Mid$() returns a String Variable.
Mid() returns a Variant.

Mid$() will be more efficient when working with strings in VBA code.

Mid() is the only choice when working with fields, because a field might be
Null and strings cannot be Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

Hey can anyone explain to me the difference between Mid and Mid$ ....i
notice sometimes when i a running the update query with Mid$ it tells
me that for example 3 records will be updated ( which in theory is the
right amount based on the example) however sometimes only two records
are updated! Also in the Mid function i am not specifying the last
argument because I need the remainder of the string

example Mid([field1], 4) i am doing this becasue i want everything
from the 4th character till the end of the string.....is this approach
ok...

please advise
nawab

[Nawab]

thanks for your reply....although.....still not sure which func to
use...u say the Mid$ is text only.....that means alphanumeric is ok
but no special character like !#@ etc.....is that
correct....secondly....when im doing an update it tells me im about to
update 3 records in my table...which is the correct amount....but when
i go to the table it is only updating two records....please advise....

regards
nawab


"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<kc*******************@newssvr31.news.prodigy .com>...

The difference between the functions with the $ and those without is how
they handle Nulls. The ones with the $ are text only and will give you an
error if you pass them a Null.

As for your second question, if it does what you want it's ok.

--
Wayne Morgan
Microsoft Access MVP
"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

Hey can anyone explain to me the difference between Mid and Mid$ ....i
notice sometimes when i a running the update query with Mid$ it tells
me that for example 3 records will be updated ( which in theory is the
right amount based on the example) however sometimes only two records
are updated! Also in the Mid function i am not specifying the last
argument because I need the remainder of the string

example Mid([field1], 4) i am doing this becasue i want everything
from the 4th character till the end of the string.....is this approach
ok...

please advise
nawab

[Nawab]

i am still having this problem, when i am running a simple update
query in Access 2000 with Mid or Mid$ it tells me that 3 records will
be updated ( which in theory is the right amount) however most of the
time only two records
are updated! the expression i am using is

[field1]=Mid([field2],4)

does it matter which side of the = the Mid function lies...i ask
because i am simply doing an update where the above expression is
true...field1 and field2 are from two different tables...also i want
every character from the 4th position onwards, whereas the Mid
function requires a third arguement for number of characters(length)
to return, which i am excluding...becasue i want till end of
string....could that approach be why i am not getting every record
updated.....please someone help!

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...

Mid$() returns a String Variable.
Mid() returns a Variant.

Mid$() will be more efficient when working with strings in VBA code.

Mid() is the only choice when working with fields, because a field might be
Null and strings cannot be Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

Hey can anyone explain to me the difference between Mid and Mid$ ....i
notice sometimes when i a running the update query with Mid$ it tells
me that for example 3 records will be updated ( which in theory is the
right amount based on the example) however sometimes only two records
are updated! Also in the Mid function i am not specifying the last
argument because I need the remainder of the string

example Mid([field1], 4) i am doing this becasue i want everything
from the 4th character till the end of the string.....is this approach
ok...

please advise
nawab

[Allen Browne]

If you place this expression in the Update row of a query:
[field1]=Mid([field2],4)
Access evalues the expression as True or False.
If it's true, the value of your field updates to True.
If it's false, the value of your field updates to False.

If you intended to update Field1 to be everything from the 4th char of
Field2 onwards, remove the expression. Place this expression in the Update
row of Field1:
Mid([field2],4)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

i am still having this problem, when i am running a simple update
query in Access 2000 with Mid or Mid$ it tells me that 3 records will
be updated ( which in theory is the right amount) however most of the
time only two records
are updated! the expression i am using is

[field1]=Mid([field2],4)

does it matter which side of the = the Mid function lies...i ask
because i am simply doing an update where the above expression is
true...field1 and field2 are from two different tables...also i want
every character from the 4th position onwards, whereas the Mid
function requires a third arguement for number of characters(length)
to return, which i am excluding...becasue i want till end of
string....could that approach be why i am not getting every record
updated.....please someone help!

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...

Mid$() returns a String Variable.
Mid() returns a Variant.

Mid$() will be more efficient when working with strings in VBA code.

Mid() is the only choice when working with fields, because a field might be Null and strings cannot be Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

Hey can anyone explain to me the difference between Mid and Mid$ ....i
notice sometimes when i a running the update query with Mid$ it tells
me that for example 3 records will be updated ( which in theory is the
right amount based on the example) however sometimes only two records
are updated! Also in the Mid function i am not specifying the last
argument because I need the remainder of the string

example Mid([field1], 4) i am doing this becasue i want everything
from the 4th character till the end of the string.....is this approach
ok...

please advise
nawab

[Bob Quintal]

na******@hotmail.com (Nawab) wrote in
news:2b**************************@posting.google.c om:

thanks for your reply....although.....still not sure which
func to use...u say the Mid$ is text only.....that means
alphanumeric is ok but no special character like !#@
etc.....is that correct....
Not correct. Mid$() returns a text value: any character that can
be created with the chr(n) function, given n is 1 to 255.

The Mid() function returns all those plus chr(0), the null
character.

secondly....when im doing an update it tells me im about to update 3 records in my table...which
is the correct amount....but when i go to the table it is only
updating two records....please advise....
Perhaps it is updating three records, but it may be updating the
record you think it is not with a null character.

Possible Reason: Given Mid([field1], 4), is field1 more than 4
characters in length?
Is field1 found for this row?
is field1 null?

Note that a query that updates the same record twice, once with
the correct value, and later with another value, will still show
the same "about to update 3 rows"

build a select query containing all the criteria for your update
query, and display field 1. Check and make sure.

Bob


regards
nawab


"Wayne Morgan" <co***************************@hotmail.com>
wrote in message
news:<kc*******************@newssvr31.news.prodigy .com>...

The difference between the functions with the $ and those
without is how they handle Nulls. The ones with the $ are
text only and will give you an error if you pass them a Null.

As for your second question, if it does what you want it's
ok.

--
Wayne Morgan
Microsoft Access MVP
"Nawab" <na******@hotmail.com> wrote in message
news:2b**************************@posting.google.c om...

> Hey can anyone explain to me the difference between Mid and
> Mid$ ....i notice sometimes when i a running the update
> query with Mid$ it tells me that for example 3 records will
> be updated ( which in theory is the right amount based on
> the example) however sometimes only two records are
> updated! Also in the Mid function i am not specifying the
> last argument because I need the remainder of the string
>
> example Mid([field1], 4) i am doing this becasue i want
> everything from the 4th character till the end of the
> string.....is this approach ok...
>
> please advise
> nawab