SQL to show only current date in results page

[TJohnson]

I need to pull up only current date in my database results page.
I am using the following SQL with a hardcoded date but would like to use
the systems current date Getdate() or similar without any user input.

SELECT DISTINCTROW [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy') AS [AssignDate], Sum([Results_Assign].[Suffix]) AS SuffixCount
FROM Results_Assign
WHERE "Timestamp" LIKE '%5/26/2006%'
GROUP BY [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy')
ORDER BY Format$([Results_Assign].[Timestamp],'mm/dd/yyyy') DESC, [Results_Assign].[Desk Code] ASC;

[mehere]

it should work, you can try this:
WHERE "Timestamp" LIKE getdate()

[TJohnson]

WHERE "Timestamp" LIKE Getdate() does not work unless you enclose 'GetDate()' then it returms 0 records.
What format does Getdate use?
Will it work with Frontpage?

[mehere]

it works directly in query analyzer, not sure about passing it through ASP ...

try removing the double quotes you have around timestamp

or use something like this to pass through an ASP page:
WHERE Timestamp LIKE " & date()

[TJohnson]

I get the following error with that...Not sure if the problem with GetDate and Date is comming from the Access Database of ASP.

Server error: Unable to retrieve schema information from the query:

SELECT DISTINCTROW [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy') AS [AssignDate], Sum([Results_Assign].[Suffix]) AS SuffixCount
FROM Results_Assign
WHERE Timestamp LIKE " & date()
GROUP BY [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy')
ORDER BY Format$([Results_Assign].[T

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression 'Timestamp LIKE " & date()

GROUP BY [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy')

ORDER BY Format$([Results_Assign].[Timestamp],'mm/dd/yyyy') DESC, [Results_Assign].[Desk Code] ASC;'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

[degsy]

You haven't concatenated the rest of the query back on.
But you don't have to break out anyway as when it is put into access it will convert it for you.


SELECT DISTINCTROW [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy') AS [AssignDate], Sum([Results_Assign].[Suffix]) AS SuffixCount
FROM Results_Assign
WHERE [Results_Assign].[Timestamp] = date()
GROUP BY [Results_Assign].[Desk Code], Format$([Results_Assign].[Timestamp],'mm/dd/yyyy')
ORDER BY Format$([Results_Assign].[Timestamp],'mm/dd/yyyy') DESC, [Results_Assign].[Desk Code] ASC

[TJohnson]

I found the answer if anyone else is going through the same thing...I setup a field in my access database called Date and had it auto format the current date in the field because timestamp would not work. I then used the following query in my frontpage database results wizard.

SELECT [Results_Assign].[Desk Code], Sum([Results_Assign].[Suffix]) AS SuffixCount, [Results_Assign].[CurrentDate] AS AssignDate
FROM Results_Assign
WHERE ((([Results_Assign].[CurrentDate])=Date()))
GROUP BY [Results_Assign].[Desk Code], [Results_Assign].[CurrentDate];