DCount problem...

[John]

I am importing a spreadsheet into a table in my database, however I need to
check the data that's in the table prior to importing the spreadsheet. I
can't seem to get the DCount function to work for me. Can anyone spot what
my problem may be? Here's the code:

'Check to see if this monthly is already in the tblMonthly table.
Set db = CurrentDb 'Do I even need this?
RptDte = DateValue(Right(Trim(strThrDte), 8)) '(strThrDte is a long string.
The date is in the last char of the string in MM/DD/YY format.
rsCnt = 0
rsCnt = DCount("[fnDte]", "tblMonthly", "Month([tblMonthly]![fnDte]) =
Month(" & RptDte & ") And Year([tblMonthly]![fnDte]) = Year(" & RptDte & ")")
'if the record set count is over zero, the month / year combination is
already in the table. If it's greater than 0, data is already there so don't
import.

If rsCnt = 0 Then
'This data does not exist in the tblMonthly table.
Else
'This monthly already is in the tblMonthly table.
MsgBox "There is alredy a monthly report in Project Cost History for " &
Format(RptDte, "MMM-YY") & ". Delete the old monthly before importing a new
one.", vbOKOnly, "Monthly exsists already..."
Exit Sub
End If

I have to check the month and year rather than just the date, because the
exact day is not known.

 

[Chris O'C via AccessMonster.com]

Dates need to be surrounded by the pound sign so Jet knows it's a date and
not a string.

rsCnt = DCount("[fnDte]", "tblMonthly", _
"Month([tblMonthly]![fnDte]) = Month(#" _
& RptDte & "#) And Year([tblMonthly]![fnDte]) = Year(#" _
& RptDte & "#)")

Chris
Microsoft MVP

I am importing a spreadsheet into a table in my database, however I need to
check the data that's in the table prior to importing the spreadsheet. I
can't seem to get the DCount function to work for me. Can anyone spot what
my problem may be? Here's the code:

'Check to see if this monthly is already in the tblMonthly table.
Set db = CurrentDb 'Do I even need this?
RptDte = DateValue(Right(Trim(strThrDte), 8)) '(strThrDte is a long string.
The date is in the last char of the string in MM/DD/YY format.
rsCnt = 0
rsCnt = DCount("[fnDte]", "tblMonthly", "Month([tblMonthly]![fnDte]) =
Month(" & RptDte & ") And Year([tblMonthly]![fnDte]) = Year(" & RptDte & ")")
'if the record set count is over zero, the month / year combination is
already in the table. If it's greater than 0, data is already there so don't
import.

If rsCnt = 0 Then
'This data does not exist in the tblMonthly table.
Else
'This monthly already is in the tblMonthly table.
MsgBox "There is alredy a monthly report in Project Cost History for " &
Format(RptDte, "MMM-YY") & ". Delete the old monthly before importing a new
one.", vbOKOnly, "Monthly exsists already..."
Exit Sub
End If

I have to check the month and year rather than just the date, because the
exact day is not known.

 

[John]

That was it! Thanks Chris...
--
Thanks - John

Dates need to be surrounded by the pound sign so Jet knows it's a date and
not a string.

rsCnt = DCount("[fnDte]", "tblMonthly", _
"Month([tblMonthly]![fnDte]) = Month(#" _
& RptDte & "#) And Year([tblMonthly]![fnDte]) = Year(#" _
& RptDte & "#)")

Chris
Microsoft MVP

I am importing a spreadsheet into a table in my database, however I need to
check the data that's in the table prior to importing the spreadsheet. I
can't seem to get the DCount function to work for me. Can anyone spot what
my problem may be? Here's the code:

'Check to see if this monthly is already in the tblMonthly table.
Set db = CurrentDb 'Do I even need this?
RptDte = DateValue(Right(Trim(strThrDte), 8)) '(strThrDte is a long string.
The date is in the last char of the string in MM/DD/YY format.
rsCnt = 0
rsCnt = DCount("[fnDte]", "tblMonthly", "Month([tblMonthly]![fnDte]) =
Month(" & RptDte & ") And Year([tblMonthly]![fnDte]) = Year(" & RptDte & ")")
'if the record set count is over zero, the month / year combination is
already in the table. If it's greater than 0, data is already there so don't
import.

If rsCnt = 0 Then
'This data does not exist in the tblMonthly table.
Else
'This monthly already is in the tblMonthly table.
MsgBox "There is alredy a monthly report in Project Cost History for " &
Format(RptDte, "MMM-YY") & ". Delete the old monthly before importing a new
one.", vbOKOnly, "Monthly exsists already..."
Exit Sub
End If

I have to check the month and year rather than just the date, because the
exact day is not known.