Date problem when using Runtime

[billsMBS]

We have been using an Access front end (97) to use linked SQL 6.5 db. We recently upgraded to Access2K and SQL2K. Everything compiled correctly, after some mods. The code works fine when it is ran on a computer with Office 2K loaded. When wanted to create a runtime version, so we ran through the Package and Deployement wizard. In one of the modules we have some code based on dates. This is shown below.

s = "SELECT PatID FROM tblInvDetail WHERE ARInvID = 0 AND PatID IS NOT Null "
s = s & "AND FromDt >= #" & CStr(Forms!frmGlobalVars!StartDt) & "# "
s = s & &quot;AND FromDt <= #&quot; & CStr(Forms!frmGlobalVars!EndDt) & &quot;# &quot;
s = s & &quot;;&quot;

---FromDt is a DateTime in SQL db.

I get an error in the Runtime version. The only indication I get is that &quot;MSACCESS.exe has generated an error and will be closed by Windows&quot;.

If I remove the # sign and replace with a single ', I don't get the error, but I don't get the data back either!

Any help would be appreciated.

 

[Durkin]

I've had the same error from Access runtime happen before. It seems to just happen on some unhandled errors, not all of them. I've always been able to recreate the error on a machine with a full version of access and it's always been something fixable. Looking at your SQL statement I'd say the real error being hidden by the runtime error message is a datatype mismatch error caused by using # to denote your dates. One thing that springs to mind is to use the format function. Try using
Format(Forms!frmGlobalVars!StartDt,&quot;Medium Date&quot
or Format(Forms!frmGlobalVars!StartDt,&quot;yyyy-mm-dd&quot
with the single quotes instead of
CStr(Forms!frmGlobalVars!StartDt)

Durkin
alandurkin@bigpond.com