Query by months

[cajunspirit]

Hi all,

Small time access user here.

I recently introduce a student payment tracking database to my former employer and now they require the ability to query by months.

I would like to use a form that allows the input of the start date and end date and then query the transactions stored.

I have my transaction table linked to the payments table, which has a date for every payment made. I want all the payments within the queried period to be displayed.

Any guidance would be greatly appreciated.

 

[apr pillai]

You can achieve this by Creating a Query using the Source Table and referencing the Form StartDate, EndDate fields directly on the query criteria row, like the sample given below:

SELECT Table1.* FROM Table1
WHERE (Table1.PaymentDate Between [Forms]![MainForm]![StartDate] AND [Forms]![MainForm]![EndDate]);

Use this Query as Record Source for a Report or Form to display the result.

 

[cajunspirit]

Thank you for the initial direction.

SELECT Payments.* 
FROM Payments;
WHERE (Payments.[B][COLOR=Red][PaymentDate field][/COLOR][/B] Between [Forms]![COLOR=Red][B][MainForm][/B][/COLOR]![B][COLOR=Red][StartDate][/COLOR][/B] AND [[B][COLOR=Red]Forms[/COLOR][/B]]![[B][COLOR=Red]MainForm[/COLOR][/B]]![[COLOR=Red][B]EndDate[/B][/COLOR]]);

The red labeled portions are those I am unsure of.

Do I make the form first, then reference it to this query?

Do I set the textbox values for "StartDate" and "EndDate" in order for the form to use these variables?

Or do I set the query then make a form out of it automatically?

 

[CBrighton]

The first red part should be replaced with the name of the field you are setting the criteria on (the date/time field). The second and third are supposed to be undefined.

The form doesn't have to exist before the query, but it's hard to test it until the form is done.

Replace "MainForm" with the name of the form you create and "StartDate" and "EndDate" with the 2 controls you add for the end user to input the time into.


If you forget about the form Access can give you input box prompts for the dates like this:

Select * from [TableName] where [FieldName] between [Enter start date] and [Enter end date];

This will display 2 input boxes asking the user for the 2 dates each time the query is run.

 

[cajunspirit]

If you forget about the form Access can give you input box prompts for the dates like this:

Select * from [TableName] where [FieldName] between [Enter start date] and [Enter end date];

This will display 2 input boxes asking the user for the 2 dates each time the query is run.

This works beautifully.

The first red part should be replaced with the name of the field you are setting the criteria on (the date/time field). The second and third are supposed to be undefined.

The form doesn't have to exist before the query, but it's hard to test it until the form is done.

Replace "MainForm" with the name of the form you create and "StartDate" and "EndDate" with the 2 controls you add for the end user to input the time into.

I have adapted the code to my database.

SELECT * 
FROM Payments;
WHERE (Payments.Date_paid Between [Forms]![Query payment form]![txtQSDate] AND [Forms]![Query payment form]![txtQEDate]);

I keep getting this error "Characters found at the end of SQL statement", so I suspect syntax errors....

Do I need to add ".text" to the controls?

 

[Brianwarnock]

There should not be a ; at the end of the From clause

Brian

 

[cajunspirit]

It all works fantastically, thank you all.

I am a bit ashamed to be honest that I could not get this working myself.
Is there any learning resource available with these keywords to use in SQL?

 

[CBrighton]

SQL is very widely-used, there are bound to be at least thousands of learning resources!

Books, websites, courses, and forums like this one.

Keep in mind that this forum is a learning resource and if you learn something after asking for help here then it's all good.

Personally, working for a large corporation I get free access to thousands of EBooks on various subjects including SQL. However a lot of the time it's quicker to ask here than it is to find a book which has your problem in it. Plus anyone who searches this forum at a later date with the same problem could find this thread with the answer!