Passing on a variable

[RobDavis]

Hiya,

This is probably easy but I'm pretty new at all this.

I have a query in MySQL on an invoices table where I am selecting all invoices prior to a specific invoice date. The only way I can get 1.9.122 to do this is by passing a variable for the invoice date - i.e. InvoiceDate < ?. The reason I can't put an actual date into the query is because 30 days late changes every day! The trouble is, if I then try to manipulate the returned data, filter by company etc, it asks me for the cut off date again. So....is there a way to pass the variable to the results and keep it there without always having to re-enter the Invoice Date? In Access 97 I could use Date()-30 but I can't find an equivalent in Open Office.

BTW, I can get the data using raw SQL, but that data is then marked as read only. I don't suppose there's a way round that.....is there?

My Setup: XP SP2 and an old Win98 laptop for remote testing

Thanks a lot,

Robin Davis

[DrewJensen]

Rob,

If you are using MySQL version 4.1.1 or higher then you would use the datediff function.

for example

WHERE datediff( CUR_DATE() - [col_value] ) > 29

Substitute [col_value] with your firld name.

HTH

Andrew Jensen
_________________
Blog - http://baseanswers.spaces.live.com/

[RobDavis]

Hi,

Thanks for your suggestion but it just throws up errors for me. I use the following to analyse unpaid invoices in a spreadsheet, but cannot get it into a form. any suggestions anybody?

SELECT `InvoiceID`, `InvoiceDate`, `Date`, `CompanyName`, `Fee` + `Repeats` + `Expr1` + `Expenses` - `NHI` AS `Grandtotal`, `OrderNumber`, `Artist`, `Product`, `Producer`, `Studio`, `Comments` FROM `invoices` `invoices` WHERE ( ( `Pencil` = '-1' AND `Confirmed` = '-1' AND `Invoiced` = '-1' AND `Received` = '0' AND `Cancelled` = '0' AND `CompanyName` IS NOT NULL AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= `InvoiceDate` ) )

Rob Davis

[DrewJensen]

Well, I created a table in MySQL 4.1.1.3 and guessed at the column types. Then tried to run your select statment. Keep getting an error. If I remove the section trying to check for the date difference I still get the error.

If I execute this select statement

[RobDavis]

Hi,

Thanks for your help Andrew, but your suggestion still gave me readonly results. HOWEVER, whilst trying different options based on your original suggestion, I struck gold!

THIS, returns editable results:
SELECT `invoices`.*, `Fee` + `Repeats` + `Expr1` + `Expenses` - `NHI` AS `Grandtotal` FROM `invoices` `invoices` WHERE ( ( `CompanyName` IS NOT NULL AND DATEDIFF( CURDATE(), `InvoiceDate` ) >= 30 ) )

One note, I could only create this query in non-design mode. In design mode it throws up errors.

Thanks again Andrew, for pointing me in the right direction.

Robin Davis