more help with programming!

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

Guest

okay. what the engineer did is this

took a long number and converted it into a brief number but not sure exactly
what is going on with this code. The original number is this 200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))

Thank you
 
JudyT said:
okay. what the engineer did is this

took a long number and converted it into a brief number but not sure exactly
what is going on with this code. The original number is this 200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))
Click to expand...


How do you want it to sort? Or am I missunderstnding what
you mean by "sort out correctly"?

If you want to sort in the result, then you should get rid
of the Val function.

If you want it in some other style, tell what you do want.
 
okay my apologies first!

The information that I have below is what the engineer wrote in the query
but the issue is this when it converts it to brief this is what we get
06-16-04. what positino are these numbers pulling from; from the brief1
conversion.

Thank you for your help in advance.

Marshall Barton said:
JudyT said:
okay. what the engineer did is this

took a long number and converted it into a brief number but not sure exactly
what is going on with this code. The original number is this 200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))
Click to expand...


How do you want it to sort? Or am I missunderstnding what
you mean by "sort out correctly"?

If you want to sort in the result, then you should get rid
of the Val function.

If you want it in some other style, tell what you do want.
Click to expand...
 
okay. what the engineer did is this

took a long number and converted it into a brief number but not sure exactly
what is going on with this code. The original number is this 200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))
Click to expand...

I would expect this expression to return 06-00016-004. Is that a) not what you
get? or b) not what you expect? What DO you expect, and what do you get?

Is the datathpe of the field Text or is it Number? It should be Text.

John W. Vinson [MVP]
 
Hey John,

Okay, it returns this as text 06-16-04. As I said, not sure how it is
pulling out the fields for this end result. Can you help me?

John W. Vinson said:
okay. what the engineer did is this

took a long number and converted it into a brief number but not sure exactly
what is going on with this code. The original number is this 200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))
Click to expand...

I would expect this expression to return 06-00016-004. Is that a) not what you
get? or b) not what you expect? What DO you expect, and what do you get?

Is the datathpe of the field Text or is it Number? It should be Text.

John W. Vinson [MVP]
Click to expand...
 
Okay, it returns this as text 06-16-04. As I said, not sure how
it is pulling out the fields for this end result.
Click to expand...

Whatever is going on, it looks like a mess to me. It seems that
you're using a very non-standard method of storing a date value. If
you need to pull out individual parts of that and you really do need
this non-standard format, then you really ought to store the parts
in different fields.

Or, use a real date field and format it for display in the different
contexts. This:

Format(Date(),"yyyy0000dd0mm")

seems to me to return the original format.
 
The Mid function will extract the specified # of characters from a string,
starting at the specified position. The value it returns is a String.

Mid(String, StartPosition, #ofCharacters)

Mid("200600016004",3,2) = "06"
Mid("200600016004",5,5) = "00016" (Val() then turns the string into a
number, thereby stripping the leading zeros)
Mid("200600016004",10,3) = "004" (again, Val() strips the leading zeros.

all this boils down to:
"06" & "-" & 16 & "-" & 4

Which should equal "06-16-4". You say you get "06-16-04". I don't know why
you get "04".

HTH,


JudyT said:
Hey John,

Okay, it returns this as text 06-16-04. As I said, not sure how it is
pulling out the fields for this end result. Can you help me?

John W. Vinson said:
okay. what the engineer did is this

took a long number and converted it into a brief number but not sure
exactly
what is going on with this code. The original number is this
200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you
clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))
Click to expand...

I would expect this expression to return 06-00016-004. Is that a) not
what you
get? or b) not what you expect? What DO you expect, and what do you get?

Is the datathpe of the field Text or is it Number? It should be Text.

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
The result you will get is 06-16-4, not 06-16-04
But what result do you want to get?

If your question is more a matter of what the Mid function
is doing, read George's reply or search for Mid in the VBA
Editor Help.
--
Marsh
MVP [MS Access]

okay my apologies first!

The information that I have below is what the engineer wrote in the query
but the issue is this when it converts it to brief this is what we get
06-16-04. what positino are these numbers pulling from; from the brief1
conversion.


Marshall Barton said:
JudyT said:
okay. what the engineer did is this

took a long number and converted it into a brief number but not sure exactly
what is going on with this code. The original number is this 200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))
Click to expand...


How do you want it to sort? Or am I missunderstnding what
you mean by "sort out correctly"?

If you want to sort in the result, then you should get rid
of the Val function.

If you want it in some other style, tell what you do want.
Click to expand...
Click to expand...
 
I was finally able to talk with the engineer and he said exactly what you are
saying. Starting position and then no of chars for the string. Thank you
for your help!

George Nicholson said:
The Mid function will extract the specified # of characters from a string,
starting at the specified position. The value it returns is a String.

Mid(String, StartPosition, #ofCharacters)

Mid("200600016004",3,2) = "06"
Mid("200600016004",5,5) = "00016" (Val() then turns the string into a
number, thereby stripping the leading zeros)
Mid("200600016004",10,3) = "004" (again, Val() strips the leading zeros.

all this boils down to:
"06" & "-" & 16 & "-" & 4

Which should equal "06-16-4". You say you get "06-16-04". I don't know why
you get "04".

HTH,


JudyT said:
Hey John,

Okay, it returns this as text 06-16-04. As I said, not sure how it is
pulling out the fields for this end result. Can you help me?

John W. Vinson said:
On Tue, 11 Sep 2007 11:16:01 -0700, JudyT
<[email protected]>
wrote:

okay. what the engineer did is this

took a long number and converted it into a brief number but not sure
exactly
what is going on with this code. The original number is this
200600016004.
When I count from left to right, the (2) being number 1 position the (0)
being the second position, it just doesn't sort out correctly. Can you
clear
things up for me.

Brief1: (Mid([WorkLog]![COMMITMENT#],3,2)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],5,5)) & "-" &
Val(Mid([WorkLog]![COMMITMENT#],10,3))

I would expect this expression to return 06-00016-004. Is that a) not
what you
get? or b) not what you expect? What DO you expect, and what do you get?

Is the datathpe of the field Text or is it Number? It should be Text.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
I was finally able to talk with the engineer and he said exactly what you are
saying. Starting position and then no of chars for the string.
Click to expand...

Ummm... pressing F1 or selecting Help from the menu and searching for Mid
would have told you the same thing too.

John W. Vinson [MVP]
 
Ummm... pressing F1 or selecting Help from the menu and searching for Mid
would have told you the same thing too.
Click to expand...

Well, that's true for 90% of forum posts, isn't it?

Besides, if everyone did that before posting, what would all of us do in our
copious spare time?

:-)
 
Well, that's true for 90% of forum posts, isn't it?
Click to expand...

Not unless Microsoft did a major fix on searching in the Help file while I
wasn't looking... said:
Besides, if everyone did that before posting, what would all of us do in our
copious spare time?
Click to expand...

hmmmm... online cribbage anyone?

John W. Vinson [MVP]
 
Back
Top