If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DATE HELP
Hello All
Sorry but really struggling with this Date Query I have a table that holds Dates and times (General Date Format) of various daily server logs that I import to an Access Database. Each log is date stamped and this table provides the last date of import to allow the next file to be selected. I also have a table that after importing the logs then takes a number of counts from the logs which it updates in this table against a date value (Primary Key of table) My probelm is with the lookup and access to the date. I have used a recordset to access the table as below, but the issue appears to be with how the date is being formatted after it leaves the vba statement. As can be seen below I have tried reformating the date value at the beginningbut to no avail. I have also tried removing the format in the Set Recordset SQL Public Sub getcount() Dim rstDateUpdate As Recordset Dim rstTestCount As Recordset Dim dtSrvrDate As Date Set rstDateUpdate = CurrentDb.OpenRecordset("SELECT * FROM tblServerImports WHERE (tblServerImports.ServerName)='TestServer'") With rstDateUpdate ' dtSrvrDate = Format(Left(.Fields("LatestFiledate"), 10), "mm\/dd\/yyyy") dtSrvrDate = Left(.Fields("LatestFiledate"), 10) ' dtSrvrDate = Int(.Fields("LatestFiledate")) End With Set rstTestCount = CurrentDb.OpenRecordset("SELECT tblServerCounts.* FROM tblServerCounts WHERE (((tblServerCounts.Date)=#" & Format(dtSrvrDate, "dd\/mm\/yyyy") & "#))", dbOpenDynaset) With rstTestCount MsgBox rstTestCount.RecordCount End With End Sub There is a record for the date being searched therfore count should be 1 but returns 0 (this is also the trigger in the main code to create a new entry if no date exists) Any Help would be gratefully appreciated Kind Regards -- Many Thanks Alan |
#2
|
|||
|
|||
DATE HELP
In queries you must use dates in the form mm/dd/yyyy or yyyy/mm/dd.
For an explanation see International Dates in Access at: http://allenbrowne.com/ser-36.html You might try using the DCount function instead. If DCount("*","tblServerCounts","[Date]=" & Format(dtSrvrDate,"\#yyyy-mm-dd\#")) = 0 Then 'Do stuff End IF By the way, Date is a bad name for a field. Date() is a function that returns the current system date. The two can get confused. Fpr instance, if I had written DCount("*","tblServerCounts","Date=" & Format(dtSrvrDate,"\#yyyy-mm-dd\#")) The comparison might well have used the FUNCTION Date instead of the value of the FIELD named Date. A better name would be ActionDate or fldDate or something that specifies what kind of date this is such as HireDate, TerminationDate, AdmissionDate, etc. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Alan wrote: Hello All Sorry but really struggling with this Date Query I have a table that holds Dates and times (General Date Format) of various daily server logs that I import to an Access Database. Each log is date stamped and this table provides the last date of import to allow the next file to be selected. I also have a table that after importing the logs then takes a number of counts from the logs which it updates in this table against a date value (Primary Key of table) My probelm is with the lookup and access to the date. I have used a recordset to access the table as below, but the issue appears to be with how the date is being formatted after it leaves the vba statement. As can be seen below I have tried reformating the date value at the beginningbut to no avail. I have also tried removing the format in the Set Recordset SQL Public Sub getcount() Dim rstDateUpdate As Recordset Dim rstTestCount As Recordset Dim dtSrvrDate As Date Set rstDateUpdate = CurrentDb.OpenRecordset("SELECT * FROM tblServerImports WHERE (tblServerImports.ServerName)='TestServer'") With rstDateUpdate ' dtSrvrDate = Format(Left(.Fields("LatestFiledate"), 10), "mm\/dd\/yyyy") dtSrvrDate = Left(.Fields("LatestFiledate"), 10) ' dtSrvrDate = Int(.Fields("LatestFiledate")) End With Set rstTestCount = CurrentDb.OpenRecordset("SELECT tblServerCounts.* FROM tblServerCounts WHERE (((tblServerCounts.Date)=#" & Format(dtSrvrDate, "dd\/mm\/yyyy") & "#))", dbOpenDynaset) With rstTestCount MsgBox rstTestCount.RecordCount End With End Sub There is a record for the date being searched therfore count should be 1 but returns 0 (this is also the trigger in the main code to create a new entry if no date exists) Any Help would be gratefully appreciated Kind Regards |
#3
|
|||
|
|||
DATE HELP
Hi Alan,
Try: dtSrvrDate = DateValue(.Fields("LatestFiledate")) And: ...WHERE [Date] = #" & Format(dtSrvrDate, "yyyy-mm-dd") & "#"..... Please note that the use of "Date" for a field name is a poor choice as it can easily get confused with the Date() function. Do a search for "reserved words" in your on-line help in Access to get a list of words that should not be used for table, field and variable names. The DateValue() function will provide you with just the date portion of the LatestFiledate field. When constructing date conditions in a SQL string, it is best to use the universal yyyy-mm-dd format that will not be affected by a user's computer's regional settings. Hope that helps, Clifford Bass Alan wrote: Hello All Sorry but really struggling with this Date Query I have a table that holds Dates and times (General Date Format) of various daily server logs that I import to an Access Database. Each log is date stamped and this table provides the last date of import to allow the next file to be selected. I also have a table that after importing the logs then takes a number of counts from the logs which it updates in this table against a date value (Primary Key of table) My probelm is with the lookup and access to the date. I have used a recordset to access the table as below, but the issue appears to be with how the date is being formatted after it leaves the vba statement. As can be seen below I have tried reformating the date value at the beginningbut to no avail. I have also tried removing the format in the Set Recordset SQL Public Sub getcount() Dim rstDateUpdate As Recordset Dim rstTestCount As Recordset Dim dtSrvrDate As Date Set rstDateUpdate = CurrentDb.OpenRecordset("SELECT * FROM tblServerImports WHERE (tblServerImports.ServerName)='TestServer'") With rstDateUpdate ' dtSrvrDate = Format(Left(.Fields("LatestFiledate"), 10), "mm\/dd\/yyyy") dtSrvrDate = Left(.Fields("LatestFiledate"), 10) ' dtSrvrDate = Int(.Fields("LatestFiledate")) End With Set rstTestCount = CurrentDb.OpenRecordset("SELECT tblServerCounts.* FROM tblServerCounts WHERE (((tblServerCounts.Date)=#" & Format(dtSrvrDate, "dd\/mm\/yyyy") & "#))", dbOpenDynaset) With rstTestCount MsgBox rstTestCount.RecordCount End With End Sub There is a record for the date being searched therfore count should be 1 but returns 0 (this is also the trigger in the main code to create a new entry if no date exists) Any Help would be gratefully appreciated Kind Regards -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 |
Thread Tools | |
Display Modes | |
|
|