Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Unanswered: Pass a variable to a query

    Hi Everyone,

    Here is the situation; I have approx 16 text files linked in an mdb. These files are delivered daily with the only differences being the file date embedded in the name (GSK_shipment20080530.txt). By 'file date' I don't mean the date the file was created but the date the file represents.

    I have code that reads the file names and determines the date in the name. In the example above it is 05/30/2008. The code then copies the table to another directory as GSK_shipment.txt. This is the file name the mdb is linked to so with each new file the mdb is always linked to the latest data.

    I have queries that append the data from the linked table to an Access table in another mdb. I pass with it the file_dtg (embedded file date) and load_dtg (now()). I'm currently using a function that is assigned the value of the embedded date once I capture it, but I don't think putting a function in an append query is the best way since there will be 1000s of calls to it for each query.

    Any thoughts would be appreciated.

    Thanx, Stu
    --If its free, take it for what its worth!

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    How are you running the queries, where from?

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What I have done in the past is to create a Parameter table. Basically you have a table that has one row and one field (more fields if you need). Then you have code to update the table with the date value. Then your append query the parameter table as part of the query.

    The only other way I know of is to create a parameter query. Edit a query, then right click in the Design view and choose Parameters. Add a parameter and then in code you set the parameter values. I don't have a sample of the code, but the steps are, create a Query object, set the proper parameter(s) using the Parameters collection then execute the query.

    I like the table version because it seems more straight forward (easier for me) and the added benefit is being able to set the value manually and running the query manually, if needed.

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Quote Originally Posted by rogue
    How are you running the queries, where from?
    The queries are being run via vba. The function is being called within the query itself, looks something like 'Expr1 = fncCalled()'. The function simply provides the value of the global variable. I'm the table idea might be OK, I'm going to try that next.

    Thanx for the replies!
    --If its free, take it for what its worth!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •