I recently converted from text logs being imported to an access database table
called call-logs to radius accounting which of course puts the date into
"Calls". The text logs had date and time in separate fields and lookups were
fast. Radius accounting has date and time together. The SQL statement I'm
using gives me a mismatch error if the field type for CallDate in the Calls
table is set to "Date" I use asp code for all customer account maintenance
and have made sure the datestart and dateend are date types. If CallDate is
set to text it works but VERY, VERY slow as compared to when date and time
were is separate fields. I've already isolated other lookups and verified it
is the date parameter causing the problems.
sql="SELECT Sum([AcctSessionTime]) AS [SumDuration] FROM [calls] WHERE
UserName='"_
& username & "' AND CallDate >= '" & datestart & "' AND callDate <= '"
&_
dateend & "'"
Even this simpler statement give the mismatch error:
sql="SELECT Sum([AcctSessionTime]) AS [SumDuration] FROM [Calls] WHERE
CallDate >= '" & datestart & "'"
examples of datestart and dateend are as follows"
datestart = "2000-03-01"
dateend = "2000-03-31"
I know CallDate should be a date type but if it is set to that, searches via
dates give the " Data type mismatch in criteria expression" message. I know,
I know upgrade to SQL 7 but haven't been able to do that yet. Short of writing
a script to convert the radius accounting records to the old table for
customer and internal accounting usage.
I have the CallDate type set to text in ServerPorts which is probably why it
doesn't get updated as well. Any assistance would be appreciated.
--Mike Kovacich
--------------DAA41EC025E14457F89ADD16Content-Type: text/html; charset=us-asciiContent-Transfer-Encoding: 7bit
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">I recently converted from text logs being imported to an access databasetable called call-logs to radius accounting which of course puts the dateinto "Calls". The text logs had date and time in separate fields and lookupswere fast. Radius accounting has date and time together. The SQL statementI'm using gives me a mismatch error if the field type for CallDate in theCalls table is set to "Date" I use asp code for all customer accountmaintenance and have made sure the datestart and dateend are date types.If CallDate is set to text it works but VERY, VERY slow as compared towhen date and time were is separate fields. I've already isolated otherlookups and verified it is the date parameter causing the problems.
sql="SELECT Sum([AcctSessionTime]) AS [SumDuration]FROM [calls] WHERE UserName='"_
& username &"' AND CallDate >= '" & datestart & "' AND callDate <= '" &_
dateend & "'"Even this simpler statement give the mismatch error:
sql="SELECT Sum([AcctSessionTime]) AS [SumDuration] FROM[Calls] WHERE CallDate >= '" & datestart & "'"examples of datestart and dateend are as follows"
datestart = "2000-03-01"
dateend = "2000-03-31"I know CallDate should be a date type but if it is set to that, searchesvia dates give the " Data type mismatch in criteria expression" message.I know, I know upgrade to SQL 7 but haven't been able to do that yet. Shortof writing a script to convert the radius accounting records to the oldtable for customer and internal accounting usage.
I have the CallDate type set to text in ServerPorts which is probablywhy it doesn't get updated as well. Any assistance would be appreciated.
--
Mike Kovacich
--------------DAA41EC025E14457F89ADD16--