Help With Variable Code Mistake

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

The below code errors out and highlights the MenWorked on the first Line
and says Variable not defined
What can I do?



MenWorked = DCount("[EmployeeID]", "Employees", "[Work Date] = " & WorkDate)
If MenWorked < 3 Then ' Two or fewer men worked so
Rate = 65 ' Rate is $65.00
Else
ExtraMen = MenWorked - 2 ' Count the extra men
Rate = 65 + (ExtraMen * 32.5) ' Start with the orignina $65.00 and add
End If ' Multiply the number of extra men by
' $32.50
 
Suggestions:
1. Declare the MenWorked variable.
2. Check that WorkDate has a date value (not Null).
3. Delimit the literal date with # in the string.
4. Use Nz() convert a null return value to zero.

This kind of thing:
Dim MenWorked As Long
Dim strWhere As String
If IsDate([WorkDate]) Then
strWhere = "[Work Date] = " & Format(WorkDate, "\#mm\/dd\/yyyy\#")
MenWorked = Nz(DCount("[EmployeeID]", "Employees", strWhere),0)
If MenWorked < 3 Then ...
 
This part of my code is highlighted in Yellow and so does not work, what can
I do ?

ExtraMen = MenWorked - 2
["Rate"] = 65 + ["ExtraMen"] * 32.5




Allen Browne said:
Suggestions:
1. Declare the MenWorked variable.
2. Check that WorkDate has a date value (not Null).
3. Delimit the literal date with # in the string.
4. Use Nz() convert a null return value to zero.

This kind of thing:
Dim MenWorked As Long
Dim strWhere As String
If IsDate([WorkDate]) Then
strWhere = "[Work Date] = " & Format(WorkDate, "\#mm\/dd\/yyyy\#")
MenWorked = Nz(DCount("[EmployeeID]", "Employees", strWhere),0)
If MenWorked < 3 Then ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
The below code errors out and highlights the MenWorked on the first Line
and says Variable not defined
What can I do?



MenWorked = DCount("[EmployeeID]", "Employees", "[Work Date] = " &
WorkDate)
If MenWorked < 3 Then ' Two or fewer men worked so
Rate = 65 ' Rate is $65.00
Else
ExtraMen = MenWorked - 2 ' Count the extra men
Rate = 65 + (ExtraMen * 32.5) ' Start with the orignina $65.00 and add
End If ' Multiply the number of extra men
by
' $32.50
Click to expand...
Click to expand...
 
We dont' know what Rate is, or ExtraMen.
Variables? Text boxes?
Could their value be Null?
Why do they have quote marks within the square brackets?

If they are text boxes, try:
Me.Rate = 65 + Nz(Me.ExtraMen,0) * 32.5

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
This part of my code is highlighted in Yellow and so does not work, what
can I do ?

ExtraMen = MenWorked - 2
["Rate"] = 65 + ["ExtraMen"] * 32.5




Allen Browne said:
Suggestions:
1. Declare the MenWorked variable.
2. Check that WorkDate has a date value (not Null).
3. Delimit the literal date with # in the string.
4. Use Nz() convert a null return value to zero.

This kind of thing:
Dim MenWorked As Long
Dim strWhere As String
If IsDate([WorkDate]) Then
strWhere = "[Work Date] = " & Format(WorkDate, "\#mm\/dd\/yyyy\#")
MenWorked = Nz(DCount("[EmployeeID]", "Employees", strWhere),0)
If MenWorked < 3 Then ...


Dave Elliott said:
The below code errors out and highlights the MenWorked on the first
Line and says Variable not defined
What can I do?



MenWorked = DCount("[EmployeeID]", "Employees", "[Work Date] = " &
WorkDate)
If MenWorked < 3 Then ' Two or fewer men worked so
Rate = 65 ' Rate is $65.00
Else
ExtraMen = MenWorked - 2 ' Count the extra men
Rate = 65 + (ExtraMen * 32.5) ' Start with the orignina $65.00 and
add
End If ' Multiply the number of extra men
by
' $32.50
Click to expand...
Click to expand...
Click to expand...
 
