What is the sysdate equivalent in Access docmd.runsql?

[01-7700]

I've tried sysdate in an sql statement, ie "select a,b,c from d where submitted_date > sysdate - 30;" and get an error - unknown function. I have googled for the answer and not found a reference to this. Does anyone know the answer? Thanks.

 

[Joe4]

Are you actually running this against an Access table or a SQL table?

 

[mrmmickle1]

Try this modification:

strSQL = [COLOR=#333333]"select a,b,c from d where submitted_date > " & [/COLOR][B][COLOR=#ff0000]DATE[/COLOR][/B][COLOR=#333333] & "- 30;"
DoCmd.RunSQL strSQL
[/COLOR]

 

[01-7700]

Are you actually running this against an Access table or a SQL table?

This is being run against an Access query.

 

[Joe4]

Just use:

[COLOR=#333333]SELECT a,b,c FROM d WHERE submitted_date > Date() - 30;[/COLOR]

 

[xenou]

strSQL = "select a,b,c from d where submitted_date > " & DATE & "- 30;"
DoCmd.RunSQL strSQL

If written in the vba environment, ... almost ...

(Joe is right if written in the normal Access GUI environment)

 

[01-7700]

Try this modification:

strSQL = [COLOR=#333333]"select a,b,c from d where submitted_date > " & [/COLOR][B][COLOR=#ff0000]DATE[/COLOR][/B][COLOR=#333333] & "- 30;"
DoCmd.RunSQL strSQL
[/COLOR]

good idea - but it is evaluating as 0 in this expression - i also tried using > #" & DATE & "# - 30;" - same effect

 

[01-7700]

Just use:

[COLOR=#333333]SELECT a,b,c FROM d WHERE submitted_date > Date() - 30;[/COLOR]

Yes! Thanks everyone

 

[mrmmickle1]

good idea - but it is evaluating as 0 in this expression - i also tried using > #" & DATE & "# - 30;" - same effect

That's odd..... I added this to a test database containing email. It executes as expected....

[COLOR=#0000ff]Option Compare Database[/COLOR]


[COLOR=#0000ff]Sub[/COLOR] Test()


[COLOR=#0000ff]    Dim[/COLOR] strSQL [COLOR=#0000ff]As String[/COLOR]


    strSQL = "SELECT Inbox.Priority, Inbox.Subject, Inbox.From FROM Inbox WHERE submitted_date >" & Date & "-30;"


    DoCmd.RunSQL strSQL


[COLOR=#0000ff]End Sub[/COLOR]

Immediate Window:

?strSQL
SELECT Inbox.Priority, Inbox.Subject, Inbox.From FROM Inbox WHERE submitted_date >5/10/2016-30;

Glad you were able to solve your issue.

 

[xenou]

Hi,

Well, technically 5/10/2016 - 30 is about -29.99 (5 divided by 10 divided by 2016 less 30). That should provide some results. But the correct way to write the string is with date literals enclosed in hashes:

strSQL = "SELECT Inbox.Priority, Inbox.Subject, Inbox.From FROM Inbox WHERE submitted_date > (#" & Date & "# - 30);"

result:

?strSQL
SELECT Inbox.Priority, Inbox.Subject, Inbox.From FROM Inbox WHERE submitted_date > (#5/10/2016# - 30);

Another minor detail DoCmd.RunSQL would require an action statement (Insert/Update/Delete) and I'm not sure it will do anything with Select.

The hashes (or lack thereof) might be less of an issue in the Access UI where Access will sometimes add them for you. But that won't be true if you are writing code in the VBA environment.
ξ