SELECT variable Substitution?
Hi All,
Is there some way in Access to use a form of variable substitution in the SELECT portion of a query to come up with a variable date value?
Such as:
SELECT # & "2005-01-01 " & DatePart("h",StartDate) & ":00:00" & #
FROM tblRecords
I hope to come up with a valid date I can use in a nested IIf function to determine if a call from a previous day overlaps into an hour period of the following day which has been a major roadblock for me in writing queries to deal with this report.
Thanks for any help.
[561 byte] By [
KalebZen] at [2007-11-11 8:46:13]
# 2 Re: SELECT variable Substitution?
Thanks for the reply Phil.
If you run this code in Access:
CREATE TABLE `tblRecords` (
`RecordId` COUNTER NOT NULL,
`StartDate` DATETIME,
`EndDate` DATETIME
)
INSERT INTO tblRecords ( RecordId, StartDate, EndDate )
VALUES (403, '2005-02-28 09:53:31', '2005-10-26 21:10:59');
You have a table with one record.
If you then run this SELECT statement you receive a syntax error message in Access 2002:
SELECT # & "2005-01-01 " & DatePart("h",StartDate) & ":00:00" & # FROM tblRecords
The error is:
Syntax error in date in query expression '# & "2005-01-01 & DatePart("h", StartDate) & ":00:00" & #'.
I have a second thread (http://forums.dev-archive.com/showthread.php?t=153372) on this forum that will give an idea of what I'm trying to do.
Thanks.
# 3 Re: SELECT variable Substitution?
This seems to work:
SELECT Format("2005-01-01 " & DatePart("h", StartDate) & ":00:00","yyyy-m-d hh:mm:ss") FROM tblRecords
Any advice on the other thread would be so helpful! :)
