Dcount

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Naz - 19 Mar 2008 16:41 GMT

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.

Signature

_______________________
Naz,
London

Reply to this Message

Beetle - 19 Mar 2008 17:25 GMT

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.

Signature

_________

Sean Bailey

> Hi
>
[quoted text clipped - 30 lines]
>
> All help is appreciated.

Reply to this Message

Klatuu - 19 Mar 2008 18:55 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> Since your ID field is text you need to use the appropriate quote delimiters.
> Modify the DCount line like this;
[quoted text clipped - 40 lines]
> >
> > All help is appreciated.

Reply to this Message

Beetle - 19 Mar 2008 19:47 GMT

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.?

Signature

_________

Sean Bailey

> In addition, you don't need and really shouldn't use a field name in the
> DCount function. The better way is:
[quoted text clipped - 64 lines]
> > >
> > > All help is appreciated.

Reply to this Message