I'm not sure why you are doing this through a query instead of using an
input form. That said I would use the alternate single-record append
query,
since you seem to be supplying every value through parameters except for
the
new document number.
Assuming that Department code and Document type code are text fields then
Dmax would be something like the following
DMax("[Document Number]","[Master Document List]", "Department Code] ="""
&
[Enter Department Code:] & """ AND [Document Type Code]=""" & [Enter
Document Type Code:] & """")
Wrap that in the NZ function and add 1 to get the next value.
NZ(DMax("[Document Number]","[Master Document List]", "Department Code]
="""
& [Enter Department Code:] & """ AND [Document Type Code]=""" & [Enter
Document Type Code:] & """") ,0) +1
INSERT INTO [Master Document List]
( [Department Code]
, [Document Type Code]
, [Document Number]
, [Document Name] )
VALUES
( [Enter Department Code:]
, [Enter Document Type Code:]
, NZ(DMax("[Document Number]","[Master Document List]",
"Department Code] =""" & [Enter Department Code:] &
""" AND [Document Type Code]=""" & [Enter Document Type Code:] &
"""") ,0) +1
, [Enter New Document Name])
I've broken the DMax statement into multiple lines to make this easier to
read. I should all be on one line
Thank you, John, for the suggestion. Here is the code from my query:
INSERT INTO [Master Document List] ( [Department Code], [Document Type
Code], [Document Number], [Document Name] )
SELECT DISTINCTROW [Master Document List].[Department Code], [Master
Document List].[Document Type Code], Max([Master Document
List]![Document
Number])+1 AS [New Document Number], [Enter New Document Name] AS
[Document
Name]
FROM [Master Document List]
GROUP BY [Master Document List].[Department Code], [Master Document
List].[Document Type Code]
HAVING ((([Master Document List].[Department Code])=[Enter Department
Code:]) AND (([Master Document List].[Document Type Code])=[Enter
Document
Type Code:]));
Hope this helps. And I do really appreciate all the help I have
received
so
far. Thank you very much!
-Nicole
:
I suggest you post the entire query and not just the calculated
column.
Open the query in design mode
Switch to SQL view (View menu)
Copy the text
Paste it into your message.
That will give anyone trying to help you with this a better idea of
what
needs
to be done to make this work for you.
Nicole wrote:
Thank you, John. Unfortunately it didn't work. I tried using your
suggestion and it resulted in the message that it was going to
append
zero
rows. I tried a few variations of your suggestion as well and I got
some of
the same error messages that I detailed below.
Surely, what I want Access to do for me is possible. I'll keep
trying
and
keep accepting any other suggestions.
Thanks again,
Nicole
:
You might try using the DMax function instead in conjunction with
an
NZ
function.
NewDocumentNumber: 1+Nz(DMax("[Document Number]","[Master
Document
List]"),0)
Here are some of the various null statements that I have
attempted
to use
and
the result:
1. New Document Number: IIf(IsNull([Master Document
List]![Document
Number]),1,Max([Master Document List]![Document Number])+1)
Result: Error: "You tried to execute a query that does not
include
the
specified expression [see above expression] as part of an
aggregate
function.
2. New Document Number: 1+Nz(Max([Master Document
List]![Document
Number]))
Result: "You are about to append 0 row(s)."
3. New Document Number: Nz(Max([Master Document List]![Document
Number])+1)
Result: "You are about to append 0 row(s)."
4. New Document Number: IIf(IsNullMax([Master Document
List]![Document
Number])),1,+1)
Result: "The expression you entered has a function containing
the
wrong
number of arguments."
What I need this thing to do is to find the max value for the
document
number and add one to insert a new document into the master list
(a
sequentially numbered list). It works as long as there are
previous
values
(previous documents for the specified conditions). However, if
there is
no
previous document and the number is null, then it doesn't work.
In
this
instance, I need the query to assign a value of 1 to the new
document.
Any help would be appreciated!
Thanks,
Nicole
:
Thanks for your help, Steve. Unfortunately, I have tried
several
variations
of these functions with no luck. It is likely that my
statements
are
incorrect, which is why I am not getting the results I want. I
am
going
to
try again this afternoon and tomorrow morning, and if I fail, I
will post
my
failed statements here in the hopes that you or someone else
might
be
able to
help me correct them.
Thanks again for all your help!
-Nicole
:
Try IIF() and Nz()
e.g.
IIF(Isnull(fieldname), dothis, dothat)
Nz(fieldname, 0)
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
I have a database to manage and track document control
numbers
and
maintain
a
master document list. I have created an append query for
adding new
documents to the master list. The query will assign a new
number to
the
document and create a new record for the document in the
master list.
The
document number consists of three components, two alpha and
one
numeric -
some of which repeat, therefore each component is a
separate
column
in the
table and all three together make up the primary key for
the
table
[example:
XX-XX-0001].
The query simply finds the max value for the numeric
portion
of the
number,
based on input for the other two components and adds one to
create a
new
number. However, if there is no previous entry for the
alpha
components
of
the number, then there is no max value and the query will
not
successfully
create a new record (there are no error messages generated,
it
simply
gives
the message that it will be updating "0" reocrds in the
table.)
Here is the code I am currently using to update numbers:
New Document Number: Max([Master Document List]![Document
Number])+1
I've tried playing around with the Null functions with no
success. I
am a
novice, so any suggestions should be as simplistic or
well-explained
as
possible. Thank you very much for any help with this.
-Nicole
