DCOUNT

[DawnP277]

I have a Dcount in VB that is getting a syntax error getting a runtime error
3075.

Here is my code.

If someone could tell me what I have wrong that would be appreciated.

Private Sub SerialNum_AfterUpdate()
Dim Serial As Variant
Dim fcrate As String
Dim IntCount As Variant
Dim CrateNum As String
Serial = DLookup("[TestParm]", "Tabledata", "[Serial Number] =
Form![SerialNum]")


Select Case Serial
Case "LOW"
fcrate = Forms![CratesUsed]![Text0]
Case "MED"
fcrate = Forms![CratesUsed]![Text2]
Case "HIGH"
fcrate = Forms![CratesUsed]![Text4]
End Select

CrateNum = CStr(fcrate)
IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum)
MsgBox (IntCount)
IntCount = IntCount + 1
End Sub


Thanks
Dawn

 

[vanderghast]

The syntaxt is wrong. You need FORMS, not form, then the form name, then the
control name supplying the value to compare to:

Serial = DLookup("[TestParm]", "Tabledata",
"[Serial Number] = FORMS!FormNameHere![SerialNum]")


(sure, there is no need to use all cap, you can use forms instead of FORMS,
but the final s is required).


Vanderghast, Access MVP

 

[Roger Carlson]

Since CrateNum is a string, the DCount should be:

IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[DawnP277]

Thak you Roger that worked.

So for future reference, a value that is a string needs to have an & in the
front and bak enclosed in quotes?

Since CrateNum is a string, the DCount should be:

IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a Dcount in VB that is getting a syntax error getting a runtime
error
3075.

Here is my code.

If someone could tell me what I have wrong that would be appreciated.

Private Sub SerialNum_AfterUpdate()
Dim Serial As Variant
Dim fcrate As String
Dim IntCount As Variant
Dim CrateNum As String
Serial = DLookup("[TestParm]", "Tabledata", "[Serial Number] =
Form![SerialNum]")


Select Case Serial
Case "LOW"
fcrate = Forms![CratesUsed]![Text0]
Case "MED"
fcrate = Forms![CratesUsed]![Text2]
Case "HIGH"
fcrate = Forms![CratesUsed]![Text4]
End Select

CrateNum = CStr(fcrate)
IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum)
MsgBox (IntCount)
IntCount = IntCount + 1
End Sub


Thanks
Dawn

 

[Roger Carlson]

Well, this is sort of complicated.

