date query

[firewall_ie]

Hi There,

I need some code that will let me enter a date into a field from a form and run it in a query. I like to display all record details from weekending 29/06/03.

 

[dcx693]

The coding depends on how you have things set up. Will you be selecting the date from a combo box or entering the date into a text box on the form? Do you want the query then to run automatically?

Generally, you need some SQL code like this:
SELECT fieldsFROM tablename
WHERE (((tablename.Date) Between Forms!formname!datefield And Now()));

The key expression is that last part:
Between datefield And Now();

If you have a set starting or end point, like 29/06/03 that you mentioned you can replace one of the dates in that expression with it. Just enclose the date within # symbols like this:
Between #29/06/03# And Now();

 

[firewall_ie]

And where exactly do you put the SQL code

 

[dcx693]

As I said in my response

The coding depends on how you have things set up. Will you be selecting the date from a combo box or entering the date into a text box on the form? Do you want the query then to run automatically?

 

[firewall_ie]

Yes I want to enter the date into a text box and click search and for the macro to run its self. Everything is in the main form.

So I have my main form with client details, I want a text box to enter the date (which I know how to do) so then I have a command button that will run a macro that will pass the date to a query.

I hope I explained well this time

 

[dcx693]

Very well explained...

So, you'll want to put some code into the command button. The usual place is in the Click event. First, have some code to check if the date in the text box is valid. Users enter all sorts of nonsense; no sense running the query unless the date is valid. Next, have code to open the query displaying the records you want.

As far as how to create the query, I assume you already know how to create a generic query. The key is to have a field in your query, with it's criteria line referencing the field with the date on your main form. So if you your date field is called txtDate and your form is called mainForm, put something like this into the criteria line:
Between Forms!mainForm!txtDate and #29/06/03#

See the attached picture for an example. (The date format will look a bit different since I'm in the states.)