DCount works for one field but does not work with two fields

R

RCGUA

I have a table named tblEquipment with two columns, the first column
has date and the column is named MachineDate the second column has
a location number and the column is named MachineLocationNumber I
have two variables stDate represents the date and stLocation is for
the MachineLocationNumber. I can find records that match the date
and records that match the location number, but the DCount below (the
third one) shows zero even when there is a record in the table that
matches -both- the stDate and stLocation

==== The first DCount code below works and it counts the number of
records in the table that match the stDate variable.
The second DCount code below works and it counts the number of records
in the table that match the stLocation variable.
The third always counts zero. ===========

If DCount("*", "tblEquipment", "[MachineDate] = #" & stDate & "#") > 0
Then
MsgBox "greater than zero"
End If

If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation) >
0 Then
MsgBox "greater than zero"
End If

If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation +
"[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
 
K

Ken Snell [MVP]

Build the third argument like a WHERE clause (you also left out some "
characters):

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " & stLocation & "
AND [MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
 
R

RCGUA

Build the third argument like a WHERE clause (you also left out some "
characters):

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " & stLocation & "
AND [MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/




I have a table named tblEquipment with two columns, the first column
has date and the column is named MachineDate    the second column has
a location number and the column is named MachineLocationNumber   I
have two variables stDate represents the date and stLocation is for
the MachineLocationNumber.   I can find records that match the date
and records that match the location number, but the DCount below (the
third one) shows zero even when there is a record in the table that
matches -both- the stDate and stLocation
Click to expand...
==== The first DCount code below works and it counts the numberof
records in the table that match the stDate variable.
The second DCount code below works and it counts the number of records
in the table that match the stLocation variable.
The third always counts zero. ===========
Click to expand...
If DCount("*", "tblEquipment", "[MachineDate] = #" & stDate & "#") > 0
Then
MsgBox "greater than zero"
End If
Click to expand...
If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation) >
0 Then
MsgBox "greater than zero"
End If
Click to expand...
If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation +
"[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...
Click to expand...

Your solution works perfectly. I was trying to simplify things so I
only used two fields but I really need to match on three fields. I
added MachineName but I have something wrong with a comma or
something.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
 
D

Damon Heron

Remove " right before the [MachineName] ---
so:

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND [MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If

Damon

Build the third argument like a WHERE clause (you also left out some "
characters):

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " & stLocation &
"
AND [MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/




I have a table named tblEquipment with two columns, the first column
has date and the column is named MachineDate the second column has
a location number and the column is named MachineLocationNumber I
have two variables stDate represents the date and stLocation is for
the MachineLocationNumber. I can find records that match the date
and records that match the location number, but the DCount below (the
third one) shows zero even when there is a record in the table that
matches -both- the stDate and stLocation
Click to expand...
==== The first DCount code below works and it counts the number of
records in the table that match the stDate variable.
The second DCount code below works and it counts the number of records
in the table that match the stLocation variable.
The third always counts zero. ===========
Click to expand...
If DCount("*", "tblEquipment", "[MachineDate] = #" & stDate & "#") > 0
Then
MsgBox "greater than zero"
End If
Click to expand...
If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation) >
0 Then
MsgBox "greater than zero"
End If
Click to expand...
If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation +
"[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...
Click to expand...

Your solution works perfectly. I was trying to simplify things so I
only used two fields but I really need to match on three fields. I
added MachineName but I have something wrong with a comma or
something.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
 
R

RCGUA

Remove " right before the [MachineName] ---
so:

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND [MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If

Damon


Build the third argument like a WHERE clause (you also left out some "
characters):
Click to expand...
If DCount("*", "tblEquipment", "[MachineLocationNumber] = " & stLocation &
"
AND [MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
--
Click to expand...

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
news:3289df9d-b4cd-4b36-ad45-ddfa20855122@w41g2000yqb.googlegroups.com....
Click to expand...
I have a table named tblEquipment with two columns, the first column
has date and the column is named MachineDate the second column has
a location number and the column is named MachineLocationNumber I
have two variables stDate represents the date and stLocation is for
the MachineLocationNumber. I can find records that match the date
and records that match the location number, but the DCount below (the
third one) shows zero even when there is a record in the table that
matches -both- the stDate and stLocation
==== The first DCount code below works and it counts the number of
records in the table that match the stDate variable.
The second DCount code below works and it counts the number of records
in the table that match the stLocation variable.
The third always counts zero. ===========
If DCount("*", "tblEquipment", "[MachineDate] = #" & stDate & "#") > 0
Then
MsgBox "greater than zero"
End If
If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation) >
0 Then
MsgBox "greater than zero"
End If
If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation+
"[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...
Click to expand...

Your solution works perfectly.  I was trying to simplify things so I
only used two fields but I really need to match on three fields.  I
added MachineName but I have something wrong with a comma or
something.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...

Thanks -- I get Syntax error (missing operator)
 
B

Bob Quintal

m:
Build the third argument like a WHERE clause (you also left out
some " characters):

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocatio n & "
AND [MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


.com...


I have a table named tblEquipment with two columns, the first
column
has date and the column is named MachineDate    the second
column h as
a location number and the column is named MachineLocationNumber
  I have two variables stDate represents the date and
stLocation is for the MachineLocationNumber.   I can find
records that match the date and records that match the location
number, but the DCount below (the third one) shows zero even
when there is a record in the table that matches -both- the
stDate and stLocation
Click to expand...
==== The first DCount code below works and it counts the number of
records in the table that match the stDate variable.
The second DCount code below works and it counts the number of
records in the table that match the stLocation variable.
The third always counts zero. ==========
Click to expand...
If DCount("*", "tblEquipment", "[MachineDate] = #" & stDate &
"#") > 0
Then
MsgBox "greater than zero"
End If
Click to expand...
If DCount("*", "tblEquipment", [MachineLocationNumber] =
stLocation)
Click to expand...
0 Then
MsgBox "greater than zero"
End If
Click to expand...
If DCount("*", "tblEquipment", [MachineLocationNumber] =
stLocation + "[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...
Click to expand...

Your solution works perfectly. I was trying to simplify things so
I only used two fields but I really need to match on three fields.
I added MachineName but I have something wrong with a comma or
something.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...
not a comma but quotes.

AND "[MachineName] = """ & stMachineName & """ AND

to embed a single double quote in a string you use double double
quotes.
 
R

RCGUA

m:




Build the third argument like a WHERE clause (you also left out
some " characters):
If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocatio n & "
AND [MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
--
        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
.com...
I have a table named tblEquipment with two columns, the first
column
has date and the column is named MachineDate    the second
column h as
a location number and the column is named MachineLocationNumber
  I have two variables stDate represents the date and
stLocation is for the MachineLocationNumber.   I can find
records that match the date and records that match the location
number, but the DCount below (the third one) shows zero even
when there is a record in the table that matches -both- the
stDate and stLocation
==== The first DCount code below works and it counts the number  of
records in the table that match the stDate variable.
The second DCount code below works and it counts the number of
records in the table that match the stLocation variable.
The third always counts zero. ==========
If DCount("*", "tblEquipment", "[MachineDate] = #" & stDate &
"#") > 0
Then
MsgBox "greater than zero"
End If
If DCount("*", "tblEquipment", [MachineLocationNumber] =
stLocation)
0 Then
MsgBox "greater than zero"
End If
If DCount("*", "tblEquipment", [MachineLocationNumber] =
stLocation + "[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...
Click to expand...
Your solution works perfectly.  I was trying to simplify things so
I only used two fields but I really need to match on three fields.
 I added MachineName but I have something wrong with a comma or
something.
Click to expand...
If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = " & stMachineName & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
MsgBox "greater than zero"
End If
Click to expand...

not a comma but quotes.

AND "[MachineName] = """ & stMachineName & """ AND

to embed a single double quote in a string you use double double
quotes.
Click to expand...

I still can't get this to work.
I tried using the double quotes, and tried various versions of below
but it still will not work. I'm sorry, but I'm not clear on the
syntax for dates and column names and varaibles.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = """ & stMachineName & """ AND
[MachineDate] = #" & stDate & "#") > 0 Then
 
H

Hans Up

RCGUA said:
I still can't get this to work.
I tried using the double quotes, and tried various versions of below
but it still will not work. I'm sorry, but I'm not clear on the
syntax for dates and column names and varaibles.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = """ & stMachineName & """ AND
[MachineDate] = #" & stDate & "#") > 0 Then
Click to expand...

How many records in tblEquipment satisfy all 3 conditions?
 
D

Douglas J. Steele

You didn't remove the erroneous double quote in front of [MachineName] ,
like Damon said to:

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " & stLocation & "
AND [MachineName] = """ & stMachineName & """ AND [MachineDate] = #" &
stDate & "#") > 0 Then

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hans Up said:
RCGUA said:
I still can't get this to work.
I tried using the double quotes, and tried various versions of below
but it still will not work. I'm sorry, but I'm not clear on the
syntax for dates and column names and varaibles.

If DCount("*", "tblEquipment", "[MachineLocationNumber] = " &
stLocation & " AND "[MachineName] = """ & stMachineName & """ AND
[MachineDate] = #" & stDate & "#") > 0 Then
Click to expand...

How many records in tblEquipment satisfy all 3 conditions?
Click to expand...
 
J

John W. Vinson

I'm sorry, but I'm not clear on the
syntax for dates and column names and varaibles.
Click to expand...

The third argument of a domain function must be a Text String which evaluates
to a valid SQL WHERE clause, without the word WHERE - try building a query
that gets the desired results and view its SQL to see. Normally this string
will be constructed in pieces, some text literals (enclosed in " characters)
and some variables (either VBA variables or form references or field
references to other fields in the same query).

Literal dates must be delimited by # characters.
Text strings must be delimited by quotemarks - either ' or " will work. The
singlequote character ' can cause problems if the text string might contain an
apostrophe (O'Brien, Fred's Diner); the " character can be a hassle since you
use " to delimit string constants. You can use two consecutive " to represent
one " in a literal string.
Number fields should have no delimiter.
It NEVER hurts to delimit fieldnames or form/report control references with
square brackets; it's often essential to do so.
If you use a VBA variable name, it needs to be OUTSIDE the quotes (so that
you're including its value rather than its name).

In your case, you originally had:

If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation +
"[MachineDate] = #" & stDate & "#") > 0 Then

What you want the third argument to END UP looking like (assuming that
MachineLocationNumber is of Text datatype) might be

[MachineLocationNumber] = "A1233" AND [MachineDate] = #8/15/2009#

Quotemarks around the location value, # delimiters around the date...

You could build this up from pieces, using the convention that "" inside a
string equates to ":

A string litral, with no variables -

"[MachineLocationNumber] = """

The three quotes at the end are a double doublequote *inside* the string
literal (to get a " mark in the result), and a doublequote to terminate the
string literal.

stLocation - your VBA variable. You might find occasion to instead use
something like

[Forms]![SomeFormname]![SomeTextbox]

instead of stLocation.

Another string literal:

""" AND [MachineDate] = #"

Note the blank before AND (needed to keep the text from running together with
the variable you already appended), and the inclusion of the word AND, part of
the valid SQL for the criterion.

stDate

Just be sure that stDate is in mm/dd/yyyy format or an unambiguous format such
as yyyy-mm-dd; European date formats such as 16/8/2009 will mess you up badly!

And a final literal

"#"

In sum,

"[MachineLocationNumber] = """ & stLocation & """ AND [MachineDate] = #" &
stMachineDate & "#"
 
R

RCGUA

I'm sorry, but I'm not clear on the
syntax for dates and column names and varaibles.
Click to expand...

The third argument of a domain function must be a Text String which evaluates
to a valid SQL WHERE clause, without the word WHERE - try building a query
that gets the desired results and view its SQL to see. Normally this string
will be constructed in pieces, some text literals (enclosed in " characters)
and some variables (either VBA variables or form references or field
references to other fields in the same query).

Literal dates must be delimited by # characters.
Text strings must be delimited by quotemarks - either ' or " will work. The
singlequote character ' can cause problems if the text string might contain an
apostrophe (O'Brien, Fred's Diner); the " character can be a hassle sinceyou
use " to delimit string constants. You can use two consecutive " to represent
one " in a literal string.
Number fields should have no delimiter.
It NEVER hurts to delimit fieldnames or form/report control references with
square brackets; it's often essential to do so.
If you use a VBA variable name, it needs to be OUTSIDE the quotes (so that
you're including its value rather than its name).

In your case, you originally had:

If DCount("*", "tblEquipment", [MachineLocationNumber] = stLocation +
"[MachineDate] = #" & stDate & "#")  > 0 Then

What you want the third argument to END UP looking like (assuming that
MachineLocationNumber is of Text datatype) might be

[MachineLocationNumber] = "A1233" AND [MachineDate] = #8/15/2009#

Quotemarks around the location value, # delimiters around the date...

You could build this up from pieces, using the convention that "" inside a
string equates to ":

A string litral, with no variables -

"[MachineLocationNumber] = """

The three quotes at the end are a double doublequote *inside* the string
literal (to get a " mark in the result), and a doublequote to terminate the
string literal.

stLocation  - your VBA variable. You might find occasion to instead use
something like

[Forms]![SomeFormname]![SomeTextbox]

instead of stLocation.

Another string literal:

""" AND [MachineDate] = #"

Note the blank before AND (needed to keep the text from running together with
the variable you already appended), and the inclusion of the word AND, part of
the valid SQL for the criterion.

stDate

Just be sure that stDate is in mm/dd/yyyy format or an unambiguous formatsuch
as yyyy-mm-dd; European date formats such as 16/8/2009 will mess you up badly!

And a final literal

"#"

In sum,

"[MachineLocationNumber] = """ & stLocation & """ AND [MachineDate] =#" &
stMachineDate & "#"
Click to expand...

Thank You all very very much for your help. I managed to get this
working with the code below, however, the MachineLocationNumber field
in the table is a long integer field and the only way I could get rid
of a "data type mismatch" error was to change the
MachineLocationNumber to a text field.

If DCount("*", "tblEquipment", "[MachineName] = '" & stMachineName &
"' AND [MachineLocationNumber] = '" & stLocation & "' AND
[MachineDate] = #" & stDate & "#") > 0 Then
 
H

Hans Up

RCGUA said:
Thank You all very very much for your help. I managed to get this
working with the code below, however, the MachineLocationNumber field
in the table is a long integer field and the only way I could get rid
of a "data type mismatch" error was to change the
MachineLocationNumber to a text field.

If DCount("*", "tblEquipment", "[MachineName] = '" & stMachineName &
"' AND [MachineLocationNumber] = '" & stLocation & "' AND
[MachineDate] = #" & stDate & "#") > 0 Then
Click to expand...

If you want to change MachineLocationNumber back to a number, you can
make the DCount expression work by eliminating the single quotes
surrounding stLocation.

Dim strCriteria As String
strCriteria = "[MachineName] = '" & stMachineName & _
"' AND [MachineLocationNumber] = " & stLocation & _
" AND [MachineDate] = #" & stDate & "#"
Debug.Print strCriteria
If DCount("*", "tblEquipment", strCriteria) > 0 Then
.....

The advantage of assigning the criteria to a string variable, is
Debug.Print gives you an opportunity to view exactly what you're
submitting to the DCount function (after Access has finished building
the expression from your other variables).

Viewing the completed expression should help troubleshoot the quotes.
You can also copy the criteria from the Debug.Print statement and paste
it as the WHERE clause of a new query in SQL View:

SELECT * FROM tblEquipment WHERE put_your_criteria_here

Test that query to see if your criteria are valid.

Debug.Print places its output in the Immediate Window. You can get
there from Access with the Ctrl+g keystroke combination.

Good luck,
Hans
 
J

John W. Vinson

Thank You all very very much for your help. I managed to get this
working with the code below, however, the MachineLocationNumber field
in the table is a long integer field and the only way I could get rid
of a "data type mismatch" error was to change the
MachineLocationNumber to a text field.

If DCount("*", "tblEquipment", "[MachineName] = '" & stMachineName &
"' AND [MachineLocationNumber] = '" & stLocation & "' AND
[MachineDate] = #" & stDate & "#") > 0 Then
Click to expand...

My apologies: I misunderstood (the fact that you had the st- prefix on
Location made me think it was a Text field in the table, and I didn't go back
and reread the thread).

Again, as I said to start: Text fields require ' or " delimiters; Date fields
require # delimiters; Number fields should have NO delimiters.

Rather than changing the datatype of MachineLocationNumber, just remove the
quote marks:

If DCount("*", "tblEquipment", "[MachineName] = '" & stMachineName &
"' AND [MachineLocationNumber] = " & stLocation & " AND
[MachineDate] = #" & stDate & "#") > 0 Then
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DCount with one single condition works, but several ones, joined together with the logical operator 3
dcount issue 2
DCount problem... 1
Searching Across Two Tables For A Record 3
Curiosity question relating to Dcount in a Text Box and in VBA 3
check for two fields instead of one 4
Update query and using two tables in dcount statement 11
Duplicate Entry checking with one field Null 5

Top