DCount Problem

[BarnetBoy]

DCount probs...still!

I have a query with a field that puts the records in sequential order by the ID field. The code is:

Num: DCount("*","ABC Query","ID <=" & [ID])

this gives results like
ID Num
41 1
43 2
49 4
47 3
55 5
etc..

What I want to do, however, is have the sequence based on the date and time the entry was entered (two separate fields).
ie
Date, Time, Num
01/01/04 09.50 1
01/01/04 09.51 2
02/01/04 10.21 3
03/04/04 09.20 4

etc.

I have tried and tried and tried, but I cannot find a way of getting the dcount thing above to work with a combination of these two fields. I have tried using a field that combines the date and time fields. but that doesn't seem to work.

Can someone with a better grasp of syntax please save the last remaining hairs on my head. thanx in advance.

 

[dcx693]

You might be having a problem referencing your Date and Time fields, since both "Date" and "Time" are VBA reserved words. Try changing to MyDate and MyTime or something else.

The general syntax for two criteria in DCount functions is:

DCount("field","table or query name","[field1] <=" & [some value 1] & " AND [field2] <=" & [some value 2])

Your date and time fields are separated out, with your Time field looking like a string. Don't forget to delimit it properly in the DCount function if it's really a string. Strings need ' ' around them like this:

DCount("*","ABC Query","ID <='" & [ID]"'")

 

[Fizzio]

This follows on from your previous query and dcx has highlighted something I mentioned before ie, not naming fields Date, Time etc as these are reserved words. In general, follow the same question you posted before as you have had a duplicate answer for the same question.

Anyway, less of the preaching, here is a solution to your problem.
It will work on static recordsets ie snapshot but once it becomes dynamic and you start adding records, it will go pear shaped!

 

[BarnetBoy]

Thanks!!

Fizio and DCX,

Thanx sooooo much. This will help me no end. BTW I never had a field that was named using a reserved word, but it had a long name so I was paraphrasing. Sorry for the confusion.

Fizio. thanks for the module. It works amazingly,but it was a little supprising to see what it did when I scrolled my query from left to right in datasheet view. As I will run this query only when running a report it should be fine.

Once again.....THANK YOU!!!![/FONT]
I don't know how or why you guys find the time to help slooow beginners like me, but thanks. I'll get the beers in should we ever meet!

 

[BarnetBoy]

This follows on from your previous query and dcx has highlighted something I mentioned before ie, not naming fields Date, Time etc as these are reserved words. In general, follow the same question you posted before as you have had a duplicate answer for the same question.

Anyway, less of the preaching, here is a solution to your problem.
It will work on static recordsets ie snapshot but once it becomes dynamic and you start adding records, it will go pear shaped!

Fizzio,

Is there a way that I can make this only produce a single sequence? I'm finding that it is giving me unpredictable results.

 

[Fizzio]

I did tell you it would go pear shaped
How does it become unpredictable and how are you trying to use it?

 

[BarnetBoy]

I did tell you it would go pear shaped
How does it become unpredictable and how are you trying to use it?

I was trying to use it in three separate queries and pull them together in a unison query. Once combined I wanted to combine that field with another for which the value would be A, B, or C respectively from each query.

The idea is that I could then sort like this.
1A
1B
1C
2A
2B
2C
3A
3B
3C
4A
4B
4C etc.
but it is coming out like....

1A
2B
3C
4A
5B
6C
7A
8B
9C
10A
11B
12C etc.

It goes mental when I try to base a report on it. the numbers start at 3x the number of records I have.