The below code shows me the number of times A employees is on the same
record
What I really need is to know how many employees worked on the same day and
how many days this occured
this may show up more than once per record on
Example: Forest Gump, Bubba Gump and Leroy Brown all work on Thursday
04/21/05
But on Wednesday 03/16/05 Forest worked by himself
I have a combox box on my form named Employee Time with its control source
set to EmployeeID
it looks up the employee from the employee table
also on the form I have a field named WorkDate with its control source set
to Work Date (Note Spaces)
also a textbox named Text34 which =Format([Work Date],"dddd") 'This shows
the day work was done by week day

Thank You So Much For Trying To Help, This code is for a Very difficult
calculation

MenWorked and ExtraMen are both textboxes
Very hard to explain this without seeing it.





Allen Browne said:
We dont' know what Rate is, or ExtraMen.
Variables? Text boxes?
Could their value be Null?
Why do they have quote marks within the square brackets?

If they are text boxes, try:
Me.Rate = 65 + Nz(Me.ExtraMen,0) * 32.5

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
This part of my code is highlighted in Yellow and so does not work, what
can I do ?

ExtraMen = MenWorked - 2
["Rate"] = 65 + ["ExtraMen"] * 32.5




Allen Browne said:
Suggestions:
1. Declare the MenWorked variable.
2. Check that WorkDate has a date value (not Null).
3. Delimit the literal date with # in the string.
4. Use Nz() convert a null return value to zero.

This kind of thing:
Dim MenWorked As Long
Dim strWhere As String
If IsDate([WorkDate]) Then
strWhere = "[Work Date] = " & Format(WorkDate,
"\#mm\/dd\/yyyy\#")
MenWorked = Nz(DCount("[EmployeeID]", "Employees", strWhere),0)
If MenWorked < 3 Then ...


The below code errors out and highlights the MenWorked on the first
Line and says Variable not defined
What can I do?



MenWorked = DCount("[EmployeeID]", "Employees", "[Work Date] = " &
WorkDate)
If MenWorked < 3 Then ' Two or fewer men worked so
Rate = 65 ' Rate is $65.00
Else
ExtraMen = MenWorked - 2 ' Count the extra men
Rate = 65 + (ExtraMen * 32.5) ' Start with the orignina $65.00 and
add
End If ' Multiply the number of extra
men by
' $32.50
Click to expand...
Click to expand...
Click to expand...
 
I don't think the approach you have will give you the detail you are after,
and I suspect there are several missing pieces of the puzzle here. For
example, it seems likely that some people worked on one project, and others
on another, on the same day.

The question is therefore bigger than can be solved in a newsgroup for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
The below code shows me the number of times A employees is on the same
record
What I really need is to know how many employees worked on the same day
and
how many days this occured
this may show up more than once per record on
Example: Forest Gump, Bubba Gump and Leroy Brown all work on Thursday
04/21/05
But on Wednesday 03/16/05 Forest worked by himself
I have a combox box on my form named Employee Time with its control source
set to EmployeeID
it looks up the employee from the employee table
also on the form I have a field named WorkDate with its control source set
to Work Date (Note Spaces)
also a textbox named Text34 which =Format([Work Date],"dddd") 'This
shows
the day work was done by week day

Thank You So Much For Trying To Help, This code is for a Very difficult
calculation

MenWorked and ExtraMen are both textboxes
Very hard to explain this without seeing it.


Allen Browne said:
We dont' know what Rate is, or ExtraMen.
Variables? Text boxes?
Could their value be Null?
Why do they have quote marks within the square brackets?

If they are text boxes, try:
Me.Rate = 65 + Nz(Me.ExtraMen,0) * 32.5

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
This part of my code is highlighted in Yellow and so does not work, what
can I do ?