The third argument of the DCount is an SQL Where clause without the "WHERE"
keyword. In a Where clause, string or text values must have string
delimiters: either quote marks (") or apostrophe ('). But the entire
argument must also be encased in string delimiters and to throw yet another
thing to gum the works, you've got a variable rather than a literal string
value.

So what you want to be evaluated (after the variable value insertion) is:
[Crate] = "7"
So if I put quotes around the whole thing, I get
"[Crate] = "7""
This will error because of the way the interpreter reads the quotes. In
order to put a quote within a quoted string, you have to double the quotes:
"[Crate] = ""7"""
But now I need to replace the explicit 7 with the variable:
"[Crate] = "" & CrateNum & """
But this will error because now we have two different strings that have to
be concatenated and the interpreter isn't reading the quotes right again.
to fix it, you have to douple the inner quotes again:
"[Crate] = """ & CrateNum & """"

Now, I said you can also do this with apostophes. So let me repeat the
process:
[Crate] = '7'
can be surrounded with quotes this way and it will not error.
"[Crate] = '7'"
But adding the variable again:
"[Crate] = ' & CrateNum & '"
gives us two incomplete strings. We need to add a quote to each:
"[Crate] = '" & CrateNum & "'"

So either:
"[Crate] = '" & CrateNum & "'"
or
"[Crate] = """ & CrateNum & """"
will work. If it's possible for your data to have an apostrophe in it, you
have to use the second.

You can also use the Chr$(34) character (which the code for a quote) like
this:

"[Crate] = " & Chr$(34) & CrateNum & Chr$(34)


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thak you Roger that worked.

So for future reference, a value that is a string needs to have an & in
the
front and bak enclosed in quotes?

Since CrateNum is a string, the DCount should be:

IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a Dcount in VB that is getting a syntax error getting a runtime
error
3075.

Here is my code.

If someone could tell me what I have wrong that would be appreciated.

Private Sub SerialNum_AfterUpdate()
Dim Serial As Variant
Dim fcrate As String
Dim IntCount As Variant
Dim CrateNum As String
Serial = DLookup("[TestParm]", "Tabledata", "[Serial Number] =
Form![SerialNum]")


Select Case Serial
Case "LOW"
fcrate = Forms![CratesUsed]![Text0]
Case "MED"
fcrate = Forms![CratesUsed]![Text2]
Case "HIGH"
fcrate = Forms![CratesUsed]![Text4]
End Select

CrateNum = CStr(fcrate)
IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum)
MsgBox (IntCount)
IntCount = IntCount + 1
End Sub


Thanks
Dawn

 

[vanderghast]

Strange.


I don't have Northwind installed at this moment, so I cannot supply an
example based on it, but I am sure the syntax:


DCount("*", "TableNameHere", "Crate = FORMS!formNameHere!ControlNameHere ")


works quite fine, WITHOUT ANY PROBLEM, even without REQUIRING any delimiter
(since you supply the container, name not the constant itself), even if
Crate is a field with data type as string, or date, or if the control value
contains ' or " or # or... AND all these PROBLEMS that can arise if you
move the argument "out" of the "string", like:


DCount("*", "TableNameHere", "Crate = " &
FORMS!formNameHere!ControlNameHere )


(which, as it is, won't work for string, date, ... etc).


Strange, but since the OP said that the second syntax now works, while the
first one does not..., am I really in a position to argue?



Vanderghast, Access MVP

Well, this is sort of complicated.

The third argument of the DCount is an SQL Where clause without the
"WHERE" keyword. In a Where clause, string or text values must have
string delimiters: either quote marks (") or apostrophe ('). But the
entire argument must also be encased in string delimiters and to throw yet
another thing to gum the works, you've got a variable rather than a
literal string value.

So what you want to be evaluated (after the variable value insertion) is:
[Crate] = "7"
So if I put quotes around the whole thing, I get
"[Crate] = "7""
This will error because of the way the interpreter reads the quotes. In
order to put a quote within a quoted string, you have to double the
quotes:
"[Crate] = ""7"""
But now I need to replace the explicit 7 with the variable:
"[Crate] = "" & CrateNum & """
But this will error because now we have two different strings that have to
be concatenated and the interpreter isn't reading the quotes right again.
to fix it, you have to douple the inner quotes again:
"[Crate] = """ & CrateNum & """"

Now, I said you can also do this with apostophes. So let me repeat the
process:
[Crate] = '7'
can be surrounded with quotes this way and it will not error.
"[Crate] = '7'"
But adding the variable again:
"[Crate] = ' & CrateNum & '"
gives us two incomplete strings. We need to add a quote to each:
"[Crate] = '" & CrateNum & "'"

So either:
"[Crate] = '" & CrateNum & "'"
or
"[Crate] = """ & CrateNum & """"
will work. If it's possible for your data to have an apostrophe in it,
you have to use the second.

You can also use the Chr$(34) character (which the code for a quote) like
this:

"[Crate] = " & Chr$(34) & CrateNum & Chr$(34)


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thak you Roger that worked.

So for future reference, a value that is a string needs to have an & in
the
front and bak enclosed in quotes?

Since CrateNum is a string, the DCount should be:

IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum & "'")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I have a Dcount in VB that is getting a syntax error getting a runtime
error
3075.

Here is my code.

If someone could tell me what I have wrong that would be appreciated.

Private Sub SerialNum_AfterUpdate()
Dim Serial As Variant
Dim fcrate As String
Dim IntCount As Variant
Dim CrateNum As String
Serial = DLookup("[TestParm]", "Tabledata", "[Serial Number] =
Form![SerialNum]")


Select Case Serial
Case "LOW"
fcrate = Forms![CratesUsed]![Text0]
Case "MED"
fcrate = Forms![CratesUsed]![Text2]
Case "HIGH"
fcrate = Forms![CratesUsed]![Text4]
End Select

CrateNum = CStr(fcrate)
IntCount = DCount("[ID]", "FinisherTable", _
"[Crate] = '" & CrateNum)
MsgBox (IntCount)
IntCount = IntCount + 1
End Sub


Thanks
Dawn