Use Function in Query problem

[Co]

Hi All,

I'm trying to run an access query using a function I created to tell
the query what path to use.

Public Function QueryVar(ByVal sPath as String) As String
QueryVal = sPath
End Function

QueryVar("H:\Local\Archive.mdb")

Now I run a query saying:
INSERT INTO ArchiveTable ........ IN QueryVar SELECT ........ FROM
ArchiveTmp;

I'm getting an error saying he can't find the path.
What am I doing wrong here?

Marco

 

[Stefan Hoffmann]

hi Marco,

Now I run a query saying:
INSERT INTO ArchiveTable ........ IN QueryVar SELECT ........ FROM
ArchiveTmp;
I'm getting an error saying he can't find the path.
What am I doing wrong here?

You cannot use a expression after IN, there must be a constant.

I assume you have a saved query, let's call it qryArchive. Then you can
do this:

Dim db As DAO.Database
Dim SQL As String

SQL = "INSERT INTO ArchiveTable ........ " & _
"IN " & QueryVarOrPathVariable & " " & _
"SELECT ........ FROM ArchiveTmp;"

Set db = CurrentDb
db.QueryDefs.Item("qryArchive").SQL = SQL
Set db = Nothing


mfG
--> stefan <--

 

[Dale Fye]

Stefan,

1. I'm assuming there is a typo in the QueryVar function, because the
return value is QueryVal, not QueryVar.

2. Not sure why you even need the function if all you are doing is passing
it a path and returning the same value.

3. The IN clause requires that the file name be wrapped in single or double
quotes. So it should read something like:

Dim SQL as string

SQL = "INSERT INTO ArchiveTable (Field1, .....) " _
& "IN '" & QueryVar([FieldName]) & "' " _
& "SELECT ..... FROM ArchiveTmp"

currentdb.execute SQL


HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Gary,

I've recently started using a single function. Cannot remember where I saw
this technique, but I like it. I put this in a code module, so that it can
be accessed from almost anywhere.

Public Function fnPath(SomeValue as Variant = NULL) as String

Static myPath as Variant

if not isnull(SomeValue) then
myPath = SomeValue
elseif isempty(myPath) then
myPath = "C:\"
endif
fnPath = myPath

End Sub

I will generally add some error checking to make sure that the passed value
meets some criteria (isnumeric, isdate, is a valid path).

Dale

 

[Gary Walter]

Hi Dale,

I wondered why no one said anything about
the construct...

I don't know...my one coding prime directive
is, "Can I immediately identify what I was doing
when I have to revisit the code some 6 months
later?"

The GET and SET functions document themselves
well for me, but that doesn't mean a single function
isn't equally valid...I suppose it means you won't have
any global vars (in a strict sense).

I will just have to remember to not reply to any post
using that method from now on I guess.

gary

 

[Dale Fye]

Gary,

The reason I like it is that I don't need any "global variables", and can
use this function in queries, which you cannot do with global variables.

For example, if I have a query that I want to be able to run from several
forms, each of which has an EmployeeID field, I could write two queries, one
refering to each of the forms txt_EmpID control, or I could set fnEmpID in
the Current event of each form, and refer to fnEmpID in my query, so I can
reuse the query.

I save all functions in mod_GlobalVariables and usually add a little
documentation to each function. Once you get used to this technique, it is
great.

Dale