I am in the process of transfering out database from an Access backend to an SQL backend linked to an access front end.
All is going well apart from 1 problem, i have this code =DCount("*","dashpro ","(Date()-[Date recorded])<8") linked over about 40 text boxes changing slightly on each txt box. It used to work great on the access backend, however since moving to sql it just comes up with #Error there are some boxes that where a bit different and have this code =DCount("*","dashquery"), they all work properly.
because of this it lead me to believe that the dates are calculated differently through SQL, so in the access query that pulls the data from the sql databases i changed the date to be filled as so
Expr1: Format(dbo_tblcustomerdeta
ils.[Date Recorded],"dd/mm/yyyy")
this shows fine in the query view, but when i load the text boxes they just flash quickly with the #Error message.
The actual date in the SQL table is stores as nvarchar(50), when i try to change it to DATETIME i get the error
'tblcustomerdetails' table
- Unable to modify table.
Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated.
I believe this is because the data is stored in an english format (although as text) eg 24/03/08
any help with this problem would be greatly appreciated.
Start Free Trial
