 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2006Date Comparision
Hi, I am not getting the records matched with the entered date. Its raising an error like "No recoreds found for that date" Query: Me.RecordSource=select Distinct(MeasurementData.Inspector),MeasurementData. ReadingDate,MeasurementData.Comments FROM MeasurementData WHERE MeasurementData.ReadingDate=" & mDate Is my query wrong or something i need to do..? Can anyone help me? what is " & mDate Is mDate a field in your tables? If so, which table? maybe... WHERE MeasurementData.ReadingDate=MeasurementData.mDate  SignatureRick B > Hi, > I am not getting the records matched with the entered date. Its raising [quoted text clipped - 12 lines] > > Can anyone help me? mDate is a Date variable assigned as follows dim mDate as Date mDate=Format(inputbox("enter date;"),"mm/dd/yyyy"); what could be the problem? >what is " & mDate > [quoted text clipped - 9 lines] >> >> Can anyone help me? Date literals in Access must be in US date format or otherwise internationally unambiguous so its best to format the value of the mDate variable: Me.RecordSource="SELECT DISTINCT " & _ "Inspector, ReadingDate, Comments " & _ "FROM MeasurementData " & _ "WHERE MeasurementData.ReadingDate=#" & _ Format(mDate,"mm/dd/yyyy") & "#" Ken Sheridan Stafford, England > Hi, > I am not getting the records matched with the entered date. Its raising an [quoted text clipped - 10 lines] > > Can anyone help me? Before querying i formatted the mDate variable as u told..... But i didn't put # symbol in the query.... Is it required ? >Date literals in Access must be in US date format or otherwise >internationally unambiguous so its best to format the value of the mDate [quoted text clipped - 14 lines] >> >> Can anyone help me? Yes. The # character is the date delimiter character. When you build an SQL statement in code and concatenate a date/time value into it you need to wrap the value in # characters. This identifies it as a literal date, which, as I said, must be in a US format. This has always been the case in SQL, but strangely this requirement was only introduced in VBA in Access 95. Prior to that the local regional date format was respected in VBA. Now, however, its standardised in both VBA and SQL, so you need to do the same if building an expression as the criterion for a DLookup function for instance. If you were to omit the date delimiter characters the date would probably be interpreted as an arithmetical expression, so today, 03/04/2006 would evaluate to 0.000664672648720505. Now because Access stores date/time values as a 64 bit floating point number as an offset from 30 December 1899 00:00:00 this would then be evaluated as 30 December 1899 00:00:57, which is unlikely to match anything in the database! Ken Sheridan Stafford, England > Before querying i formatted the mDate variable as u told..... But i didn't > put # symbol in the query.... Is it required ? [quoted text clipped - 17 lines] > >> > >> Can anyone help me? Hi Ken, Thanks a lot for explaining regarding date comparision... it wil help me a lot... i had one more query related doubt. Can u help me in that also..? Query: Insert Operation. insert into MeasurementData values('" & Form_frmGenerator.selPartNum & "','" & Me.Controls("locLabel" & ind).Caption & "'," & Round(Val(Me.Controls("txtVal" & ind)), 3) & ",#" & Format(ReadingDate, "mm/dd/yyyy") & "#,'" & Inspector & "'," & ShiftID. ListIndex + 1 & "," & Check.ItemData(Check.ListIndex) & "," & sFlag & ",'" & Comments & "')" i am getting run time error as "Too few parameters are required". But i kept all parameters. i verified by priniting the query also. query was correct. i don't know why i am getting this run time error.. Last parameter of the query i.e. Comments field can be empty. May be this could be the problem..? If that is the case how can i overcome that problem? >Yes. The # character is the date delimiter character. When you build an SQL >statement in code and concatenate a date/time value into it you need to wrap [quoted text clipped - 20 lines] >> >> >> >> Can anyone help me? You don't seem to have a parenthesised column list after the table name. Ken Sheridan Stafford, England > Hi Ken, > Thanks a lot for explaining regarding date comparision... it wil help me a [quoted text clipped - 13 lines] > i.e. Comments field can be empty. May be this could be the problem..? If that > is the case how can i overcome that problem? There are no Parenthesised columns in my query. i am forming the query and storing it into a String named as strQuery like below. strQuery="insert into MeasurementData values('" & Form_frmGenerator. selPartNum & "','" & Me.Controls("locLabel" & ind).Caption & "'," & Round(Val (Me.Controls("txtVal" & ind)), 3) & ",#" & Format(ReadingDate, "mm/dd/yyyy") & "#,'" & Inspector & "'," & ShiftID.ListIndex + 1 & "," & Check.ItemData (Check.ListIndex) & "," & sFlag & ",'" & Comments & "')" currentDB.execute(strQuery) --- Here i am getting run time error..... >You don't seem to have a parenthesised column list after the table name. > [quoted text clipped - 6 lines] >> i.e. Comments field can be empty. May be this could be the problem..? If that >> is the case how can i overcome that problem? Are you inserting values into every field in the table? If not you need to list the columns which the values are to be inserted into in parentheses after the table name and before the VALUES keyword. What sometimes confuses people with this is that any autonumber column is included. Normally you would not assign a value to such a column of course, so no value would be included in the VALUES list, but in that case you need to list all the other columns to which the values are to be assigned. If in the VALUES list you are assigning values to every column then I have to admit that at present I can't see where the problem might be. What happens if you copy the string to which the expression evaluates and paste it into a blank query in SQL view and then run it? Does it then successfully append a row or not? Ken Sheridan Stafford, England > There are no Parenthesised columns in my query. i am forming the query and > storing it into a String named as strQuery like below. [quoted text clipped - 17 lines] > >> i.e. Comments field can be empty. May be this could be the problem..? If that > >> is the case how can i overcome that problem? i need to assign value for every field in the table.i am able to append the record from SQL View. then what could be the problem? >Are you inserting values into every field in the table? If not you need to >list the columns which the values are to be inserted into in parentheses [quoted text clipped - 19 lines] >> >> i.e. Comments field can be empty. May be this could be the problem..? If that >> >> is the case how can i overcome that problem? Hi Ken i got it.. Thanks for the help.... Can u help me in organising tables... i am not getting the possible answers for that..... u can go through the link below for that mapping problem http://www.accessmonster.com/Uwe/Forum.aspx/access-gettingstarted/14198/How-to-m ap-tables#5d36ad4d89c21uwe >i need to assign value for every field in the table.i am able to append the >record from SQL View. then what could be the problem? [quoted text clipped - 4 lines] >>> >> i.e. Comments field can be empty. May be this could be the problem..? If that >>> >> is the case how can i overcome that problem? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.