By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Help required on Access SQL queries

P: n/a
Hi - a bit new to access. I'm looking for help on if Access can do
the following:

I have an access database on which I run a SQL query which I export as
an excel spreadsheet for sending to a client.

One field in a record contains an image pathname e.g.
/images/folder1/imagename.jpg

Is there anyway in the SQL query that I can remove the pathname and
just leave the filename e.g.
imagename.jpg?

Another field contains an html page name without the domain name
information.e.g.
test.html

Is there anyway in the SQL query that I can prefix the pagename with
the domain name information e.g.
http://www.domainname.com/test.html

TIA
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You can use functions in your queries. So, you can write a function that
returns the "last" token out of a set of delimited values. Just place the
following function in a standard module.

Public Function sGetLastF(vText As Variant) As String

Dim vBuf As Variant

If IsNull(vText) = False Then
vBuf = Split(vText, "/")
sGetLastF = vBuf(UBound(vBuf, 1))
End If

End Function

Now, in your query builder, just type in

MyFileName:sGetLastF([YouPathNameField])
For the url one, you can just make another expression in the query builder
like:

FullURL:("http://www.domainname.com/" + ([YourURLField]))
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

Nov 12 '05 #2

P: n/a

Hi Guys - thanks for your replies.

If I could trouble you for one more thing.

The image files mentioned earlier are in different directories with
different levels. e.g.

/images/folder1/imagename.jpg
/images/folder2/imagename.jpg
/images/imagename.jpg

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
I don't understand your question here.

The fact of different directories does not change the fact that you want to
return just the file name. What does one have to do with the other.

The function I posted will always return the file name, regardless of how
many folders deep you have.

Perhaps your problem is stated wrong. Did you need something more then a
function that returns the file name?
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #4

P: n/a
Hi Albert - youre absolutely right - I'm being a bit of a dickhead
here.

I checked the records and found that the seperator used in the record
pathname is a backward slash (\) and not a forward slash (/).

I've changed this in your function and it now works fine.

Again thanks a lot for your help

Regards
DAvid


On Mon, 27 Oct 2003 17:29:35 GMT, "Albert D. Kallal"
<NO************@msn.com> wrote:
I don't understand your question here.

The fact of different directories does not change the fact that you want to
return just the file name. What does one have to do with the other.

The function I posted will always return the file name, regardless of how
many folders deep you have.

Perhaps your problem is stated wrong. Did you need something more then a
function that returns the file name?


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.