Date Date Date

  • Thread starter Thread starter SeRene
  • Start date Start date
S

SeRene

Hi,

I was advised to make use of an update query to update one
of my fields, "Report Date" field.
However, this query w/o any criteria will update ALL the
records with the SAME date. I do not want the same date
for all records in the table. How can i set the criteria
in the update query so that previous records with the
correct dates which i want will not be affected by the
date entered for the next few records.

Thanks!
 
You need to somehow be able to identify which records you want updated so
that your update query can select them via the WHERE clause. So, how do you
know which records have the right date and which ones don't?
 
You need to somehow be able to identify which records you want updated so
that your update query can select them via the WHERE clause. So, how do you
know which records have the right date and which ones don't?
Click to expand...

Hi Ken,
I am not able to identify which records i want to be
updated because i actually get those records from a text
file which i need to import daily. The data in the text
file changes daily so perhaps today i may have 3 records,
tomorrow i may have 5 records. So for each day, i have to
allow the user to enter a report date via input box and
that date should be "attached" to the 3 records. For the
next 5 records, the process repeats. --> Enter report date
and date "attached" to the 5 records.
 
How are you putting the text file's records into your table? If you're using
an Append Query, then you can ask for the date via a parameter (such as
[Enter Records' Date:] )and have it be added to each record being appended.

If you're using TransferText, then put the text file's records into an
empty, temporary table, and then run an append query similar to this:

INSERT INTO PermanentTableName
( FieldName1, FieldName2, FieldName3 )
SELECT *, [Enter Records' Date:] AS RecordDate
FROM TemporaryTableName;

Then you can run a delete query to clean out the temp table for the next
file.
 
How are you putting the text file's records into your
table? If you're using
an Append Query, then you can ask for the date via a parameter (such as
[Enter Records' Date:] )and have it be added to each record being appended.

If you're using TransferText, then put the text file's records into an
empty, temporary table, and then run an append query similar to this:

INSERT INTO PermanentTableName
( FieldName1, FieldName2, FieldName3 )
SELECT *, [Enter Records' Date:] AS RecordDate
FROM TemporaryTableName;

Then you can run a delete query to clean out the temp table for the next
file.
Click to expand...
Hi Ken,

I am making use of TransferText. I've tried the SQL text
you gave but it seems that there isn't any date shown
after entering a date in the prompt box.
Any idea where i went wrong??

This is my SQL txt
INSERT INTO PermanentTableName
( FieldName1, FieldName2, FieldName3 )
SELECT TempTableName.Field1, TempTableName.Field2,
Click to expand...
TempTableName.Field3, [Enter Report Date:] AS reportdate
(this field is one of the fields in the PermanentTable)
 
You're trying to append four fields per record, but the SQL statement is
only accepting three. You have three fields inside the ( ) [ note: I
showed three fields as examples just so you'd see the syntax -- you can use
as many as you need, and replace the generic names with the actual names in
the target table ], but have four fields in the SELECT clause. Thus, the
date parameter has no place to go!

Change the fields as noted above and see if that works for you.

--
Ken Snell
<MS ACCESS MVP>

SeRene said:
How are you putting the text file's records into your table? If you're using
an Append Query, then you can ask for the date via a parameter (such as
[Enter Records' Date:] )and have it be added to each record being appended.

If you're using TransferText, then put the text file's records into an
empty, temporary table, and then run an append query similar to this:

INSERT INTO PermanentTableName
( FieldName1, FieldName2, FieldName3 )
SELECT *, [Enter Records' Date:] AS RecordDate
FROM TemporaryTableName;

Then you can run a delete query to clean out the temp table for the next
file.
Click to expand...
Hi Ken,

I am making use of TransferText. I've tried the SQL text
you gave but it seems that there isn't any date shown
after entering a date in the prompt box.
Any idea where i went wrong??

This is my SQL txt
INSERT INTO PermanentTableName
( FieldName1, FieldName2, FieldName3 )
SELECT TempTableName.Field1, TempTableName.Field2,
Click to expand...
TempTableName.Field3, [Enter Report Date:] AS reportdate
(this field is one of the fields in the PermanentTable)
FROM TemporaryTableName;
Click to expand...
Click to expand...
 
Back
Top