How to get last 30 days of records

[tracy1234]

I'm trying to pull the past x days of transactions via a select query that's using a pass through.

My select looks like this:

SELECT [pt get fulfillment invoices].*
FROM [pt get fulfillment invoices]
WHERE ((now())-[pt get fulfillment invoices].[TRX_DATE]) >=[Past ? Days];

Past ? Days is for the user to input a number.

I've used something similar before directly in the passthrough using round(sysdate)instead of now() but I can't seem to use "sysdate" in the select query and I can't get the passthrough to directly accept a user input.

-Tracy

 

[KenReay]

Hi

Why not build the actual value:

assuming user entered value is in xDays,

strSQL = "SELECT [pt get fulfillment invoices].*
FROM [pt get fulfillment invoices]
WHERE [TRX_DATE]) <= #&quot; & DateAdd(&quot;d&quot;,-xDays,DAte()) & &quot;#;&quot;




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tracy1234]

Sorry Ken, you've lost me. Even without the extra &quot; at the end of the statement, I get a syntax error in the select statement and an odbc failure if I use it directly in the passthrough. Where exactly am I supposed to be using this?

How does it prompt the user for xdays?

-Tracy

 

[tracy1234]

As it turns out the select query does work as is if only I had remembered to take out the hard coded date range in my pass-through query. Duh!

-Tracy

 

[kamloops]

Hi There,

Create a form for the user to enter the value of days in a textbox.

Then in your SQL make it >= Forms![FromName]![textboxname}

See if that works...

Krystal