DLookUp with '

[scottian]

Im using a function to autoatically import a bunch of files from a folder. Im trying to use DLookUp to ascertain whether the file has already been imported, but some of the filenames contain ', for example
ACM008_to_8month's .xls. The simplest solution would be to remove the offending character, however once the database has been completed i will handing it over to a seperate team which is why im trying to automate as much as possible.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[PHV]

What is your actual DLookUp code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

At the moment its this:-

If IsNull(DLookup("FileName", "FilesLookUpTable", "FileName ='" & FileNameID & "'")) Then

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[PHV]

Use this:

If IsNull(DLookup("FileName", "FilesLookUpTable", "FileName ='" & Replace(FileNameID, "'", "''") & "'")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

PHV,

thanks for the help, but im still not sorted. I get 'sub or function not defined' and 'Replace' is highlighted as the offender. Any ideas?

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Which version of Access are you using?

 

[scottian]

2007.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Are any references marked MISSING?

 

[scottian]

now youve lost me. How would i find that out.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Open up a code window and look under Tools->References

 

[scottian]

OK. Ive got the references window open but i dont know which reference to choose. So i just checked them all. still 'sub or function not defined' error.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

What I asked was, are any of them marked MISSING?

 

[scottian]

Im looking in the references window, but i cant see that anything is missing. Ive gone down the list selecting each available reference but i cant see 'missing' anywhere. Ive checked the help files but couldnt get any help from. I dont think im doing this correctly. Also ive found that some of the references give an error 'name conflicts with existing module, project or object library' or 'error in loading DLL'.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[PHV]

In fact, uncheck all references you can.
Does then the DLookUp works ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

Unchecked as much as i can, only two references could not be cleared, "Visual basic for applications" and "Microsoft Access 8.0 Object Library". I then got a different error 'user defined type not defined' with this highlighted "Dim ThisDB As DAO.Database". So i then went back into available references searched for any containing 'DAO' in the name and re-checked "Microsoft DAO 2.5/3.5 Compatibility Lib..." tried it and i got the original error 'sub or function not defined' and 'Replace' is highlighted again.
I really appreciate your time with this.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Uncheck it and tick Microsoft DAO 3.6 Object Library.

http://support.microsoft.com/kb/225962

 

[scottian]

Ok that done, but still get 'sub or function not defined' error.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Can you try the Right function somewhere? The immediate window would suit:

?Right("abc",1)

 

[scottian]

I copied and pasted your suggestion '?Right("abc",1)' but when i pressed enter the line was changed to 'Print Right("abc", 1)'

so i just tried the right function 'MsgBox Right("abc", 1)', which worked.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Does your database compile? Have you tried a compact and repair? Does Replace work in a new database?