Insert a number in a query (Access or VBA)

[nilses]

Hello,

I have a problem in my query and I do not see how me to leave there. I have a query with these fields, Type, Series, Intervening and End. I can have several times the same Type with the same Series, the same Intervening and different End_Dates. If I have two dates, I can reach the first date by word FIRST and the last by word LAST. My problem is that I wish to reach the number in second, third etc position. It is possible to create a column after End_Date and which indicates by a figure the position of End_Date in the order chonologic, of oldest with most recent, for the same Type and Series. Is it possible to do it directly in Access or VBA?

Code: My Starting Request:

SELECT [Type], [Series], [Intervening],[End_Date]
FROM [Prev]
ORDER BY [Type], [Series], [End_Date];

Type........Serie.....Intervening.....End_Date

124.........13745....684.................09/09/2003
124.........14013....680.................25/02/2003
124.........14013....680.................14/05/2003
124.........14013....680.................02/09/2003
124.........14021....384.................15/07/2003
124.........14089....680.................12/02/2003
124.........14089....680.................17/09/2003
124.........14089....680.................18/06/2003
124.........14089....680.................20/06/2003
124.........15159....184.................01/04/2003


How must I make in Access/VBA to create the Number field and to insert a number in the chronological order for the same Type and Series.

Type........Series....Intervening.....End_Date......Number
124.........14089....680.................12/02/2003...1....... <--Oldest
124.........14089....680.................18/06/2003...2....... <--
124.........14089....680.................20/06/2003...3....... <--
124.........14089....680.................17/09/2003...4....... <--The Last
124.........13745....684.................09/09/2003...1....... <--Oldest
124.........14013....680.................25/02/2003...1....... <--Oldest
124.........14013....680.................14/05/2003...2....... <--
124.........14013....680.................02/09/2003...3....... <--The last
124.........14021....384.................15/07/2003...1....... <--The last

Thanks for your help

Nilses

 

[EMP]

Try this query:-

SELECT [Prev].*, DCount("*","[Prev]","[Type]=" & [Type] & " and [Series]=" & [Series] & " and [End_Date]<=#" & [End_Date]& "#") AS Num
FROM [Prev]
ORDER BY [Type], [Series], [End_Date];

 

[nilses]

Hello EMP,

Thanks for your help, when I launch your code, I 've an error. The data type is incompatibility in the expression of criteria.

The result is this.

Type........Serie.....Intervening.....End_Date.........Num

124.........13745....684.................09/09/2003......#Error
124.........14013....680.................25/02/2003......#Error
124.........14013....680.................14/05/2003......#Error
124.........14013....680.................02/09/2003......#Error
124.........14021....384.................15/07/2003......#Error
124.........14089....680.................12/02/2003......#Error
124.........14089....680.................17/09/2003......#Error
124.........14089....680.................18/06/2003......#Error
124.........14089....680.................20/06/2003......#Error
124.........15159....184.................01/04/2003......#Error

I don't no why, do you help me.

Nilses

 

[EMP]

The attached database contains the query I posted. It works fine on my system.

The database is saved in Access 97 version from Access 2000. You can open it to see if it works on your system.

In the database, I used the field name Series, not Serie.

 

[nilses]

Hello EMP,

Your attached database works on my system, but i 've a question. Sometimes, the num is correct and he's respect the order chonologic like this and sometimes no, why?.

Type...Series...Interv...End_Date......Num...For Me
124....14013...680.......09/02/2003...3........It's the oldest, it's 1
124....14013...680.......25/02/2003...2........It's the second
124....14013...680.......14/05/2003...3........It's the last, It's 3

by your query:

Type...Series...Interv...End_Date......Num...For Me
124....13745...684.......09/09/2003...1........Ok i'ts only one
124....14013...680.......09/02/2003...3........No, It's the first
124....14013...680.......25/02/2003...2........Yes, it's the second
124....14013...680.......14/05/2003...3........Yes, it's the third
124....14021...384.......15/07/2003...1........Ok, it's only one
124....14089...680.......18/06/2003...1........Yes, it's the first
124....14089...680.......20/06/2003...2........Yes, it's the second
124....14089...680.......17/09/2003...3........Yes, it's the third
124....14089...680.......02/12/2003...0........No, it's the fourth
124....15159...184.......04/01/2003...1........Yes, it's the first
133....13333...133.......13/03/2003...1........Yes, it'st the first
133....13333...133.......13/04/2003...2........Yes, it's the second

I don't understand, one time it's run and some time no, why EMP?.

Nilses

 

[EMP]

I think it is because the date format of your system is set to dd/mm/yyyy. My system is in the US format m/d/yyyy, so I don't have any problem running the query.

You can use the Format() function to force Access to compare the dates using yy/mm/dd:-

SELECT [Prev].*, DCount("*","[Prev]","[Type]=" & [Type] & " and [Series]=" & [Series] & " and format([End_Date],'yy/mm/dd')<='" & format([End_Date],'yy/mm/dd') & "'") AS Num
FROM Prev
ORDER BY [Type], [Series], [End_Date];


I have also included another query using Select Count. It should work regardless of date format settings. But the query is non-updatable:-

SELECT a.*, (Select Count(*) from Prev where [Type]=a.[Type] and [Series]=a.[Series] and [End_Date]<=a.[End_Date]) AS Num
FROM Prev AS a
ORDER BY [Type], [Series], [End_Date];

 

[nilses]

Thanks EMP,

It's run perfectly. The date format is always the problem for us, i'm stupid

Thanks for your help ;-)

Have a nice day or evening at your home.

Nilses