Concatenate two fields....

[YouMust]

i have two fields from a linked database that I'd like to join.

I've put this in the control source but i only get the first [PATH] and not the [FILE] in the output this is what I'm using:

=[PATH] & [FILE]

I get the same result if I try to do this in a query:

FULLPATH: [Path] & [FILE]

i only get PATH outputted.

any idea?

 

[theDBguy]

Hi. What does [FILE] contain?

 

[YouMust]

file contains the file name with its extension (ie) book.PDF

https://imgur.com/a/uiiXrZp

 

[isladogs]

OK give us an example of both the path and file combination
I don't want to look at an external link to do so.

 

[YouMust]

hows this?

PATH=\\SPRINT2\REGISTER\HB\HB101943\
FILE= HB101943.PDF

=[PATH] & [FILE]


\\SPRINT2\REGISTER\HB\HB101943\HB101943.PDF

but i just get
\\SPRINT2\REGISTER\HB\HB101943\

 

[theDBguy]

Hi. What do you get with either of these?

=[FILE]

or

=IsNull([FILE])

 

[YouMust]

=[FILE] I get the expected FILE output HB101943.PDF

=IsNull([FILE]) returned 0

Thanks for your help guys

 

[theDBguy]

=[FILE] I get the expected FILE output HB101943.PDF

=IsNull([FILE]) returned 0

Thanks for your help guys

I'm thinking maybe there's a line feed character in there? Try expanding the Textbox to show two rows. Does that help?

 

[YouMust]

ah yes thats it DBguy!

https://imgur.com/a/PVjYrlT

how would i remove that ? im trying to make a hyper link from the concatenation

 

[theDBguy]

ah yes thats it DBguy!

https://imgur.com/a/PVjYrlT

how would i remove that ? im trying to make a hyper link from the concatenation

How did those values get put into the table?

 

[YouMust]

they were placed in the pervasive database by Globalshop solutions ERP software

 

[theDBguy]

they were placed in the pervasive database by Globalshop solutions ERP software

So, I am guessing we're not trying to modify them, correct? You just wanted to display them correctly, right? If so, we'll need to determine first what it is. Can you try the following, please?

=InStr([PATH],Chr(13))

and

=InStr([PATH],Chr(10))

 

[isladogs]

As a line feed is normally two characters, try this

FullPath: Left([Path], Len([Path])-2) & [File]

 

[YouMust]

=InStr([PATH],Chr(13)) = 0

=InStr([PATH],Chr(10)) = 0

If I use trim it shows fine (no additional lines) but i cant get it to concatenate:
https://imgur.com/a/MpRdifY

 

[YouMust]

as a line feed is normally two characters, try this

fullpath: Left([path], len([path])-2) & [file]

doesnt seem to work output=

\\sprint2\register\hb\hb101943\                                                                                                                                                                                                                         hb101943.pdf

 

[isladogs]

OK I'm just going out. Can you test Len(Path) and compare with the numbers of printable characters in the same string. If there is a difference, replace the -2 with the actual difference obtained

 

[theDBguy]

=InStr([PATH],Chr(13)) = 0

=InStr([PATH],Chr(10)) = 0

If I use trim it shows fine (no additional lines) but i cant get it to concatenate:
https://imgur.com/a/MpRdifY

If both those tests failed, then it's not a line feed or a carriage return character. Just as another test, take one of the paths and count the number of visible characters in it. Then, try the following test:


=Asc(Mid([PATH],X+1,1))


Where X is the number of characters you counted.

 

[YouMust]

\\SPRINT2\REGISTER\HB\HB101943\HB101943.PDF

that seems to work!!

Will this work for all PATH lengths ?

 

[YouMust]

If both those tests failed, then it's not a line feed or a carriage return character. Just as another test, take one of the paths and count the number of visible characters in it. Then, try the following test:


=Asc(Mid([PATH],X+1,1))


Where X is the number of characters you counted.

I'll give that a go right away

 

[YouMust]

If both those tests failed, then it's not a line feed or a carriage return character. Just as another test, take one of the paths and count the number of visible characters in it. Then, try the following test:


=Asc(Mid([PATH],X+1,1))


Where X is the number of characters you counted.

here we go:

=Asc(Mid([PATH],[31]+1,1))


#NAME?