ExtraMen = MenWorked - 2
["Rate"] = 65 + ["ExtraMen"] * 32.5




Suggestions:
1. Declare the MenWorked variable.
2. Check that WorkDate has a date value (not Null).
3. Delimit the literal date with # in the string.
4. Use Nz() convert a null return value to zero.

This kind of thing:
Dim MenWorked As Long
Dim strWhere As String
If IsDate([WorkDate]) Then
strWhere = "[Work Date] = " & Format(WorkDate,
"\#mm\/dd\/yyyy\#")
MenWorked = Nz(DCount("[EmployeeID]", "Employees", strWhere),0)
If MenWorked < 3 Then ...


The below code errors out and highlights the MenWorked on the first
Line and says Variable not defined
What can I do?



MenWorked = DCount("[EmployeeID]", "Employees", "[Work Date] = " &
WorkDate)
If MenWorked < 3 Then ' Two or fewer men worked so
Rate = 65 ' Rate is $65.00
Else
ExtraMen = MenWorked - 2 ' Count the extra men
Rate = 65 + (ExtraMen * 32.5) ' Start with the orignina $65.00 and
add
End If ' Multiply the number of extra
men by
' $32.50
Click to expand...
Click to expand...
Click to expand...
 
All people are on the same job or record via the main form.

Thanks, Anyway


Allen Browne said:
I don't think the approach you have will give you the detail you are after,
and I suspect there are several missing pieces of the puzzle here. For
example, it seems likely that some people worked on one project, and others
on another, on the same day.

The question is therefore bigger than can be solved in a newsgroup for
you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
The below code shows me the number of times A employees is on the same
record
What I really need is to know how many employees worked on the same day
and
how many days this occured
this may show up more than once per record on
Example: Forest Gump, Bubba Gump and Leroy Brown all work on Thursday
04/21/05
But on Wednesday 03/16/05 Forest worked by himself
I have a combox box on my form named Employee Time with its control
source
set to EmployeeID
it looks up the employee from the employee table
also on the form I have a field named WorkDate with its control source
set
to Work Date (Note Spaces)
also a textbox named Text34 which =Format([Work Date],"dddd") 'This
shows
the day work was done by week day

Thank You So Much For Trying To Help, This code is for a Very difficult
calculation

MenWorked and ExtraMen are both textboxes
Very hard to explain this without seeing it.


Allen Browne said:
We dont' know what Rate is, or ExtraMen.
Variables? Text boxes?
Could their value be Null?
Why do they have quote marks within the square brackets?

If they are text boxes, try:
Me.Rate = 65 + Nz(Me.ExtraMen,0) * 32.5

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This part of my code is highlighted in Yellow and so does not work,
what can I do ?

ExtraMen = MenWorked - 2
["Rate"] = 65 + ["ExtraMen"] * 32.5




Suggestions:
1. Declare the MenWorked variable.
2. Check that WorkDate has a date value (not Null).
3. Delimit the literal date with # in the string.
4. Use Nz() convert a null return value to zero.

This kind of thing:
Dim MenWorked As Long
Dim strWhere As String
If IsDate([WorkDate]) Then
strWhere = "[Work Date] = " & Format(WorkDate,
"\#mm\/dd\/yyyy\#")
MenWorked = Nz(DCount("[EmployeeID]", "Employees", strWhere),0)
If MenWorked < 3 Then ...


The below code errors out and highlights the MenWorked on the first
Line and says Variable not defined
What can I do?



MenWorked = DCount("[EmployeeID]", "Employees", "[Work Date] = " &
WorkDate)
If MenWorked < 3 Then ' Two or fewer men worked so
Rate = 65 ' Rate is $65.00
Else
ExtraMen = MenWorked - 2 ' Count the extra men
Rate = 65 + (ExtraMen * 32.5) ' Start with the orignina $65.00 and
add
End If ' Multiply the number of extra
men by
' $32.50
Click to expand...
Click to expand...
Click to expand...
 
Back
Top