in code query

  • Thread starter Thread starter doug
  • Start date Start date
D

doug

i am running a query in access 97 that runs on the click
event of a button. The code is a select sum of a field
for certain criteria...the sql line looks fine but when i
call the recordset i get an error. can anyone direct me
how to run a query like that in code. my current code
look something like this:


strSQL = "SELECT Sum(Time_Log_table.Time) AS Expr1 FROM
Time_Log_table WHERE ((Time_Log_table.[Task Date])= """ &
[Forms]![time_entry_form]![calendar].[Value] & """ AND
(Time_Log_table.[Assoc Number]=""" & [Forms]!
[time_entry_form]![txtAssocNumber].[Value] & """))"

Set rst = dbs.OpenRecordset(strSQL)
If Not rst.EOF And Not rst.BOF Then
txtDailyHours.Text = rst!expr1
End If

thanks for any help
 
Doug,

Can I ask you a few questions first...
- Is this code being called from an event on the time_entry_form form,
or from somewhere else?
- Is the Task Date field a Date/Time data type, or Text, or what?
- Is the Assoc Number field a Number data type, or Text, or what?
- Is txtDailyHours the name of a control on the time_entry_form form,
or somewhere else?
- What is the error message you have received?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this SQL:

strSQL = "SELECT Sum([Time]) AS SumOfTime " & _
"FROM Time_Log_table " & _
"WHERE [Task Date]= #" & _
[Forms]![time_entry_form]![calendar]"# AND " & _
[Assoc Number]= '" & _
[Forms]![time_entry_form]![txtAssocNumber] & "'"

Set rst = dbs.OpenRecordset(strSQL)
If Not rst.EOF Then Me!txtDailyHours = rst!expr1

To delimite dates (USA format) you have to put # at the beginning and
end of the date.

You don't need the .Value property of the referenced control, it is the
default property.

If the control is on the same form as the VBA code you can shorten the
control reference by removing Froms!FormName!ControlName and using
Me!ControlName, instead.

You should not use "Time" as a column name, 'cuz it is also a VBA
function - Time().

If your column "Time" is holding a time value, summing it will be pretty
meaningless, 'cuz a time value indicates the number of milliseconds
since midnight of whichever day the time value was created.

In Access VBA:
Don't assign a value to a control using the .Text property unless the
focus is on that control. Anyway, the correct property to use in
assignments is the .Value property, and .Value is the default property
of controls; therefore, you don't even need to indicate that property
during assignment.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSZ3Z4echKqOuFEgEQJn2ACfZm0CFaGaitKnv9YlvxZ9xDnR3RgAnjXc
tRy7ezR3oviJHUnmOY70i70S
=l5dh
-----END PGP SIGNATURE-----
 
Back
Top