Whats wrong with this syntax?!.....errrrrrrrrrr

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having difficulties getting the DCount function to work with a variable
as the criteria. Here is my code so far:


Dim numberorrecords As Integer

numberofrecords = DCount

("EntityRecordNumber", "EXTRACTED_Entities", "[ENTITY_IMPORT_DATE] = " &
timestamp)



I want to count the how many records (EntityRecordNumber) there are on my
Extracted_Entities table, whose value for the ENTITY_IMPORT_DATE field equals
the value set in the variable "timestamp"....what am I doing wrong????
 
Assume that ENTITY_IMPORT_DATE has been defined as a date/time data type,
you need to use # to delimit the value. As well, the date needs to be in
mm/dd/yyyy format, regardless of what your Regional Settings might be.
(Okay, this isn't strictly true: it can be in any unambiguous format, such
as yyyy-mm-dd or dd mmm yyyy. The point is, it can't be dd/mm/yyyy or it'll
be misinterpretted for the first 12 days of each month)

numberofrecords = DCount("EntityRecordNumber", _
"EXTRACTED_Entities", _
"[ENTITY_IMPORT_DATE] = " & Format(timestamp, "\#yyyy\-mm\-dd
hh\:nn\:ss\#"))
 
Steve P said:
Dim numberorrecords As Integer

numberofrecords = DCount
Click to expand...

Well, all things aside, if you copied and pasted right out of your code,
there's a typo in your variable declaration statement....

numbero"r"records vs numbero"f"records
 
Doug, thanks for your reply.

I reviewed the design of my EXTRACTED_Entities table and the
ENTITY_IMPORT_DATE field is currently set to a "text" Data Type, which is
acceptable. That particular field on the table is just there to identify
which input file the record was created from.

The logic that the defecto line of code is apart of, counts how many records
meet that criteria and if the count is greater than zero, we'd know that
records with that "timestamp" (which is really only an 11 character string
extracted from a much longer filepath string) have already been uploaded onto
the table, rejecting the request to upload.

Douglas J. Steele said:
Assume that ENTITY_IMPORT_DATE has been defined as a date/time data type,
you need to use # to delimit the value. As well, the date needs to be in
mm/dd/yyyy format, regardless of what your Regional Settings might be.
(Okay, this isn't strictly true: it can be in any unambiguous format, such
as yyyy-mm-dd or dd mmm yyyy. The point is, it can't be dd/mm/yyyy or it'll
be misinterpretted for the first 12 days of each month)

numberofrecords = DCount("EntityRecordNumber", _
"EXTRACTED_Entities", _
"[ENTITY_IMPORT_DATE] = " & Format(timestamp, "\#yyyy\-mm\-dd
hh\:nn\:ss\#"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Steve P said:
I'm having difficulties getting the DCount function to work with a
variable
as the criteria. Here is my code so far:


Dim numberorrecords As Integer

numberofrecords = DCount

("EntityRecordNumber", "EXTRACTED_Entities", "[ENTITY_IMPORT_DATE] = " &
timestamp)



I want to count the how many records (EntityRecordNumber) there are on my
Extracted_Entities table, whose value for the ENTITY_IMPORT_DATE field
equals
the value set in the variable "timestamp"....what am I doing wrong????
Click to expand...
Click to expand...
 
If it's a text field, you need to put quotes around the value:

numberofrecords = DCount("EntityRecordNumber", _
"EXTRACTED_Entities", _
"[ENTITY_IMPORT_DATE] = '" & timestamp & "'")

Exagerated for clarity, that last line is

"[ENTITY_IMPORT_DATE] = ' " & timestamp & " ' ")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Steve P said:
Doug, thanks for your reply.

I reviewed the design of my EXTRACTED_Entities table and the
ENTITY_IMPORT_DATE field is currently set to a "text" Data Type, which is
acceptable. That particular field on the table is just there to identify
which input file the record was created from.

The logic that the defecto line of code is apart of, counts how many
records
meet that criteria and if the count is greater than zero, we'd know that
records with that "timestamp" (which is really only an 11 character string
extracted from a much longer filepath string) have already been uploaded
onto
the table, rejecting the request to upload.

Douglas J. Steele said:
Assume that ENTITY_IMPORT_DATE has been defined as a date/time data type,
you need to use # to delimit the value. As well, the date needs to be in
mm/dd/yyyy format, regardless of what your Regional Settings might be.
(Okay, this isn't strictly true: it can be in any unambiguous format,
such
as yyyy-mm-dd or dd mmm yyyy. The point is, it can't be dd/mm/yyyy or
it'll
be misinterpretted for the first 12 days of each month)

numberofrecords = DCount("EntityRecordNumber", _
"EXTRACTED_Entities", _
"[ENTITY_IMPORT_DATE] = " & Format(timestamp, "\#yyyy\-mm\-dd
hh\:nn\:ss\#"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Steve P said:
I'm having difficulties getting the DCount function to work with a
variable
as the criteria. Here is my code so far:


Dim numberorrecords As Integer

numberofrecords = DCount

("EntityRecordNumber", "EXTRACTED_Entities", "[ENTITY_IMPORT_DATE] = "
&
timestamp)



I want to count the how many records (EntityRecordNumber) there are on
my
Extracted_Entities table, whose value for the ENTITY_IMPORT_DATE field
equals
the value set in the variable "timestamp"....what am I doing wrong????
Click to expand...
Click to expand...
Click to expand...
 
Hey guys, thanks for the help. It was a very "newbie" mistake..lol.

Here is the code that finally ended up working:

numberofrecords = DCount("[EntityRecordNumber]", "EXTRACTED_Entities",
"[ENTITY_IMPORT_DATE] = '" & timestamp & "'")

Apparently, I needed to concatenate the variable into the function with
single quotes and concatenate the final double quote as well.
 
Back
Top