I Can't seem to get the Forms!
Dashboard![BeginDate] part to work. Any thoughts? Thanks!
Set rsSendAccountTransfersToContracts = _
db.OpenRecordset("SELECT * " & _
"FROM [tblAccountTransfers] " & _
"WHERE [EffectiveDate] = '" & _
Forms!Dashboard![BeginDate] & "'")
Problem 1 - Assumign that BeginDate is a text box containing a date, you
need to be a lot more defensive about typing. At the moment you are sending
this value to the db engine (including the quotes):-
'14 Mar 2003'
or whatever the user happenned to type in. You can check with help on
Literal Dates in SQL, but in brief there are very few formats that are
legal. Here is one:
#2003-03-14#
The last line of the query should therefore go
... "WHERE EffectiveDate = " & Format (BeginDate,"\#yyyy\-mm\-dd\#")
Problem 2:- Always specify what type of recordset you want to open.
Snapshots are much faster than dynasets if you don't want to update them,
and also don't lock out other users. Similarly ForwardOnly takes less
memory and keeps your computer and the network happy.
By the way, a good tip when creating SQL on the fly is to put it in a
separate string, and then debug it:
strSQL = "SELECT * " & vbNewLine & _
"FROM tblAccountTranfers" & vbNewLine & _
"WHERE etc...;"
MsgBox strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
Remove the MsgBox when you have thoroughly tested all the possible
scenarios.
Hope that helps
Tim F
