In your query, replace this field:
tblHDSCHDEPOMEDNOTE.DateOfHD
with this somewhat complicated expression (not tested):
IIf(Nz(DMax("DateOfHD", "tblHDSCHDEPOMEDNOTE", "tblHDSCHDEPOMEDNOTE.ID = " &
tblHDSCHDEPOMEDNOTE.ID), 0) = 0, #mm/dd/yy#, IIf(DatePart(DMax("DateOfHD",
"tblHDSCHDEPOMEDNOTE", "tblHDSCHDEPOMEDNOTE.ID = " &
tblHDSCHDEPOMEDNOTE.ID), 1) Between 2 And 5, DMax("DateOfHD",
"tblHDSCHDEPOMEDNOTE", "tblHDSCHDEPOMEDNOTE.ID = " & tblHDSCHDEPOMEDNOTE.ID)
+ 2, DMax("DateOfHD", "tblHDSCHDEPOMEDNOTE", "tblHDSCHDEPOMEDNOTE.ID = " &
tblHDSCHDEPOMEDNOTE.ID) + 3))
The logic of the above expression is to find the maximum value of
tblHDSCHDEPOMEDNOTE.DateOfHD for a value of tblHDSCHDEPOMEDNOTE.ID, identify
if the date is a Monday, Tuesday, Wednesday, or Thursday date, and if it is,
add 2 days to the date, else add 3 days to the date (date is a Friday or
Saturday date -- assuming that you cannot put a Sunday date into the
tblHDSCHDEPOMEDNOTE.DateOfHD field by your form/query/logic/design). You'll
note that you can ignore whether the MWF or TTS field is checked, assuming
that one or the other must be checked.
The above assumes that, if this is the first record to be inserted for a
tblHDSCHDEPOMEDNOTE.ID value, you want tblHDSCHDEPOMEDNOTE.DateOfHD to be
some hard-coded date (which I show generically as #mm/dd/yy# in the above
expression). The above also assumes that the tblHDSCHDEPOMEDNOTE.ID field is
a numeric (e.g., Long Integer) data type, not a string/text datatype.
Note that your query will need to be changed to use VALUES syntax (not
SELECT syntax) for an append query so that you insert just a single record
each time. You'll need to hardcode or otherwise provide the values for the
query so that it will run. Are you running this query in VBA code (that is,
are you able to build the SQL statement string in code, such that you can
hardcode values into the string)?
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Leo said:
Ken Snell said:
If you add multiple records in the append query, the query cannot see
other
records that are being added by that query when it runs. The only way to
see
"the previously added" record is to add one record at a time, and then
the
query can "see" what is in the table when it next runs.
Can you post the SQL statement of the append query that you're using
right
now? It'll probably be easier to amend your current query than to build a
new one for you here.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
:
Are you adding one record at a time in the append query? If yes, then
a
proper expression can be used in the Append query as part of a
calculated
field.
But it'll be easier to help you if you tell us what the primary key
field(s)
is/are for the table, and which fields (and their sources) are being
inserted. I assume that you run this append query from a form's code?
Or
is
the "MWF" field in the table data that are being used as the source
for
the
append query?
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
I have an append query. When it is RUN it adds various fields of the
record.
One such field is DateOfQD. When the next record is added I need to
change
the value as follows;
If the field 'MWF" is selected (i.e., -1 since it is a check
box)
and
If the previous record's DateofQd is Monday then the next
record's
DateofQD field is Wednessday and if the previous record's field is
Wednessday
the next one is Friday and finally if the previous record's field is
Friday
the next record's field is Monday.
In the criteria field how do I write the formula?
While I am running this query, if I need to have start date
and
end
date from another table (Name: tblSCHEDULE) that will add set number
of
records throug this append query where and how should I enter the
formula?
Can any one help?
Thanks
Leo
No. I like to add a number of records at a time based on start and end
dates. But if that is not possible I guess I need to add one record at
a
time. This query has only one table as its source. The name of the
table
is tblHDSCHDEPOMEDNOTE. The primary key is [HDSCHDEPOMEDNOTE id]
and
it is an autonumber field. The table also has another field called
[Account_Number] which serves as a link to another table called Schd.
The
primary key for the table Schd is Schdid. All fields on this append
query are in the same table, including [MWF] and [TTS] and both are
yes/no check boxes. Depending on what selection user makes I want to
append the records with all the same data except the date/field called
[DateOfHd]. The date field should update either Monday wednessday
Friday
or Tues Thur Saturday.
Greatly appreciate the interest and help
Thanks
Leo
Here is the copy from the SQL view of the query. In the design view there
are not expressions or criteria added so far.
INSERT INTO tblHDSCHDEPOMEDNOTE ( Account_Number, DateOfHD, TS, TE,
NextAvilable, Epo, Med, [Note], Station, Shift, MWF, TTS, TimesPerWeek )
SELECT tblHDSCHDEPOMEDNOTE.Account_Number, tblHDSCHDEPOMEDNOTE.DateOfHD,
tblHDSCHDEPOMEDNOTE.TS, tblHDSCHDEPOMEDNOTE.TE,
tblHDSCHDEPOMEDNOTE.NextAvilable, tblHDSCHDEPOMEDNOTE.Epo,
tblHDSCHDEPOMEDNOTE.Med, tblHDSCHDEPOMEDNOTE.Note,
tblHDSCHDEPOMEDNOTE.Station, tblHDSCHDEPOMEDNOTE.Shift,
tblHDSCHDEPOMEDNOTE.MWF, tblHDSCHDEPOMEDNOTE.TTS,
tblHDSCHDEPOMEDNOTE.TimesPerWeek
FROM tblHDSCHDEPOMEDNOTE;
Thanks
I will certainly give it a try and let you know. And once again I greatly appreciate your help.
