Problem with EVAL

[Antonio via AccessMonster.com]

Hi,

I'm trying to get a piece of code to wotk neat, using the Eval function, to
avoid writing a bunch of redundant code.

Note that in the code below, StrEval should always contain a double, and in
most cases the value 8. However, this way, it always returns 0...

'-----------------------------------------------------------------------------
--------------------------
Dim MyWeek: MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
Dim i as Integer

(...)

For i = 0 To 6
StrEval = "TimeCardDetailRcdst![" & MyWeek(i) & "]"
If Eval(StrEval) > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst!
[AccountGroupCost]
'Instead of ActualRcdst![Hours] = TimeCardDetailRcdst![Mon]
lets try..
ActualRcdst![Hours] = Eval(StrEval)
(...)
ActualRcdst.Update
End If
Next
'-----------------------------------------------------------------------------
--------------------------

I can't seem to get Eval to execute the String like a line of code. I've also
tried to use Val and CDbl, but the value supposed to return is always 0...

Anyone?

Thanks
Antonio

 

[Douglas J Steele]

I'm assuming you've got textboxes on your form named Mon, Tue, etc. Rather
than using Eval, why not create a reference to the textbox itself?

Dim ctlCurr As Control

For i = 0 To 6
Set ctlCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If ctlCurr > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = ctlCurr
(...)
ActualRcdst.Update
End If
Next

Are your textboxes bound or unbound? If they're bound, is the field to which
they're bound named the same as the textbox? If so, I'd advise renaming your
textboxes txtMon, txtTue and so on, and using those names in your array,
just to make sure you're referring to the textbox, not the recordset field.

 

[Antonio via AccessMonster.com]

Hi,
Mon, Tue, We, .... are fields in the database, hence available in
TimeCardDetailRcdst.
This RecordSet has various fields, resulting from a Select Query, in which
Mon, Tue, Wed, .....
It isn't possible to reference .Control through the Recordset...
Any idea?
Thanks
Antonio

Douglas J Steele wrote:
I'm assuming you've got textboxes on your form named Mon, Tue, etc. Rather
than using Eval, why not create a reference to the textbox itself?

Dim ctlCurr As Control

For i = 0 To 6
Set ctlCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If ctlCurr > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = ctlCurr
(...)
ActualRcdst.Update
End If
Next

Are your textboxes bound or unbound? If they're bound, is the field to which
they're bound named the same as the textbox? If so, I'd advise renaming your
textboxes txtMon, txtTue and so on, and using those names in your array,
just to make sure you're referring to the textbox, not the recordset field.

Hi,

[quoted text clipped - 3 lines]

Note that in the code below, StrEval should always contain a double, and in
most cases the value 8. However, this way, it always returns 0...
'---------------------------------------------------------------------------

[quoted text clipped - 15 lines]

End If
Next
'---------------------------------------------------------------------------

[quoted text clipped - 5 lines]

Thanks
Antonio

 

[Douglas J Steele]

Sorry: I guess I didn't read closely enough, so I though TimeCardDetailRcdst
was a form, not a recordset. For recordsets, you use Field, not Control.

Dim fldCurr As DAO.Field

For i = 0 To 6
Set fldCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If fldCurr.Value > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = fldCurr.Value
(...)
ActualRcdst.Update
End If
Next

If TimeCardDetailRcdst is an ADO recordset, change the declaration to

Dim fldCurr As ADODB.Field


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
Mon, Tue, We, .... are fields in the database, hence available in
TimeCardDetailRcdst.
This RecordSet has various fields, resulting from a Select Query, in which
Mon, Tue, Wed, .....
It isn't possible to reference .Control through the Recordset...
Any idea?
Thanks
Antonio

Douglas J Steele wrote:
I'm assuming you've got textboxes on your form named Mon, Tue, etc. Rather
than using Eval, why not create a reference to the textbox itself?

Dim ctlCurr As Control

For i = 0 To 6
Set ctlCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If ctlCurr > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = ctlCurr
(...)
ActualRcdst.Update
End If
Next

Are your textboxes bound or unbound? If they're bound, is the field to which
they're bound named the same as the textbox? If so, I'd advise renaming your
textboxes txtMon, txtTue and so on, and using those names in your array,
just to make sure you're referring to the textbox, not the recordset field.

Hi,

[quoted text clipped - 3 lines]

Note that in the code below, StrEval should always contain a double, and in
most cases the value 8. However, this way, it always returns 0...

'-------------------------------------------------------------------------- - "Sun")
[quoted text clipped - 15 lines]

End If
Next

'-------------------------------------------------------------------------- -[quoted text clipped - 5 lines]

Thanks
Antonio

 

[Douglas J Steele]

BTW, is there some reason why your recordset is denormalized?

If you had one row for each day, rather than fields across the row, your
code would be much simpler.

You can take the existing datasource and normalize it using a UNION query:

