incident #

[netuse@debouck.]

i need to make an incident # to be stored in a table in the format ddmmyy and then starting at 1 for the first incident on the day auto increment and reset back to one on a new day. any help would be appreciated

 

[WayneRyan]

net,

Break it into two fields:

IncidentDate - Date/Time
IncidentNumber - Number

Then use the form's BeforeInsert event to:

IncidentNumber = Nz(Dmax("[IncidentNumber]", "YourTable", "[IncidentDate] = #" & Date & "#"), 0) + 1

Essentially that says the IncidentNumber is the maximum number found for
that day (0 if none), then add 1 to it.

Wayne

 

[netuse@debouck.]

Is There A Way To Combine It Into One # Like 07080401,07080402 Etc

 

[WayneRyan]

net,

You can, but you want to store them seperately. There will be times when
the date component is needed, you will appreciate having a nice reliable
date field handy without having to dig it out of some string.

Producing your number 07080401:

Format([IncidentDate], "mmddyy") & IncidentNumber

Very easy. In fact, by seperating them, it makes it very to change it up
for whatever reason:

Format([IncidentDate], "mmmyyyy") & IncidentNumber

Because your date part is really a date field you can manipulate it quite
easily.

Wayne

 

[netuse@debouck.]

ok got it working except for the format where do i put the string
WHAT I REALLY WANT TO DO IS TAKE THE IncidentDate format ddmmyy and the IncidentNumber combine them put the in the field Incident for example and use that number for the primary key.

and i wanna say thanks a lot for your help

 

[WayneRyan]

net,

I don't reccommend it, but this does it.

You can get the IncidentNumber, by breaking apart your existing Primary
Keys and finding the Max+1 for the date.

Incident = Format(Date, "ddmmyy") & Me.IncidentNumber

Wayne

 

[netuse@debouck.]

where do i put the code ?

i got it never mind

thanks for your help

 

[netuse@debouck.]

Wayne,

Everything Works Fine Except Its Dropping The First 0 In 0808041 So It Reads 808041

 

[WayneRyan]

net,

You appear to be storing the primary key a number field. You can display
the number with the format function (to restore the leading zero(s)). I
thought you were making a string primary key?

Two values in one numeric field opens up a whole new world of problems.
What happens when you get 08080410? That is #10 for August 8. Ten
items on a day will cause great problems.

It really is easier; DateField, ItemNumber ...

Wayne