Dcount Counter (1 Viewer)

damian

Registered User.
Local time
Today, 11:01
Joined
Jun 27, 2004
Messages
87
Hi
Is it possible to include a Dcount function in a query (qrySurveyDetail) to sequentially number records where a text field (ImageName) is not null?

RecordID
67
68
57
71

ImageName
Image
Image
No Image
Image


Dcount Number
1
2
Blank
3



The RecordID for each record, although unique, isn't sorted.

I'd like the query to have the ability for users to add to it or delete records and for the images to retain the sequence. ie If the record containing Image 1 was deleted, Image 2 would be renamed Image 1.

If this isn't possible with Dcount, any pointers on the SQL or VBA front?

Thanks
 
Last edited:

damian

Registered User.
Local time
Today, 11:01
Joined
Jun 27, 2004
Messages
87
Thanks John.

In the event that a record doesn't contain an image, the ranking result gives a 1. If there are no images, I'd like this value to be blank and for the next record with an image to obtain the next sequential number. Is there any way of adjusting the function to accommodate this scenario?
 

John Big Booty

AWF VIP
Local time
Today, 22:01
Joined
Aug 29, 2005
Messages
8,263
Have you tried first creating a query to select only those records that contain an image. Then applying the solution to those results.
 

damian

Registered User.
Local time
Today, 11:01
Joined
Jun 27, 2004
Messages
87
Think I've over simplified my initial query in the first post.

Due to the user having the ability to input records anywhere within a subform and in any order, I'm unable to sort on the autonumber (RecordID) or any other field for that matter in order for the likes of your domain function or Dcount to work properly in the underlying query.

Following on with creating a new query that displays only the records with images:

I'm able to sort the query correctly (based on the ChildRecordID for the subform, Distance which is also a number and on a 'Switch' function in order to force the sorting for a text field, forcing values to switch to 1, 2 or 3 with nulls leaving a blank).

I've concatenated these 3 fields (with an underscore as a separator) and thought I had it sussed:

Expr1
1_0.0_2
1_0.0_3
1_114.0_
1_115.0_
1_117.0_
1_123.0_
1_3.0_
3_2.8_
5_13.0_
5_56.0_
5_99.0_

Expr1 is the concatenated field in the query, sorting however is messed up on row number 7 (ie 1_3.0_ ) where the 3.0 is Distance - this should be in row number 3 and knackers up my attempts to finally solve the problem.

I've used a 'Format' function for the distance element of the Expr1 field (ie "000.0) but it results in throwing out the Dcount further still.:banghead:
 

damian

Registered User.
Local time
Today, 11:01
Joined
Jun 27, 2004
Messages
87
Will go down the VBA route instead. Thanks for your help John
 

Users who are viewing this thread

Top Bottom