Microsoft Access - Error while trying to retrive value from database

Asked By Nami123
10-Feb-12 07:14 AM
 sir ,
while trying to retrive data from database an error is coming . The error is like this:

Data type mismatch in criteria expression.

 the query i have given is
SELECT Timestamp , Milliseconds,Value,Quality FROM Ramp1_2012_02_08_08_46_58_656_AM WHERE (TimeStamp BETWEEN '02-10-2012 10:05:46 AM' AND '02-10-2012 10:08:46 AM')

Please help me to solve this issue.Iam new to MsAccess
Thanks&Regards
Nami
  Dilip Sharma replied to Nami123
10-Feb-12 07:22 AM
u checkd this query is running in ms access ?
  Nami123 replied to Dilip Sharma
10-Feb-12 07:28 AM
 yes i checked in msaccess also .But there also showing same error
  Dilip Sharma replied to Nami123
10-Feb-12 07:37 AM
u have to convert varchar to date time

exmple

select * from dbo.TB_Natwest_Bank where Date between
convert(datetime,'2011-02-28') and 
convert(datetime,'2010-01-10')

u can check diffrent conversion method here

http://www.sqlusa.com/bestpractices/datetimeconversion/ 




  kalpana aparnathi replied to Nami123
10-Feb-12 02:19 PM
hi,

Cause:

The conditions that cause this error are as follows:
  • Using an Access data source. This behavior does not occur against a SQL data source.
  • Search criteria on a Date/Time Data type field.
  • User enters the value of the search criteria in the form of 'mm/dd/yy'
Solution:

There are two situations where you need to apply a workaround to this behavior:
  • When executing this type of query in the Query Designer, such as in testing the query before saving the Active Server Pages (ASP) script, it is best to hard code a test date in the criteria field in the "Grid Pane" and then run the query (see step 7 under Steps to Reproduce Behavior" in the MORE INFORMATION section below).
  • When executing this type of query in the ASP script, you must change the code that the Design-Time Control outputs to one of the below formats:
more detail:click here
  Pat Hartman replied to Nami123
10-Feb-12 05:51 PM
Strings are delimited with single or double quotes.  Dates are delimited with pound signs (#), and numeric values are not delimited.
Create New Account
help
Error Error: "Type DATE is not a defined system type." DateTime date = new DateTime (); System.Globalization. CultureInfo Cul = new System.Globalization. CultureInfo ( "en-GB" , true ); date = DateTime .Parse(txtdate.Text.Trim(), Cul, System.Globalization. DateTimeStyles .NoCurrentDateDefault); / / cmd = new SqlCommand("select count (empname) from tbl_sales where date date.ToShortDateString()+ "'", con); cmd = new SqlCommand ( "select count (empname) from tbl_sales where MONTH(date) = MONTH(CAST('" + date.ToShortDateString() + "' as DATE))" , con); int j = ( int )cmd.ExecuteScalar(); if (j = = 0
retrieve error value from SP Hello, I would like to be able to retrieve the @@error value from a stored procedure. I am usinf SQL2000 and vb6 Here is the SP procedure invCreateInvheadRecord - --inserts information into invhead for newly created invoices @varinvno as float, @varorderno as varchar(9), @varsorderno as varchar(9), @varodate as datetime, @varsdate as datetime, @varinvdate as datetime, @varcgpsno as varchar(6), @varcustno as float, @varcustna as varchar(30), @varcorderno as varchar(30), @varostatus as varchar(1), @varsperson1 as varchar(3), @varsname1 as varchar(20), @varsperson2 as varchar(20), @varsname2 as
Stored Procedure coding error SQL Server I am trying to create a temporary table TblTEMP_CRA on an as-needed include modified data from a table TblHISTORY_CRA, querying by user entered dates for the start date and end date. I also need an identity column that starts at 1 every time. The code below keeps saying there is a syntax error near my SELECT statement. . . CREATE PROCEDURE sp_CRA_Calculations @start_date smalldatetime, @end_date smalldatetime AS DROP TABLE TblTEMP_CRA GO CREATE TABLE TblTEMP_CRA (Autonum int identity (1, 1), Type varchar (50) NULL, Check_Name varchar (50) NULL, VendCode varchar (50) NULL, VendInd varchar (50) NULL, VendTIN varchar (50) NULL, TINType varchar (50) NULL, Address1 varchar (50) NULL, Address2
How could I use Group By and order by in sql stored procedure select convert ( varchar , date , 101 ) as date , Pmt , from v_ViewNew WHERE Convert ( varchar , date , 101 )> = @FDate AND Convert ( varchar , date , 101 )< = @ToDate AND ID = 2 group by convert ( varchar , date , 101 ), Pmt * **order by date desc The * ** statement was added when the new year
modify qiery datatable attID int BID int Bcode varchar Name varchar Date datetime Ptpe varchar i want to display only bcode, name, date, ptype SET @query = ' select bcode, name, date, ptype from tbl_K_Attendance this is not working plz help me = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = DECLARE @columns VARCHAR ( 8000 ) SELECT @columns = COALESCE (@columns + ' , [' + cast date as varchar ( 11 )) + ' ]' , ' [' + cast(date as varchar ( 11 ))+ ' ]' ) FROM tbl_K_Attendance GROUP BY date DECLARE @query