make number generator in access

[van93]

i need to make a generator for id in my database, this id need real time date, brand, no entry. for example,
for first item with brand Dex
real time date = 7813
brand = dex
no entry = 1
for second item with brand Dex
real time date = 7813
brand = dex
no entry = 2

if there's change in date or brand, no entry will start again in 1.

please for help. I really need yours help. Thank you before.

[jimatqsi]

Van93, you're going to want to query the table containing this data for Max([no entry]) with where string criteria for the date and brand. That will return the highest value in the table for [no entry]. Add one to that to get your new entry number.

If there is no row matching the where string you'll have a null value for [no entry], so you'll need NZ([no entry],0) to convert the NULL to a zero and avoid an error.

You don't say if this is in a query or code behind a form. Knowing that would inform the discussion. I hope this gets you over the hump.

Also, you might pay attention to how you form the date value. 12113 could be Jan 21 or Dec 1.

Jim

[zmbd]

(Sorry, this may cross post, had to step away.)


Lets work with your fields for the momemnt.
Let me rename them for just a little bit.
[realtimedate]; [brand]; [SeqNum]

What you describe will do something like this
(take special note of lines 7,8, and 9:

[realtimedate]; [brand]; [SeqNum]
     7813        Dex     0001
     7813        Dex     0002
     7813        Dex     0003
     7813        New     0001
     7813        New     0002
     7813        New     0003
     7813        Dex     0001
     7813        Dex     0002

I suspect you want something like the following for a given [realtimedate]:

[realtimedate]; [brand]; [SeqNum]
     7813        Dex     0001
     7813        Dex     0002
     7813        Dex     0003
     7813        New     0001
     7813        New     0002
     7813        New     0003
     7813        Dex     0004
     7813        Dex     0005

(Once Again: take special note of lines 7,8, and 9)


Reseting the [SeqNum] when the date changes so from 7813 to 7814 for a given brand

is this more what you are after?

[van93]

Thank you guys for the answer. for ZMBD, i've tried to make the second one but actually i need to make the first one... can you help me for the coding... i can't do that... thank you...