Dcount

[Naz]

Hi

I'm using the Flex Grid demo (thanks peter h)...i've managed to change most
of it to my needs but have come to a stubbling block.

The demo uses Project ID in two tables, they are defined as numbers. My
project ID needs to be Text, so i changed it in both tables opened one of the
forms and got a " Data type mismatch in criteria expression " error, although
i have changed the data type the data remains unchanged.

The part of the code is below, same error for Form Open, the dcount line is
where it fails. I assume its something to do with the syntax for Text having
only changed it from Number....but i just can't seem to figure it out.

Private Sub btnExpandAll_Click()

Dim rst As Recordset

mExpand = ""
Set rst = CurrentDb.OpenRecordset("SELECT ProjectID FROM tblProjects")

Do Until rst.EOF
If DCount("ID", "tblProjectTasks", "ProjectID = " & rst!ProjectID) >
0 Then
mExpand = mExpand & "." & rst!ProjectID & "."
End If
rst.MoveNext
Loop
rst.Close
FillGrid txtDate

End Sub


All help is appreciated.

 

[Beetle]

Since your ID field is text you need to use the appropriate quote delimiters.
Modify the DCount line like this;

If DCount("ID", "tblProjectTasks", "ProjectID = '" & rst!ProjectID & "'") >

for clarity it's

If DCount("ID", "tblProjectTasks", "ProjectID = ' " & rst!ProjectID " ' ") >
but don't put spaces between the quotes.

 

[Klatuu]

In addition, you don't need and really shouldn't use a field name in the
DCount function. The better way is:

DCount("*", "tblProjectTasks", "ProjectID = """ & rst!ProjectID & """")

Note the change in quote marks. It is 3 doubles before and two doubles
after. In probably is not an issue in this case, but if any record has an
apostrephe in the field, it will throw an error. It seems daunting at first,
but here is how to easily figure it out.

First, to include a " in a string in VBA, use two qoutes ""

So, in the original version:
DCount("*", "tblProjectTasks", "ProjectID = '" & rst!ProjectID & "'")
Single Quotes ^
^

So replace one single ' with two doubles "" It then becomes
DCount("ID", "tblProjectTasks", "ProjectID = """ & rst!ProjectID & """")

That's the way I have taught myself because I can never get it right the
first time, so I write it with the single qoutes then go back and replace a
single with two doubles.

 

[Beetle]

You're right about the double quotes Dave. I usually don't get it right the
first
time either, so that's a good tip about replacing a single with two doubles.

That's the first time I've heard that you shouldn't use a field name in the
DCount function. Any specific reason, or is it just to eliminate the chance
of having a mis-spelled field name, etc.?