How to add a special no when append data to a table?

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Hi,

I use a append query"add_print" to add some record to a table"print_data",
I want to know is it possible to set a field in query can add a special
format serial no to the table,
Today date + four digit serial no = 0405030001
The four digit no need auto accumulate in each record and everyday need
starting back to "0001"

For example 2004/05/03 first time run the query add three record
the result in serial field is:
0405030001
0405030002
0405030003
2004/05/03 second time run the query add four record
the result in serial field is:
0405030004
0405030005
0405030006
0405030007
2004/05/04 first time run the query add two record
the result in serial field is:
0405040001
0405040002

Thank for help!!

Gary
 
Hi,


DMax("Format( val(1+FieldNameHere), '000000000' ) ",
"myTableNameHere",
"FieldName LIKE '" & Format( Date() , "yymmdd" ) & "*'
")


would do if there is already data, for today. If there is no data for today,
DMax would return NULL, so...

Nz( DMax( ....), Format(Date( ), "yymmdd") & "0001" )


should do. ( I didn't repeat the whole DMax expression, to make the logic
more transparent).


The DMax itself would be easier (and faster, but probably not relevant), if
the data was store atomically, in two fields: myDate and sequence. You
could then concatenate those two fields, as required, when needed (for
presentation), but data retreival would be faster, if you have to make
research based on date, or month, etc.


NextSequence= Nz( 1+ DMax( "Sequence", "tablename", "myDate=Date()" ),
1 )



( with a default value = Date( ) for the field myDate)



Hoping it may help,
Vanderghast, Access MVP
 
Thank Michel,

I can add the Sequence no in table, but if I append more than one record at
the same time,
the field show the same no
0405040001
0405040001
0405040001
but I need this
0405040001
0405040002
0405040003

How to fix this problem?
Thank again!

Gary
 
Hi,


I don't see any easy way, short of ranking the records to be added and
using their rank (1, 2, 3, ... ) rather than 1 in 1 + DMax...



Hoping it may help,
Vanderghast, Access MVP
 
Thank !!

Michel Walsh said:
Hi,


I don't see any easy way, short of ranking the records to be added and
using their rank (1, 2, 3, ... ) rather than 1 in 1 + DMax...



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top