Can I use a variable in expression in SELECT?

[Ryan]

Can I say:

SELECT mydate - [start_date], ...

in a query

The column is coming out as #Error

Actually what I need to do is:

SELECT INT(((mydate - [start_date])+7)/7), ...

to get a week offset

 

[Douglas J. Steele]

No, you can't. However, you can define a function that returns the value of
the variable, and use it.

In a module, have something like:

Public Function MyDateValue() As Date
MyDateValue = MyDate
End Function

Then change your query to:

SELECT INT(((MyDateValue() - [start_date])+7)/7), ...

Another option would be to have your function do the entire calculation, and
pass the [start_date] to it:

Public Function DateCalculation(InputDate As Date) As Date
DateCalculation = Int(((mydate - InputDate) + 7) / 7)
End Function

and

SELECT DateCalculation([start_date]), ...

 

[Jeff Boyce]

Ryan

Where? ... as in "where are you trying to use this expression?"

More info, please...

Jeff Boyce
<Access MVP>

 

[Tom Ellison]

Dear Ryan:

By generating the SQL using VBA code you can certainly do this. It is
called a dynamic query. Just make sure your code generates good SQL
in all cases. You can then apply the code as the RecordSource of a
form or report, or as the RowSource of a combo box or list box. Or
you can open a recordset on it. Finally, you can save this as a query
on the front end and just open the datasheet (not the most
professional way to open a query, but it certainly works.

Can I say:

SELECT mydate - [start_date], ...

in a query

The column is coming out as #Error

Actually what I need to do is:

SELECT INT(((mydate - [start_date])+7)/7), ...

to get a week offset

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Ryan]

In the SELECT for a crosstab query.

Actually, I found I could use a datefiff() function
instead of the subtraction and that worked. I still don't
understand why I can't do a subtraction.