dcount error cant find the field "|" referred to in your expression 3

[Johnnycat1]

Hello all...

I have what I thought to be simple dcount in some VB code that is making me crazy! The code reads:

strTotalMonHrs = DCount "[WEID]", "TblTimeSheetsDailyLogEmployees", [WEID] = strWEIDCode)

Error that this creates is that the database "can't find the field "|" referred to in your expression"

What I am banging my head on the wall with is that [WEID] = strWEIDCode returns a matching field so I don't understand why the criteria keeps giving me an error.

Your help is very appreciated!

 

[Johnnycat1]

I should probably add the entire code so that you can see the bigger picture

Private Sub ST___Mon_Exit(Cancel As Integer)
On Error GoTo Err_ST__Mon_Exit

Me.Refresh

  Dim strEmployeeName As String
  Dim strMonST As Integer
  Dim strTotalMonHrs As Integer
  Dim strMonOT As Integer
  Dim strMonDT As Integer
  Dim strTotalHrs As Integer
  Dim strCode As String
  Dim strWeekday As String
  Dim strWEID As String
  Dim strWEIDEmployeeName As String
  Dim strWEIDCode As String
  Dim strWeekEnding As String
  Dim strProjectName As String
  Dim rstTblTimeSheetsDailyLog As DAO.Recordset
  Dim rstTblTimeSheetsDailyLogEmployees As DAO.Recordset
  Dim rstTblTimeSheetsDailyLogNotes As DAO.Recordset
  
  strMonST = Nz(Forms![FrmTimeSheets]![FrmTimeSheetsDetails]![MonST], 0)
  strMonOT = Nz(Forms![FrmTimeSheets]![FrmTimeSheetsDetails]![MonOT], 0)
  strMonDT = Nz(Forms![FrmTimeSheets]![FrmTimeSheetsDetails]![MonDT], 0)
  strTotalHrs = (strMonST + strMonOT + strMonDT)
  strWeekday = "Monday"
  StrDash = "-"
  strWeekEnding = Forms![FrmTimeSheets]![WeekEndingCombo]
  strProjectName = Forms![FrmTimeSheets]![FrmTimeSheetsDetails]![Project Name]
  strCode = Forms![FrmTimeSheets]![FrmTimeSheetsDetails]![Code]
  strEmployeeName = Forms![FrmTimeSheets]![FrmTimeSheetsDetails]![EmployeeName]
  strWEID = strProjectName & StrDash & strWeekEnding & StrDash & strWeekday
  strWEIDEmployeeName = strProjectName & StrDash & strCode & StrDash & strWeekEnding & StrDash & strWeekday & StrDash & strEmployeeName
  strWEIDCode = strProjectName & StrDash & strCode & StrDash & strWeekEnding & StrDash & strWeekday
  strTotalMonHrs = DCount("[WEID]", "TblTimeSheetsDailyLogEmployees", [WEID] = strWEIDCode)
  
'Add WEID, Weekday, Week Ending, Employee Name, Code, and Project Name In TblTimeSheetsDailyLogEmployees

If strTotalHrs = 0 Then Exit Sub

Set rstTblTimeSheetsDailyLogEmployees = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogEmployees", dbOpenDynaset)

With rstTblTimeSheetsDailyLogEmployees
  .FindFirst "[WEIDEmployeeName]='" & strWEIDEmployeeName & "'"
  If .NoMatch Then .AddNew Else .Edit
  ![WEIDEmployeeName] = strWEIDEmployeeName
  ![WEID] = strWEIDCode
  ![Project Name] = strProjectName
  ![Employees] = strEmployeeName
  ![Code] = strCode
  ![Weekday] = strWeekday
  ![WeekEnding] = strWeekEnding
  ![TotalSTHrs] = strMonST
  ![TotalOTHrs] = strMonOT
  ![TotalDTHrs] = strMonDT
  ![TotalHrs] = strTotalHrs
  .Update
  End With
  
Me.Refresh
    
'Add WEID, Weekday, Week Ending, and Project Name In TblTimeSheetsDailyLogNotes

Set rstTblTimeSheetsDailyLogNotes = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogNotes", dbOpenDynaset)
        
With rstTblTimeSheetsDailyLogNotes
  .FindFirst "[WEID]='" & strWEIDCode & "'"
  If .NoMatch Then .AddNew Else .Edit
  ![WEID] = strWEIDCode
  ![Project Name] = strProjectName
  ![Weekday] = strWeekday
  ![Code] = strCode
  ![WeekEnding] = strWeekEnding
  ![TotalHrs] = strTotalMonHrs
  .Update
  
  End With

'Add WEID, Weekday, Week Ending, and Project Name In TblTimeSheetsDailyLog

Set rstTblTimeSheetsDailyLog = CurrentDb.OpenRecordset("TblTimeSheetsDailyLog", dbOpenDynaset)
        
With rstTblTimeSheetsDailyLog
  .FindFirst "[WEID]='" & strWEID & "'"
  If .NoMatch Then .AddNew Else .Edit
  ![WEID] = strWEID
  ![Project Name] = strProjectName
  ![Weekday] = strWeekday
  ![WeekEnding] = strWeekEnding
  ![TotalHours] = strTotalMonHrs
  .Update

  End With
   
Err_ST__Mon_Exit:
    Exit Sub

End Sub

Please ask if there is any additional information that I need to post.

Again, your help is appreciated.

 

[TheAceMan1]

How are ya Johnnycat1 . . .

Try the following:

[blue]Change: strTotalMonHrs = DCount("[WEID]", "TblTimeSheetsDailyLogEmployees", [WEID] = strWEIDCode)
To    : strTotalMonHrs = DCount("[WEID]", "TblTimeSheetsDailyLogEmployees", [purple][b]"[WEID] = " & strWEIDCode[/b][/purple])[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

And for safety purpose, replace this:
.FindFirst "[WEIDEmployeeName]='" & strWEIDEmployeeName & "'"
with this:

.FindFirst "WEIDEmployeeName='" & Replace(strWEIDEmployeeName, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Johnnycat1]

AceMan & PH,

Thanks for responding!

AceMan,

I replaced the code you suggested and it now gives a different error.

Now the "Runtime error 3075. Syntax error (missing operator) in Query Expression '[WEID] = Alder Construction Yard Lights-L-9/12/2010-Monday'

It is recognizing the strWEIDCode but I can't see that there is a missing operator.

Any suggestions?

 

[PHV]

Replace this:
strTotalMonHrs = DCount("[WEID]", "TblTimeSheetsDailyLogEmployees", [WEID] =" & strWEIDCode)
with this:

strTotalMonHrs = DCount("WEID", "TblTimeSheetsDailyLogEmployees", WEID='" & strWEIDCode & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Johnnycat1]

PH,

That was the trick...

Just one question. if strWEIDCode is already a string value, why does it also require the apostrophes? Don't the apostrophes just indicate a text value?

Thanks again for your valuable post!

 

[TheAceMan1]

Johnnycat1 . . .

As shown by [blue]PHV[/blue] I forgot the two apostrophe's:

[blue]strTotalMonHrs = DCount("WEID", "TblTimeSheetsDailyLogEmployees", WEID=[red][b]'[/b][/red]" & strWEIDCode & "[red][b]'[/b][/red]")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[randy700]

if strWEIDCode is already a string value, why does it also require the apostrophes
The function requires the entire WHERE clause to be entered as a string. The apostropes allow you to enter it with a string value within another string value.


Randy

 

[Johnnycat1]

Thanks Randy