can anyone help me out?

[N2S]

Hi,

This is what i want to do:
i have a textbox in a form that i have to filed up with a daynumber for each
new record i created of that day, the next day i want to start from number
one again.
so if the date is changing i want to start a new numbering
first record has to be number 1, second record has to be number 2....and so
on and the next day i want to start again from 1

I want to generate this number after the first combobox on the sheet is
filed in.

Thanks for your help
Peter

 

[Martin Scott]

I've mocked this up using 3 queries.

1. Find the max date for your records
SELECT Max(ActionDate) AS MaxOfActionDate FROM Table1

2. Join query 1 to your table by the max date found and find the max num i
your after also show the max date from query 1.
SELECT MaxOfActionDate, Max(Ref_No) AS MaxOfRef_No FROM qryMaxDate
INNER JOIN Table1 ON MaxOfActionDate = ActionDate
GROUP BY MaxOfActionDate

3. If the date in query 2 is the same as today ( use the Date() function )
then add 1 onto i, if not then start at 1
SELECT Date() AS Expr2,
IIf([MaxOfActionDate]=Date(),[MaxOfRef_No]+1,1) AS Expr1 FROM qryMaxRef

Rgds

M.

 

[Guest]

forgot to check the notify me check box.. oops..smiles my address is
(e-mail address removed) for anyone who wants to reply or reply here.

I've mocked this up using 3 queries.

1. Find the max date for your records
SELECT Max(ActionDate) AS MaxOfActionDate FROM Table1

2. Join query 1 to your table by the max date found and find the max num i
your after also show the max date from query 1.
SELECT MaxOfActionDate, Max(Ref_No) AS MaxOfRef_No FROM qryMaxDate
INNER JOIN Table1 ON MaxOfActionDate = ActionDate
GROUP BY MaxOfActionDate

3. If the date in query 2 is the same as today ( use the Date() function )
then add 1 onto i, if not then start at 1
SELECT Date() AS Expr2,
IIf([MaxOfActionDate]=Date(),[MaxOfRef_No]+1,1) AS Expr1 FROM qryMaxRef

Rgds

M.

Hi,

This is what i want to do:
i have a textbox in a form that i have to filed up with a daynumber for each
new record i created of that day, the next day i want to start from number
one again.
so if the date is changing i want to start a new numbering
first record has to be number 1, second record has to be number 2....and so
on and the next day i want to start again from 1

I want to generate this number after the first combobox on the sheet is
filed in.

Thanks for your help
Peter

 

[Guest]

Thanks that does help me a little but i think my task is kind of impossible.
I have created a project at work a downtime procedure and have implemented
it. It requires me to use a template I made to create down time doctor cards
that is cards with the doctors name on the top and side ways on the side and
his/her patients listed well this data is hand written in the patients names
i mean. the template was created in Word and I used an Excel list I created
of the doctors and three templates a sheet. well then after implementing it
realized a duhhh fact.. that the list of doctors is ever chnaing with old
doctors going and new coming. my project is kind of written in stone right
now. I have tried to do one template per page but it doesnt work. even with
three per age that is 69 pages.
so my friend suggested using Access so made my data table of doctors in
Access. now what i asked my self. How do i make this project so i can enter
in new doctors on the templates in alpha order. I make up packets for folders
and trying to keep up todate back up packets in case down time happens which
can do at anytime. can you help me or direct me to someone or somewhere i can
get help. in other words i need to make this project flexible so i can add in
alpha order new cards in the packet with the new doctors' names on them. hope
this makes sense

I've mocked this up using 3 queries.

1. Find the max date for your records
SELECT Max(ActionDate) AS MaxOfActionDate FROM Table1

2. Join query 1 to your table by the max date found and find the max num i
your after also show the max date from query 1.
SELECT MaxOfActionDate, Max(Ref_No) AS MaxOfRef_No FROM qryMaxDate
INNER JOIN Table1 ON MaxOfActionDate = ActionDate
GROUP BY MaxOfActionDate

3. If the date in query 2 is the same as today ( use the Date() function )
then add 1 onto i, if not then start at 1
SELECT Date() AS Expr2,
IIf([MaxOfActionDate]=Date(),[MaxOfRef_No]+1,1) AS Expr1 FROM qryMaxRef

Rgds

M.

Hi,

This is what i want to do:
i have a textbox in a form that i have to filed up with a daynumber for each
new record i created of that day, the next day i want to start from number
one again.
so if the date is changing i want to start a new numbering
first record has to be number 1, second record has to be number 2....and so
on and the next day i want to start again from 1

I want to generate this number after the first combobox on the sheet is
filed in.

Thanks for your help
Peter