Passing parameter to Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a procedure that uses TransferText to import file(s). After they have
been imported I need to update those records. I have the update query below.
In my procedure I have:
With qdf
.Parameters("ImportFile") = strFile
.Execute
End With

Query:
PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM SET tblPRM.UnitNumber = Trim([UnitNumber]), tblPRM.ImportFlag
= False
WHERE (((tblPRM.ImportFlag)=True));

The ImportFile is not updated. I'm sure I'm just missing a small piece of
this puzzle.

Thanks
LeAnn
 
You are not using the parameter in the actual update SQL statement. What is
the purpose of the parameter?
 
Not criteria row, I mean in the UpdateTo row.

Ken Snell (MVP) said:
You are not using the parameter in the actual update SQL statement. What is
the purpose of the parameter?

--

Ken Snell
<MS ACCESS MVP>


LeAnn said:
I have a procedure that uses TransferText to import file(s). After they
have
been imported I need to update those records. I have the update query
below.
In my procedure I have:
With qdf
.Parameters("ImportFile") = strFile
.Execute
End With

Query:
PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag
= False
WHERE (((tblPRM.ImportFlag)=True));

The ImportFile is not updated. I'm sure I'm just missing a small piece of
this puzzle.

Thanks
LeAnn
Click to expand...
Click to expand...
 
To update every record that was imported (ImportFlag = True) with the name of
the file being imported. I didn't know how to refer to the parameter in the
criteria row.

Ken Snell (MVP) said:
You are not using the parameter in the actual update SQL statement. What is
the purpose of the parameter?

--

Ken Snell
<MS ACCESS MVP>


LeAnn said:
I have a procedure that uses TransferText to import file(s). After they
have
been imported I need to update those records. I have the update query
below.
In my procedure I have:
With qdf
.Parameters("ImportFile") = strFile
.Execute
End With

Query:
PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag
= False
WHERE (((tblPRM.ImportFlag)=True));

The ImportFile is not updated. I'm sure I'm just missing a small piece of
this puzzle.

Thanks
LeAnn
Click to expand...
Click to expand...
 
What I mean is that you have a parameter named ImportFile in your SQL
statement's PARAMETERS clause. But that parameter is not used in the rest of
the SQL statement, so giving it a value does nothing for the SQL update.

Essentially, the PARAMETERS clause is unnecessary, and your actual,
operative SQL statement is this:

UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False
WHERE (((tblPRM.ImportFlag)=True));

If you want to add the ImportFile parameter's value to your imported
records, you need to include a clause in the SQL that will do that. You
don't say which field is to receive the file name string, so here is a
generic rewrite of your SQL statement:

PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False,
tblPRM.NameOfTheFieldThatHoldsFileName = ImportFile
WHERE (((tblPRM.ImportFlag)=True));

--

Ken Snell
<MS ACCESS MVP>




LeAnn said:
To update every record that was imported (ImportFlag = True) with the name
of
the file being imported. I didn't know how to refer to the parameter in
the
criteria row.

Ken Snell (MVP) said:
You are not using the parameter in the actual update SQL statement. What
is
the purpose of the parameter?

--

Ken Snell
<MS ACCESS MVP>


LeAnn said:
I have a procedure that uses TransferText to import file(s). After they
have
been imported I need to update those records. I have the update query
below.
In my procedure I have:
With qdf
.Parameters("ImportFile") = strFile
.Execute
End With

Query:
PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag
= False
WHERE (((tblPRM.ImportFlag)=True));

The ImportFile is not updated. I'm sure I'm just missing a small piece
of
this puzzle.

Thanks
LeAnn
Click to expand...
Click to expand...
Click to expand...
 
Hi Ken. I had tried an update statement but I'm getting a mismatch error.
That's why I thought I might try using an update query to accept the file
name variable. First time to try to use query parameters programatically.
OLH wasn't very helpful. It said to name the parameter the name of the field
so the field I want to update is ImportFile. I updated my query as in your
second solution and it works perfectly!

Thanks a lot Ken!


