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]

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

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" <nawab081@hotma il.com> wrote in message
news:2b57a29b.0 401071519.69d3c 022@posting.goo gle.com...[color=blue]
> 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[/color]

[Allen Browne]

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

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" <nawab081@hotma il.com> wrote in message
news:2b57a29b.0 401071519.69d3c 022@posting.goo gle.com...[color=blue]
> 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[/color]

[Nawab]

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

thanks for your reply....althou gh.....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....seco ndly....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....plea se advise....

regards
nawab




"Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message news:<kc1Lb.546 65$UJ7.30700@ne wssvr31.news.pr odigy.com>...[color=blue]
> 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" <nawab081@hotma il.com> wrote in message
> news:2b57a29b.0 401071519.69d3c 022@posting.goo gle.com...[color=green]
> > 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[/color][/color]

[Nawab]

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

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(leng th)
to return, which i am excluding...bec asue i want till end of
string....could that approach be why i am not getting every record
updated.....ple ase someone help!





"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message news:<3ffd5c80$ 0$1746$5a62ac22 @freenews.iinet .net.au>...[color=blue]
> 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" <nawab081@hotma il.com> wrote in message
> news:2b57a29b.0 401071519.69d3c 022@posting.goo gle.com...[color=green]
> > 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[/color][/color]

[Allen Browne]

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

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" <nawab081@hotma il.com> wrote in message
news:2b57a29b.0 401081054.48fd3 6e6@posting.goo gle.com...[color=blue]
> 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(leng th)
> to return, which i am excluding...bec asue i want till end of
> string....could that approach be why i am not getting every record
> updated.....ple ase someone help!
>
>
>
>
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message[/color]
news:<3ffd5c80$ 0$1746$5a62ac22 @freenews.iinet .net.au>...[color=blue][color=green]
> > 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[/color][/color]
be[color=blue][color=green]
> > 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" <nawab081@hotma il.com> wrote in message
> > news:2b57a29b.0 401071519.69d3c 022@posting.goo gle.com...[color=darkred]
> > > 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[/color][/color][/color]

[Bob Quintal]

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

nawab081@hotmai l.com (Nawab) wrote in
news:2b57a29b.0 401080956.78b00 cd3@posting.goo gle.com:
[color=blue]
> thanks for your reply....althou gh.....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....[/color]

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....whe n im doing an update[color=blue]
> 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....plea se advise....[/color]

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

[color=blue]
>
> regards
> nawab
>
>
>
>
> "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com>
> wrote in message
> news:<kc1Lb.546 65$UJ7.30700@ne wssvr31.news.pr odigy.com>...[color=green]
>> 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" <nawab081@hotma il.com> wrote in message
>> news:2b57a29b.0 401071519.69d3c 022@posting.goo gle.com...[color=darkred]
>> > 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[/color][/color]
>[/color]