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.
|