Ken Snell (MVP) said:
What I mean is that you have a parameter named ImportFile in your SQL
statement's PARAMETERS clause. But that parameter is not used in the rest of
the SQL statement, so giving it a value does nothing for the SQL update.

Essentially, the PARAMETERS clause is unnecessary, and your actual,
operative SQL statement is this:

UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False
WHERE (((tblPRM.ImportFlag)=True));

If you want to add the ImportFile parameter's value to your imported
records, you need to include a clause in the SQL that will do that. You
don't say which field is to receive the file name string, so here is a
generic rewrite of your SQL statement:

PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False,
tblPRM.NameOfTheFieldThatHoldsFileName = ImportFile
WHERE (((tblPRM.ImportFlag)=True));

--

Ken Snell
<MS ACCESS MVP>




LeAnn said:
To update every record that was imported (ImportFlag = True) with the name
of
the file being imported. I didn't know how to refer to the parameter in
the
criteria row.

Ken Snell (MVP) said:
You are not using the parameter in the actual update SQL statement. What
is
the purpose of the parameter?

--

Ken Snell
<MS ACCESS MVP>


I have a procedure that uses TransferText to import file(s). After they
have
been imported I need to update those records. I have the update query
below.
In my procedure I have:
With qdf
.Parameters("ImportFile") = strFile
.Execute
End With

Query:
PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag
= False
WHERE (((tblPRM.ImportFlag)=True));

The ImportFile is not updated. I'm sure I'm just missing a small piece
of
this puzzle.

Thanks
LeAnn
Click to expand...
Click to expand...
Click to expand...
 
If ImportFile is the name of the actual field, don't use it as the name of
the parameter too. You may confuse Jet as to which you mean -- field or
parameter -- in your query.

Change the SQL statement to this:

PARAMETERS ImportFileName Text ( 255 );
UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False,
tblPRM.ImportFile = ImportFileName
WHERE (((tblPRM.ImportFlag)=True));


Then change your code to use the ImportFileName as the name of the
parameter.
--

Ken Snell
<MS ACCESS MVP>



LeAnn said:
Hi Ken. I had tried an update statement but I'm getting a mismatch error.
That's why I thought I might try using an update query to accept the file
name variable. First time to try to use query parameters programatically.
OLH wasn't very helpful. It said to name the parameter the name of the
field
so the field I want to update is ImportFile. I updated my query as in
your
second solution and it works perfectly!

Thanks a lot Ken!


Ken Snell (MVP) said:
What I mean is that you have a parameter named ImportFile in your SQL
statement's PARAMETERS clause. But that parameter is not used in the rest
of
the SQL statement, so giving it a value does nothing for the SQL update.

Essentially, the PARAMETERS clause is unnecessary, and your actual,
operative SQL statement is this:

UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False
WHERE (((tblPRM.ImportFlag)=True));

If you want to add the ImportFile parameter's value to your imported
records, you need to include a clause in the SQL that will do that. You
don't say which field is to receive the file name string, so here is a
generic rewrite of your SQL statement:

PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM
SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag = False,
tblPRM.NameOfTheFieldThatHoldsFileName = ImportFile
WHERE (((tblPRM.ImportFlag)=True));

--

Ken Snell
<MS ACCESS MVP>




LeAnn said:
To update every record that was imported (ImportFlag = True) with the
name
of
the file being imported. I didn't know how to refer to the parameter
in
the
criteria row.

:

You are not using the parameter in the actual update SQL statement.
What
is
the purpose of the parameter?

--

Ken Snell
<MS ACCESS MVP>


I have a procedure that uses TransferText to import file(s). After
they
have
been imported I need to update those records. I have the update
query
below.
In my procedure I have:
With qdf
.Parameters("ImportFile") = strFile
.Execute
End With

Query:
PARAMETERS ImportFile Text ( 255 );
UPDATE tblPRM SET tblPRM.UnitNumber = Trim([UnitNumber]),
tblPRM.ImportFlag
= False
WHERE (((tblPRM.ImportFlag)=True));

The ImportFile is not updated. I'm sure I'm just missing a small
piece
of
this puzzle.

Thanks
LeAnn
Click to expand...
Click to expand...
Click to expand...
 
Back
Top