Using a date field from a form in an access query

2
votes

I currently have a query that uses a date field from a form. Everything compiles fine, the problem is when I try an expand on this. I want to specify a date one day ahead of the date displayed on the form. My current SQL used to grab the date is

WHERE ((DateValue([TIMESTAMP]))=[Forms]![Frm_Start]![Date])

However when I try to add + 1 to the end it displays this message:

The expression is typed incorrectly, or is too complex to be evaluated.

Any advice would be greatly appreciated.

3
sqlms-access

3 Answers

2
votes

Use a PARAMETERS clause as the first line of your SQL to inform the db engine the form control contains a Date/Time value.

PARAMETERS Forms!Frm_Start![Date] DateTime;

Then use the parameter with DateAdd() in your WHERE clause:

WHERE DateValue([TIMESTAMP])=DateAdd("d", 1, Forms!Frm_Start![Date])

However, that will require running DateValue() for every row in the table. This should be faster with [TIMESTAMP] indexed:

WHERE
        [TIMESTAMP] >= DateAdd("d", 1, Forms!Frm_Start![Date])
    AND [TIMESTAMP] < DateAdd("d", 2, Forms!Frm_Start![Date])
0
votes

One possibility is:

SELECT Table1.[ADateTime]
FROM Table1
WHERE Table1.[ADateTime] Like ([forms]![Table1]![ADate]+1) & "*"

Dates are numbers with the decimal as a time, so another possibility:

SELECT Table1.ADateTime
FROM Table1
WHERE CLng([ADateTime])=[forms]![Table1]![ADate]+1
0
votes

Try the dateadd function:

`dateadd("d",1,mydatefield)`