p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning VB.NET Databases (http://p2p.wrox.com/forumdisplay.php?f=147)
-   -   DateDiff Question (http://p2p.wrox.com/showthread.php?t=61385)

ExDb August 22nd, 2007 07:07 AM

DateDiff Question
 
Hello guys,

From the book I've been looking at comparing date/time values and currently I'm presented with a problem.

I have an application that processes and stores the time in an Access table. I use the Today() function so that 12:00 is prefixed to the time.

However the application also queries a given time value (on a form) against a specified time field in the table. The time value on the form of course doesn't contain the date so DateDiff doesn't work when comparing the minutes and seconds:

e.g.

'Compare minutes
Select.....where DateDiff("n",[datefieldintable], [timefieldonform]) = 0

'and compare minutes

Select.....where DateDiff("s",[datefieldintable], [timefieldonform]) = 0

So what I've worked out is that my time form has to have the date appended to the time and the word 'am' appended to it for the query to work. To get the table date/time field's date I've attempted to use the Datepart() function to pull out the dd, mm, and yyyy, append the time and then the word's "am". Here's what I've got so far:

"DateDiff('n'," & strdtfield & ", #" & _
"DatePart('d'," & strdtfield & ")&""/""&" & _
"DatePart('m'," & strdtfield & ")&""/""&" & _
"DatePart('yyyy'," & strdtfield & ") " & _
"12:" & txtfldTime.Text & "am#) = 0")

Here's the SQL string output:

SELECT * FROM [CrewMembers] LEFT JOIN [FitnessTestHistory] ON [CrewMembers].[CrewMemberID] = [FitnessTestHistory].[CrewMemberID] WHERE (DateDiff('n',[FitnessTestHistory].[AbdominalHold], #DatePart('d',[FitnessTestHistory].[AbdominalHold])&"/"&DatePart('m',[FitnessTestHistory].[AbdominalHold])&"/"&DatePart('yyyy',[FitnessTestHistory].[AbdominalHold]) 12:00:00am#) = 0) ORDER BY [FitnessTestHistory].[AbdominalHold]

Here's the error I'm getting

"Syntax error in date in query expression"

Can someone help me correct the date part of the string please? Thanks.

Thearon August 25th, 2007 05:12 AM

It looks like there needs to be a space in your time. For example, you have "12:00:00am" and I believe it needs to be "12:00:00 am".

If that does not work, could you post two dates and times that you are trying to compare? Also a sample of the date and time values as it appears in your database would be helpful.

Thearon

ExDb August 25th, 2007 10:11 PM

Hi Thearon,

That was the answer. I didn't have a space between the "00" and the "am".

Thanks for your help.

ExDb



All times are GMT -4. The time now is 05:26 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.