Help with DCOUNT syntax

[DetRich]

Hello,

I have form which has a hostname (textbox) plus several additional fields.
I would like to validate that the hostname entered by the user already exists
in the master Servers table. I think maybe a DCOUNT is the best way to
accomplish this, but if there is a better way, please advise me. I want to
use the hostname value entered on the form in the WHERE clause like this...

In SQL syntax, the query would be something like:
Select Count (*)
From Table
Where Form.hostname = table.hostname.

I think the DCOUNT syntax would be something like this:

Field table Criteria
If DCOUNT ("Hostname", "lktbl_servers", form.hostname =
lktbl_servers.hostname)

Can anyone help me get the syntax correct? I always go astray with the
single vs. double quotes in the criteria clause.

Thanks in advance,

DetRich

 

[vanderghast]

The third criteria has also to be a string. I don't know what form.hostname
is, do you meant FORMS!hostname!someControlName ? if so:


DCOUNT("HostName", "lktbl_servers", "FORMS!hostName!someControlName =
hostName")


Note also you don't supply the table name for the field name hostName, since
the table name is implicitly referenced by the second argument.


Vanderghast, Access MVP

 

[Krzysztof Naworyta]

Juzer DetRich <[email protected]> napisa³
| Hello,
|
| I have form which has a hostname (textbox) plus several additional
| fields. I would like to validate that the hostname entered by the user
| already exists in the master Servers table. I think maybe a DCOUNT is
| the best way to accomplish this, but if there is a better way, please
| advise me. I want to use the hostname value entered on the form in the
| WHERE clause like this...
|
| In SQL syntax, the query would be something like:
| Select Count (*)
| From Table
| Where Form.hostname = table.hostname.
|
| I think the DCOUNT syntax would be something like this:
|
| Field table Criteria
| If DCOUNT ("Hostname", "lktbl_servers", form.hostname =
| lktbl_servers.hostname)
|
| Can anyone help me get the syntax correct? I always go astray with the
| single vs. double quotes in the criteria clause.

Think about DCount() as a wrapper for SQL.

Lets say: your textbox [hostname] has value "192.168.1.1"
Your SQL could be, as you have written:

Select
Count (*) << function_name, FieldArgument
From
Table << TableArg
Where
table.hostname = "192.168.1.1" << WhereConditions

your function:

DCount(
FieldArgument,
TableArg,
WhereConditions)

(all arguments are strings!)

what means:

DCount(
"*",
"Table",
"hostname = ""192.168.1.1""")
^^ ^^ (!!!)
or

DCount(
"*",
"Table",
"hostname = '192.168.1.1'")
^ ^ (!!!)

Now we have to concatenate the 3rd argument:

"hostname = '" & Forms!FormName!hostname & "'"

Try in the Immediate window:

? "hostname = '" & Forms!FormName!hostname & "'"

does it give this result?:

hostname = '192.168.1.1'


if yes, you can write your function:

DCount(
"*",
"Table",
"hostname = '" & Forms!FormName!hostname & "'")

That's all.