Selecting field from table where field is part of a string

[Guest]

OK, currently I am using the dlookup command.

strSearch = DLookup("[File Name]", "tblImported", "[File Name] = '" &
txtFname & "'")

searches field [File Name] in tblImported where field [File Name] is equal
to txtFname.

The problem I'm running into is this; txtFname will contain a directory and
structure like s:\directory1\directory2\Excel.xls while the field [File Name]
will only contain the file's name. I want to be able to search field [File
Name] where [File Name] is equal to the last part of the string txtFname
which is the file name, and not the directory. How do I go about doing this?

I can't just put the whole directory incase they're importing the same file
from a different directory. I'm just trying to use this as a catch so Excel
spreadsheets only get imported once.

 

[John Nurick]

You can use something like this to strip the file name out of the path
(assuming you're using a recent version of Access):

strFName = Mid(strPath, InstrRev(strPath, "\") + 1)

BTW, if you just want to know if there's a matching record, use DCount
rather than DLookup.

OK, currently I am using the dlookup command.

strSearch = DLookup("[File Name]", "tblImported", "[File Name] = '" &
txtFname & "'")

searches field [File Name] in tblImported where field [File Name] is equal
to txtFname.

The problem I'm running into is this; txtFname will contain a directory and
structure like s:\directory1\directory2\Excel.xls while the field [File Name]
will only contain the file's name. I want to be able to search field [File
Name] where [File Name] is equal to the last part of the string txtFname
which is the file name, and not the directory. How do I go about doing this?

I can't just put the whole directory incase they're importing the same file
from a different directory. I'm just trying to use this as a catch so Excel
spreadsheets only get imported once.

 

[Guest]

John,

I am curious as to why you would use DCount rather than DLookup in a case
like this. Correct me if I am wrong, but DCount must read the entire
recordset, but DLookup will stop at the first match. It would seem a DLookup
would, on average, execute more quickly.

I would appreciate your thoughts on this.

You can use something like this to strip the file name out of the path
(assuming you're using a recent version of Access):

strFName = Mid(strPath, InstrRev(strPath, "\") + 1)

BTW, if you just want to know if there's a matching record, use DCount
rather than DLookup.

OK, currently I am using the dlookup command.

strSearch = DLookup("[File Name]", "tblImported", "[File Name] = '" &
txtFname & "'")

searches field [File Name] in tblImported where field [File Name] is equal
to txtFname.

The problem I'm running into is this; txtFname will contain a directory and
structure like s:\directory1\directory2\Excel.xls while the field [File Name]
will only contain the file's name. I want to be able to search field [File
Name] where [File Name] is equal to the last part of the string txtFname
which is the file name, and not the directory. How do I go about doing this?

I can't just put the whole directory incase they're importing the same file
from a different directory. I'm just trying to use this as a catch so Excel
spreadsheets only get imported once.

 

[John Nurick]

I hadn't thought of it that way; just prefer something like this
If DCount(blah blah) Then
to something like this
If Not IsNull(DLookUp(blah blah)) Then

As for the timing: I imagine that whether it's significant depend on
whether the relevant fields are indexed.

John,

I am curious as to why you would use DCount rather than DLookup in a case
like this. Correct me if I am wrong, but DCount must read the entire
recordset, but DLookup will stop at the first match. It would seem a DLookup
would, on average, execute more quickly.

I would appreciate your thoughts on this.

You can use something like this to strip the file name out of the path
(assuming you're using a recent version of Access):

strFName = Mid(strPath, InstrRev(strPath, "\") + 1)

BTW, if you just want to know if there's a matching record, use DCount
rather than DLookup.

OK, currently I am using the dlookup command.

strSearch = DLookup("[File Name]", "tblImported", "[File Name] = '" &
txtFname & "'")

searches field [File Name] in tblImported where field [File Name] is equal
to txtFname.

The problem I'm running into is this; txtFname will contain a directory and
structure like s:\directory1\directory2\Excel.xls while the field [File Name]
will only contain the file's name. I want to be able to search field [File
Name] where [File Name] is equal to the last part of the string txtFname
which is the file name, and not the directory. How do I go about doing this?

I can't just put the whole directory incase they're importing the same file
from a different directory. I'm just trying to use this as a catch so Excel
spreadsheets only get imported once.

 

[Guest]

My practice is to index any field on which I will be doing any domain
aggreagate functions. It does improve performance. With a small number of
records in the recordset, the difference between the DCount and DLookup would
hardly be noticable; however, with a very large table, it may be noticable.

I see your point on the verbage between the two. My usual practice is when
I will use the value if it returned is to return the DLookup into a variant,
then test the variant for Null before using it.

But, then there are other ways of doing things than mine....
even if they are wrong

I hadn't thought of it that way; just prefer something like this
If DCount(blah blah) Then
to something like this
If Not IsNull(DLookUp(blah blah)) Then

As for the timing: I imagine that whether it's significant depend on
whether the relevant fields are indexed.

John,

I am curious as to why you would use DCount rather than DLookup in a case
like this. Correct me if I am wrong, but DCount must read the entire
recordset, but DLookup will stop at the first match. It would seem a DLookup
would, on average, execute more quickly.

I would appreciate your thoughts on this.

You can use something like this to strip the file name out of the path
(assuming you're using a recent version of Access):

strFName = Mid(strPath, InstrRev(strPath, "\") + 1)

BTW, if you just want to know if there's a matching record, use DCount
rather than DLookup.

On Tue, 14 Feb 2006 07:49:30 -0800, rc51wv

OK, currently I am using the dlookup command.

strSearch = DLookup("[File Name]", "tblImported", "[File Name] = '" &
txtFname & "'")

searches field [File Name] in tblImported where field [File Name] is equal
to txtFname.

The problem I'm running into is this; txtFname will contain a directory and
structure like s:\directory1\directory2\Excel.xls while the field [File Name]
will only contain the file's name. I want to be able to search field [File
Name] where [File Name] is equal to the last part of the string txtFname
which is the file name, and not the directory. How do I go about doing this?

I can't just put the whole directory incase they're importing the same file
from a different directory. I'm just trying to use this as a catch so Excel
spreadsheets only get imported once.