SELECT Field1, Field2, "Mon" AS Weekday, Mon AS Hours
FROM MyTable
WHERE Mon <> 0
UNION
SELECT Field1, Field2, "Tue" AS Weekday, Tue AS Hours
FROM MyTable
WHERE Tue <> 0
UNION
SELECT Field1, Field2, "Wed" AS Weekday, Wed AS Hours
FROM MyTable
WHERE Wed <> 0
UNION
SELECT Field1, Field2, "Thu" AS Weekday, Thu AS Hours
FROM MyTable
WHERE Thu <> 0
UNION
SELECT Field1, Field2, "Fri" AS Weekday, Fri AS Hours
FROM MyTable
WHERE Fri <> 0
UNION
SELECT Field1, Field2, "Sat" AS Weekday, Sat AS Hours
FROM MyTable
WHERE Sat <> 0
UNION
SELECT Field1, Field2, "Sun" AS Weekday, Sun AS Hours
FROM MyTable
WHERE Sun <> 0

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry: I guess I didn't read closely enough, so I though TimeCardDetailRcdst
was a form, not a recordset. For recordsets, you use Field, not Control.

Dim fldCurr As DAO.Field

For i = 0 To 6
Set fldCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If fldCurr.Value > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = fldCurr.Value
(...)
ActualRcdst.Update
End If
Next

If TimeCardDetailRcdst is an ADO recordset, change the declaration to

Dim fldCurr As ADODB.Field


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
Mon, Tue, We, .... are fields in the database, hence available in
TimeCardDetailRcdst.
This RecordSet has various fields, resulting from a Select Query, in which
Mon, Tue, Wed, .....
It isn't possible to reference .Control through the Recordset...
Any idea?
Thanks
Antonio

Douglas J Steele wrote:
I'm assuming you've got textboxes on your form named Mon, Tue, etc. Rather
than using Eval, why not create a reference to the textbox itself?

Dim ctlCurr As Control

For i = 0 To 6
Set ctlCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If ctlCurr > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = ctlCurr
(...)
ActualRcdst.Update
End If
Next

Are your textboxes bound or unbound? If they're bound, is the field to which
they're bound named the same as the textbox? If so, I'd advise renaming your
textboxes txtMon, txtTue and so on, and using those names in your array,
just to make sure you're referring to the textbox, not the recordset field.

Hi,

[quoted text clipped - 3 lines]
Note that in the code below, StrEval should always contain a double, and in
most cases the value 8. However, this way, it always returns 0...

'--------------------------------------------------------------------------
-

--
--------------------------
Dim MyWeek: MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
[quoted text clipped - 15 lines]
End If
Next

'--------------------------------------------------------------------------
-

--
--------------------------

[quoted text clipped - 5 lines]
Thanks
Antonio

 

[Antonio via AccessMonster.com]

Thanks Doug, it worked just sweet!
Antonio

Douglas J Steele wrote:
Sorry: I guess I didn't read closely enough, so I though TimeCardDetailRcdst
was a form, not a recordset. For recordsets, you use Field, not Control.

Dim fldCurr As DAO.Field

For i = 0 To 6
Set fldCurr = TimeCardDetailRcdst.Controls(MyWeek(i))
If fldCurr.Value > 0 Then 'only want to keep worked hours
ActualRcdst.AddNew
ActualRcdst![AccountGroup] = TimeCardDetailRcdst![AccountGroupCost]
ActualRcdst![Hours] = fldCurr.Value
(...)
ActualRcdst.Update
End If
Next

If TimeCardDetailRcdst is an ADO recordset, change the declaration to

Dim fldCurr As ADODB.Field

Hi,
Mon, Tue, We, .... are fields in the database, hence available in

[quoted text clipped - 49 lines]

 

[Antonio via AccessMonster.com]

Thanks Doug, and you're right. This table is temporary and keeps weekly hours
for all the employees.
The routine I'm coding transfers these records to another table (called
Actual), and it does it in a normalized way, that is, onde row for each day.

Thanks again for your help
Antonio

Douglas J Steele wrote:
BTW, is there some reason why your recordset is denormalized?

If you had one row for each day, rather than fields across the row, your
code would be much simpler.

You can take the existing datasource and normalize it using a UNION query:

SELECT Field1, Field2, "Mon" AS Weekday, Mon AS Hours
FROM MyTable
WHERE Mon <> 0
UNION
SELECT Field1, Field2, "Tue" AS Weekday, Tue AS Hours
FROM MyTable
WHERE Tue <> 0
UNION
SELECT Field1, Field2, "Wed" AS Weekday, Wed AS Hours
FROM MyTable
WHERE Wed <> 0
UNION
SELECT Field1, Field2, "Thu" AS Weekday, Thu AS Hours
FROM MyTable
WHERE Thu <> 0
UNION
SELECT Field1, Field2, "Fri" AS Weekday, Fri AS Hours
FROM MyTable
WHERE Fri <> 0
UNION
SELECT Field1, Field2, "Sat" AS Weekday, Sat AS Hours
FROM MyTable
WHERE Sat <> 0
UNION
SELECT Field1, Field2, "Sun" AS Weekday, Sun AS Hours
FROM MyTable

Sorry: I guess I didn't read closely enough, so I though TimeCardDetailRcdst
was a form, not a recordset. For recordsets, you use Field, not Control.

[quoted text clipped - 78 lines]