textbox as criteria for query

[Batten]

Hi all

I have the following query in Access. This is the sql view of the query

"
SELECT Right([TRXDATE],7) AS [Month], dbo_GL00100.ACTNUMBR_3, dbo_GL00100.ACTNUMBR_4, dbo_GL00100.ACTDESCR, dbo_GL00102.ACCATDSC, dbo_GL20000M.DSCRIPTN, dbo_GL20000M.USWHPSTD, dbo_GL20000M.TRXDATE, dbo_GL20000M!DEBITAMT-dbo_GL20000M!CRDTAMNT AS Amount, dbo_GL00100.USERDEF1
FROM (dbo_GL00100 INNER JOIN dbo_GL20000M ON dbo_GL00100.ACTINDX = dbo_GL20000M.ACTINDX) INNER JOIN dbo_GL00102 ON dbo_GL00100.ACCATNUM = dbo_GL00102.ACCATNUM
WHERE (((dbo_GL00100.ACTNUMBR_4)<"5999999") AND ((dbo_GL00100.USERDEF1)="Adri Visser") AND ((dbo_GL00100.ACTNUMBR_1)="30"));
"

The fields in blue are calculations that I make in the query itself. I have a form with a textbox called tbxDate. Here I will put in the end date for the query and I want this textbox to be used as criteria in the TRXDATE field to only extract transactions prior to the date that I set

How will I be doing it. Can I do it in MDB or must I do it in SQL. I have 24 queries and all of them must use the same textbox as criteria

Can someone please help

Thanks

 

[garyholc]

You want to filter data based on two dates correct? A start and end date? If so, I would create a form, with two text boxes, one called StartDate and one called EndDate.

Create a query which extracts the fields you want, and for the date fields, in the criteria, point the query to this form and the start and end date fields, so for example, ORDERDATE >= forms!form1![startdate] and <=forms!form1![enddate]. For the report, or next form, make the query its data source.

When the user presses the button to confirm the start dates, the query runs, gets the dates from your form, and produces the report based on the query results.

Hope that makes sense.

 

[Batten]

Thanks for the help

I put in the following criteria

<="Froms!frmMEAnalytics![txtdate]"

when I run the query it gives me a Data Type mismatch error

How can i resolve this. My field in the table is a "date/time" data type with a format of "Short Date"

My texbox [txtdate]'s format is also set to "Short Date" on my form. How can there still be a mismatch on data type?

 

[garyholc]

Have you spelt forms correctly on your query as it is spelt incorrectly above.

Should work, check the syntax in the query critera :

=[Forms]![Form1]![Text0]

Form1 being the name of the form where you specify the start/end dates
Text0 being perhaps the start date, or the end date

 

[Batten]

I did misspell the word. Corrected it and it worked fine. The only problem is that the query is exported as a DAP with a PivotTable. When I try to refresh the pivottable on the DAP it tells me that the criteria is missing although it still shows in my textbox

Thanks for all the help