A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DATE HELP



 
 
Thread Tools Display Modes
  #1  
Old January 14th, 2010, 11:38 AM posted to microsoft.public.access.queries
Alan
external usenet poster
 
Posts: 459
Default 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  
Old January 14th, 2010, 05:53 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old January 14th, 2010, 06:02 PM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.