Dcount

[guidop12]

Is this the right syntax to add a sequence number to a table


Sequence: (DCount("[Total Revenue]","CompanyTwoYr","[Total Revenue] >=" &
[Total Revenue]))

Thanks

 

[Bob Barrows [MVP]]

Is this the right syntax to add a sequence number to a table


Sequence: (DCount("[Total Revenue]","CompanyTwoYr","[Total Revenue]

=" & [Total Revenue]))

Does it do what you want it to do? If so, it's correct.

 

[guidop12]

Why I asked is because when I run a join query to join two tables together
the query runs and displays the information but it will randomly pop up with
an error message that says

Syntax error (missing operator in query expression '[Total Revenue] >=')
So I'm figuring it comes from the DCount expression.

Is this the right syntax to add a sequence number to a table


Sequence: (DCount("[Total Revenue]","CompanyTwoYr","[Total Revenue]

=" & [Total Revenue]))

Does it do what you want it to do? If so, it's correct.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Michel Walsh]

You field Total Revenue may have null in it, for some record, so AFTER the
concatenation of the strings is done (the third argument), that leads to:

"[Total Revenue] >="


same as if you asked:


4 >=


This is an error, since the string then missed the 'to what' (the right side
of the comparison).

Try:

Sequence: DCount("[Total Revenue]","CompanyTwoYr",
"[Total Revenue] >=" & Nz([Total Revenue], 1+DMax("[Total Revenue]",
"CompanyTwo Yr") ) )



where a record with a null value will then get a 'rank' (sequence number)
of 0 (since there is no record with the Total Revenue >= 1+ MAX(Total
Revenue) )


Vanderghast, Access MVP

Why I asked is because when I run a join query to join two tables together
the query runs and displays the information but it will randomly pop up
with
an error message that says

Syntax error (missing operator in query expression '[Total Revenue] >=')
So I'm figuring it comes from the DCount expression.

Is this the right syntax to add a sequence number to a table


Sequence: (DCount("[Total Revenue]","CompanyTwoYr","[Total Revenue]
=" & [Total Revenue]))

Does it do what you want it to do? If so, it's correct.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[guidop12]

That was perfect

Thanks

You field Total Revenue may have null in it, for some record, so AFTER the
concatenation of the strings is done (the third argument), that leads to:

"[Total Revenue] >="


same as if you asked:


4 >=


This is an error, since the string then missed the 'to what' (the right side
of the comparison).

Try:

Sequence: DCount("[Total Revenue]","CompanyTwoYr",
"[Total Revenue] >=" & Nz([Total Revenue], 1+DMax("[Total Revenue]",
"CompanyTwo Yr") ) )



where a record with a null value will then get a 'rank' (sequence number)
of 0 (since there is no record with the Total Revenue >= 1+ MAX(Total
Revenue) )


Vanderghast, Access MVP

Why I asked is because when I run a join query to join two tables together
the query runs and displays the information but it will randomly pop up
with
an error message that says

Syntax error (missing operator in query expression '[Total Revenue] >=')
So I'm figuring it comes from the DCount expression.

guidop12 wrote:
Is this the right syntax to add a sequence number to a table


Sequence: (DCount("[Total Revenue]","CompanyTwoYr","[Total Revenue]
=" & [Total Revenue]))

Does it do what you want it to do? If so, it's correct.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.