Still need help incrementing a number after resetting it daily to 1

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Rick Brandt was kind enough to give me the following code. I was unable to
follow up at the time and I did not repost there as I think the thread is
dead now.

I have tried many variations on the code and cannot get it to work. I am
trying to reset the delivery number to 1 each morning and then to increment
as each order is added.

This is the code he provided and below is my last attempt to get it to work
and a definition of the field names that I am using.

When I try to add the code, I get the following error.

"The expression you entered contains invalid syntax. You omitted an operand
or operator, you entered an invalid character or comma, or you entered text
without surrounding it in quotation marks."


=Nz(DMax("DailyDeliveryID","tblDailyDelivery","Day(CreatedDate) =
Day(Date)"), 0) + 1

I have altered it to the following and not sure if I have done it correctly


=Nz(DMax("NPedana","Registro Entrata", "Day(Del) = Day(Date)"), 0) + 1


NPedana is the deliveryID
Registro Entrata is the name of the table where the ID is stored
Del is the date of the deliver that is in the table (created by using a
default of Date() in the properties box.(I was not sure if CreatedDate was a
function.

thank you
Michael
 
Michael said:
Rick Brandt was kind enough to give me the following code. I was
unable to follow up at the time and I did not repost there as I think
the thread is dead now.

I have tried many variations on the code and cannot get it to work. I
am trying to reset the delivery number to 1 each morning and then to
increment as each order is added.

This is the code he provided and below is my last attempt to get it
to work and a definition of the field names that I am using.

When I try to add the code, I get the following error.

"The expression you entered contains invalid syntax. You omitted an
operand or operator, you entered an invalid character or comma, or
you entered text without surrounding it in quotation marks."


=Nz(DMax("DailyDeliveryID","tblDailyDelivery","Day(CreatedDate) =
Day(Date)"), 0) + 1

I have altered it to the following and not sure if I have done it
correctly

=Nz(DMax("NPedana","Registro Entrata", "Day(Del) = Day(Date)"), 0) +
1

NPedana is the deliveryID
Registro Entrata is the name of the table where the ID is stored
Del is the date of the deliver that is in the table (created by using
a default of Date() in the properties box.(I was not sure if
CreatedDate was a function.

thank you
Michael
Click to expand...

And where are you using the code? My suggestion was to use it in the
BeforeUpdate event of the form and to test the NPedana for Null as a condition
of doing the assignment.

If that _is_ actually code then something has to be on the left side of the = as
well. If you are just trying to use it as an expression in a TextBox that will
not work.
 
Michael said:
Sorry at one point there was a suggestion to use is as the default in
the text box. I have added in the before update and I am still
confused. You said that I must add something before the "=" I am not
sure what to add.
Click to expand...

If this is a single record view form (non-continuous) and you will only have
one person entering new records at a time then using that expression as the
default value for NPedana should work. The problem is that when the Date()
function is used in VBA code the opening and closing parenthesis are not
required (Access will actually remove them if you enter them), but when used
in a form expression those parenthesis are required. So your expression
would be...

=Nz(DMax("NPedana", "Registro Entrata", "Day(Del)=Day(Date())"), 0) + 1
If I add NPedana, I am afraid it will create a unending circle. I
tried it and nothing.

NPedana = Nz(DMax("NPedana", "Registro Entrata",
"Day(Del)=Day(Date)"), 0) + 1
Click to expand...

Not sure what you mean by an "unending circle". Since BeforeUpdate can fire
multiple times for a given record you use an If-Then block to make sure that
the value is only applied once.

If Nz(NPedana, 0) = 0 Then
NPedana = Nz(DMax("NPedana", "Registro Entrata", "Day(Del)=Day(Date)"),
0) + 1
End If
 
Michael said:
Thanks Rick,
The form is a single view and only one computer will access this
form, but when I add the code to the default in the properties, I
get the same error. I tried to add the other code to the before
update, and I only get a 0 in the NPedana field. I tried to change
the date on my computer.. but still get a "0"
Click to expand...

In these cases you need to "divide and conquer". Open the immediate window
(Control-G) and type...

?Nz(DMax("NPedana", "Registro Entrata", "Day(Del)=Day(Date())"), 0) + 1
<Enter>

....and see if you get an error or the correct response. If you don't get
the correct response strip something out of the expression to simplify it
and try again. For example, I would first eliminate the WHERE clause from
the DMax() to see if that is the problem...

?Nz(DMax("NPedana", "Registro Entrata"), 0) + 1 <Enter>

Does this work? If so, then you know that the WHERE clause is the problem.
Actually in looking at this I see a different problem anyway. You should
not be using Day() because that just returns the day of the month which will
not work. You need to compare the actual dates. What should work is...

?Nz(DMax("NPedana", "Registro Entrata", "DateValue(Del)=Date()"), 0) + 1
<Enter>

If the field [Del] has no time component stored then the DateValue()
function would not be required and you could just use...

?Nz(DMax("NPedana", "Registro Entrata", "Del = Date()"), 0) + 1 <Enter>

Be careful not to be confused by formatting here. How you have Del
formatted has no bearing on whether a time component is stored or not. It
has to do with whether it is populated by using Date() or Now(). If there
is no time vaue stored (actually a time of midnight) then the expression
without the DateValue() funstion will be more efficient. You should also
apply an index to this field.

Try the above and see if you have any success.
 
Sorry at one point there was a suggestion to use is as the default in the
text box. I have added in the before update and I am still confused. You
said that I must add something before the "=" I am not sure what to add.

If I add NPedana, I am afraid it will create a unending circle. I tried it
and nothing.

NPedana = Nz(DMax("NPedana", "Registro Entrata", "Day(Del)=Day(Date)"), 0) +
1

sorry if I am slow..
thank you for you patients.
michael
 
Thanks Rick,
The form is a single view and only one computer will access this form, but
when I add the code to the default in the properties, I get the same error.
I tried to add the other code to the before update, and I only get a 0 in
the NPedana field. I tried to change the date on my computer.. but still get
a "0"

thanks for you help
Michael
 
Back
Top